問題描述
下表是從另一個(gè)包含 ID、Name、Organ 和 Age 列的表中創(chuàng)建的.器官列中的值是指定器官和狀況的代碼.
The table below was created from another table with columns ID,Name,Organ,and Age. The values found in the Organ column were codes which designated both organ and condition.
使用 CASE 我做了一個(gè)這樣的表格:
Using CASE I made a table like this:
--------------------------------------------------------
ID NAME Heart Brain Lungs Kidneys AGE
1318 Joe Smith NULL NULL NULL NULL 50
1318 Joe Smith NULL NULL NULL NULL 50
1318 Joe Smith NULL NULL NULL Below 50
1318 Joe Smith NULL NULL NULL Below 50
1318 Joe Smith NULL NULL Above NULL 50
1318 Joe Smith NULL NULL Above NULL 50
1318 Joe Smith Average NULL NULL NULL 50
1318 Joe Smith Average NULL NULL NULL 50
--------------------------------------------------------
我想查詢這個(gè)表并得到以下結(jié)果:
I would like to query this table and get the following result:
--------------------------------------------------------
1318 Joe Smith Average NULL Above Below 50
--------------------------------------------------------
換句話說，我想根據(jù)每個(gè)記錄的唯一值創(chuàng)建一個(gè)記錄列.
In other words I would like to create one record based on the unique values from each column.
推薦答案
假設(shè)每個(gè)器官可以只有一個(gè)值或 null，如示例數(shù)據(jù)所示，max 聚合函數(shù)應(yīng)該可以解決問題:
Assuming each organ can either have just one value or a null, as shown in the sample data, the max aggregate function should do the trick:
SELECT id, name,
MAX(heart), MAX(brain), MAX(lungs), MAX(kidneys),
age
FROM my_table
GORUP BY id, name, age
這篇關(guān)于SQL - 如何根據(jù)唯一值組合行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！