問題描述

下表是從另一個包含 ID、Name、Organ 和 Age 列的表中創建的.器官列中的值是指定器官和狀況的代碼.

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 我做了一個這樣的表格:

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
--------------------------------------------------------

我想查詢這個表并得到以下結果:

I would like to query this table and get the following result:

--------------------------------------------------------
1318    Joe Smith   Average NULL    Above   Below   50   
--------------------------------------------------------

換句話說，我想根據每個記錄的唯一值創建一個記錄列.

In other words I would like to create one record based on the unique values from each column.

推薦答案

假設每個器官可以只有一個值或 null，如示例數據所示，max 聚合函數應該可以解決問題:

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

這篇關于SQL - 如何根據唯一值組合行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！