将T-SQL COALESCE GROUPING SETS合并为单列，避免NULL重复。

Question

这是一个同义词数据库，其中术语和类别相互链接，并且运行 SQL Server 2008。此方案基于 此 和 此 答案。下面是一个示例：

CREATE TABLE #term (termid VARCHAR(8), en VARCHAR(32), enscope VARCHAR(32))
CREATE TABLE #link (linkid VARCHAR(10), termid VARCHAR(8), reltype VARCHAR(2), refid VARCHAR(8))
CREATE TABLE #categorylink (code VARCHAR(3), termid VARCHAR(8))

INSERT INTO #term VALUES ('100', 'ABC', 'abc_scopenote')
INSERT INTO #term VALUES ('120', 'DEF', 'def_scopenote')
INSERT INTO #term VALUES ('150', 'GHI', NULL)

INSERT INTO #link VALUES ('1', '100', 'NT', '120')
INSERT INTO #link VALUES ('2', '100', 'NT', '150')
INSERT INTO #link VALUES ('3', '120', 'BT', '100')
INSERT INTO #link VALUES ('4', '120', 'RT', '150')
INSERT INTO #link VALUES ('5', '150', 'BT', '100')
INSERT INTO #link VALUES ('6', '150', 'RT', '120')

INSERT INTO #categorylink VALUES ('S01', '100')
INSERT INTO #categorylink VALUES ('S02', '100')
INSERT INTO #categorylink VALUES ('B04', '150')

SELECT
       CASE
       WHEN #term.enscope IS NULL AND refterm.en IS NULL AND #categorylink.code IS NULL
       THEN #term.en
       ELSE NULL
       END,
       CHAR(9) + 'SN ' + #term.enscope,
       CHAR(9) + #link.reltype + CHAR(32) + refterm.en,
       CHAR(9) + 'CODE ' + #categorylink.code
    FROM #link
        INNER JOIN #term ON #term.termid = #link.termid
        INNER JOIN #term AS refterm ON refterm.termid = #link.refid
        LEFT JOIN #categorylink ON #term.termid = #categorylink.termid

    GROUP BY GROUPING SETS (#term.en, (#term.en, #term.enscope), (#term.en, #link.linkid, #link.reltype, refterm.en), (#term.en, #categorylink.code))
    ORDER BY #term.en, #categorylink.code, #link.linkid, #term.enscope
GO

DROP TABLE #term
DROP TABLE #link
DROP TABLE #categorylink
GO

如果“enscope”中有NULL，我就会得到重复的行。

如果没有“categorylink”数值，我也会得到重复的行。

请问如何避免这种情况？

我想将它们全部合并在一个单独的列中，并且不含有重复的项目。

; WITH CTEterm AS (
    SELECT
       ROW_NUMBER() OVER (PARTITION BY #term.en, refterm.en ORDER BY #term.en) AS rownumber,
       #term.en AS mainterm,
       CHAR(9) + 'SN ' + #term.enscope AS scopenote,
       CHAR(9) + #link.reltype + CHAR(32) + refterm.en AS subterms,
       CHAR(9) + 'CODE ' + #categorylink.code AS codes
    FROM #link
       INNER JOIN #term ON #term.termid = #link.termid
       INNER JOIN #term AS refterm ON refterm.termid = #link.refid
       LEFT JOIN #categorylink ON #term.termid = #categorylink.termid
)

SELECT COALESCE(
    CASE
    WHEN rownumber = 1
    THEN mainterm
    ELSE NULL
    END,
    scopenote,
    subterms,
    codes
)
FROM CTEterm
GROUP BY GROUPING SETS ((mainterm, rownumber), (mainterm, scopenote), (mainterm, subterms), (mainterm, codes))
ORDER BY mainterm, codes, subterms, scopenote

GO

如何避免在CASE语句中使用'ELSE NULL'（比如'else skip row'）？

使用COALESCE可以达到相同的效果。

ABC
NULL
    SN abc_scopenote
    NT DEF
    NT GHI
    CODE S01
    CODE S02
NULL
DEF
    SN def_scopenote
    BT ABC
    RT GHI
NULL
GHI
    BT ABC
    RT DEF
    CODE B04

我需要的是这个。

ABC
    SN abc_scopenote
    NT DEF
    NT GHI
    CODE S01
    CODE S02
DEF
    SN def_scopenote
    BT ABC
    RT GHI
GHI
    BT ABC
    RT DEF
    CODE B04

这里有同样的问题，请点击这里查看。

- 84RR1573R

---

你期望的结果集是什么样子？ - Anthony Faull

Answer 1

如果这不是你所期望的，我很抱歉，但如果你只是需要去除NULL值，那么我不明白为什么你不能像这样做：

;WITH CTEterm AS (
    SELECT
       ROW_NUMBER() OVER (PARTITION BY #term.en, refterm.en
                              ORDER BY #term.en) AS rownumber,
       #term.en AS mainterm,
       CHAR(9) + 'SN ' + #term.enscope AS scopenote,
       CHAR(9) + #link.reltype + CHAR(32) + refterm.en AS subterms,
       CHAR(9) + 'CODE ' + #categorylink.code AS codes
    FROM #link
       INNER JOIN #term ON #term.termid = #link.termid
       INNER JOIN #term AS refterm ON refterm.termid = #link.refid
       LEFT JOIN #categorylink ON #term.termid = #categorylink.termid
)
<b>SELECT
  AggValue
FROM (</b>
  SELECT
    <b>mainterm, codes, subterms, scopenote,</b>
    COALESCE(
      CASE WHEN rownumber = 1 THEN mainterm <s>ELSE NULL</s> END,
      scopenote,
      subterms,
      codes
    ) <b>AS AggValue</b>
  FROM CTEterm
  GROUP BY GROUPING SETS ((mainterm, rownumber), (mainterm, scopenote),
                          (mainterm, subterms), (mainterm, codes))
<b>) s
WHERE AggValue IS NOT NULL</b>
ORDER BY mainterm, codes, subterms, scopenote

注意：这里删除ELSE NULL仅仅是因为它没有改变任何东西（如果没有ELSE，则会被视为NULL），而不是因为删除它会有任何好处。

- Andriy M

---

顺便说一下，这个ROW_NUMBER()表达式足够了(PARTITION BY #term.en ORDER BY #term.en)。虽然会生成更多的NULL，但运行速度快了两倍。 - master-lame-master