问题描述
我有一个查询,它返回一个类似于下面的结果集(实际上它要大得多,有数千行):
<前>一个 |乙 |C |D-----|----|----|-----1 空 |d0 |d0 |空值2 空 |d0 |d1 |空值3 空 |d0 |d2 |04 d0 |d1 |d1 |空值5 d0 |d2 |d2 |0其中两行被认为是重复的,1 和 2,因为 A、B 和 D 是相同的.为了消除这种情况,我可以使用 SELECT DISTINCT A, B, D 但是我的结果集中没有得到 C 列.C列是第3、4、5行的必要信息.
那么我如何从上面的结果集到这个(C4中出现的结果也可以是NULL而不是d1):
<前>一个 |乙 |C |D-----|----|------|-----1 空 |d0 |空 |空值3 空 |d0 |d2 |04 d0 |d1 |d1 |空值5 d0 |d2 |d2 |0 解决方案
DECLARE @YourTable TABLE (一个 VARCHAR(2), B VARCHAR(2), C VARCHAR(2), D VARCHAR(2))INSERT INTO @YourTable VALUES (NULL, 'd0', 'd0', NULL)INSERT INTO @YourTable VALUES (NULL, 'd0', 'd1', NULL)INSERT INTO @YourTable VALUES (NULL, 'd0', 'd2', 'a0')INSERT INTO @YourTable VALUES ('d0', 'd1', 'd1', NULL)插入@YourTable 值('d0'、'd2'、'd2'、'a0')选择 A、B、C = MIN(C)、D从@YourTable按 A、B、D 分组
<小时>
SELECT A, B, CASE WHEN MIN(C) = MAX(C) THEN MIN(C) ELSE NULL END, D从@YourTable按 A、B、D 分组
<小时>
SELECT A, B, CASE WHEN(COALESCE(C, 'dx')) = MAX(COALESCE(C, 'dx')) THEN MIN(C) ELSE NULL END, D从@YourTable按 A、B、D 分组
I have a query that returns a result set similar to the one below (in reality it is far bigger, thousands of rows):
A | B | C | D -----|----|----|----- 1 NULL | d0 | d0 | NULL 2 NULL | d0 | d1 | NULL 3 NULL | d0 | d2 | a0 4 d0 | d1 | d1 | NULL 5 d0 | d2 | d2 | a0
Two of the rows are considered duplicates, 1 and 2, because A, B and D are the same. To eliminate this, I could use SELECT DISTINCT A, B, D but then I do not get column C in my result set. Column C is necessary information for rows 3, 4 and 5.
So how do I come from the result set above to this one (the result appearing in C4 can also be NULL instead of d1):
A | B | C | D -----|----|------|----- 1 NULL | d0 | NULL | NULL 3 NULL | d0 | d2 | a0 4 d0 | d1 | d1 | NULL 5 d0 | d2 | d2 | a0
解决方案
DECLARE @YourTable TABLE ( A VARCHAR(2) , B VARCHAR(2) , C VARCHAR(2) , D VARCHAR(2)) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd0', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd1', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd2', 'a0') INSERT INTO @YourTable VALUES ('d0', 'd1', 'd1', NULL) INSERT INTO @YourTable VALUES ('d0', 'd2', 'd2', 'a0') SELECT A, B, C = MIN(C), D FROM @YourTable GROUP BY A, B, D
SELECT A, B, CASE WHEN MIN(C) = MAX(C) THEN MIN(C) ELSE NULL END, D FROM @YourTable GROUP BY A, B, D
SELECT A, B, CASE WHEN MIN(COALESCE(C, 'dx')) = MAX(COALESCE(C, 'dx')) THEN MIN(C) ELSE NULL END, D FROM @YourTable GROUP BY A, B, D