sql - 计算具有相同关系的行数

Question

我有三张 table 。调色板、颜色和一个关系表palette_color。就像这个示例一样:

http://sqlfiddle.com/#!6/fe832/2

我想计算关系表中具有相同颜色的调色板。正如您在示例中看到的那样，我已经这样做了。但我相信我的方法效率不高。运行大约需要2秒。

我正在使用 SQL Server。

这是我计算行数的地方:

(
SELECT count(DISTINCT palette_id) as total FROM palette_color COLOR
    WHERE NOT EXISTS
        (( (SELECT color_id FROM palette_color WHERE palette_id = PALETTE.id) EXCEPT (SELECT color_id FROM palette_color WHERE palette_id = COLOR.palette_id) )
        UNION ALL
        ( (SELECT color_id FROM palette_color WHERE palette_id = COLOR.palette_id) EXCEPT (SELECT color_id FROM palette_color WHERE palette_id = PALETTE.id) ))
 ) as total

在 where 子句中，我确保只有第一个调色板出现在结果中

WHERE  id =
(
    SELECT MIN(palette_id) FROM palette_color COLOR
        WHERE NOT EXISTS
            (( (SELECT color_id FROM palette_color WHERE palette_id = PALETTE.id) EXCEPT (SELECT color_id FROM palette_color WHERE palette_id = COLOR.palette_id) )
            UNION ALL
            ( (SELECT color_id FROM palette_color WHERE palette_id = COLOR.palette_id) EXCEPT (SELECT color_id FROM palette_color WHERE palette_id = PALETTE.id) ))
)

Answer 1

在这里，我使用 FOR XML PATH 创建 palete_id 中所有 color_id 的字符串列表

然后对每组颜色进行分组并计数。

SQL FIDDLE DEMO (12毫秒)

with cList as (
    SELECT  p.id palette_id, 
            STUFF(( SELECT ',' + CAST(pc.color_id as varchar(10) )
                    FROM palette_color pc
                    WHERE pc.palette_id = p.id
                    ORDER BY pc.color_id
                  FOR
                    XML PATH('')
                  ), 1, 1, '') AS ColorList
    FROM palette p
)
select min(palette_id) palette_id, ColorList, count(*) Total
from cList
group by ColorList