我有三张桌子.调色板,颜色和关系表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) ))
)
解决方法
在这里,我使用FOR XML PATH在palete_id中创建所有color_id的字符串列表
然后分组并计算每组颜色.
SQL FIDDLE DEMO(12ms)
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,'') AS ColorList
FROM palette p
)
select min(palette_id) palette_id,ColorList,count(*) Total
from cList
group by ColorList
