問(wèn)題描述

我有一個(gè)查詢，它返回一個(gè)類似于下面的結(jié)果集(實(shí)際上它要大得多，有數(shù)千行):

其中兩行被認(rèn)為是重復(fù)的，1 和 2，因?yàn)?A、B 和 D 是相同的.為了消除這種情況，我可以使用 SELECT DISTINCT A, B, D 但是我的結(jié)果集中沒(méi)有得到 C 列.C列是第3、4、5行的必要信息.

那么我如何從上面的結(jié)果集到這個(gè)(C4中出現(xiàn)的結(jié)果也可以是NULL而不是d1):

DECLARE @YourTable TABLE (一個(gè) 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 分組

<小時(shí)>

SELECT A, B, CASE WHEN MIN(C) = MAX(C) THEN MIN(C) ELSE NULL END, D從@YourTable按 A、B、D 分組

<小時(shí)>

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

這篇關(guān)于從結(jié)果集中消除部分重復(fù)行的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！