sql – 计算具有相同关系的行

发布时间：2020-12-12 06:34:20 所属栏目：MsSql教程来源：网络整理

导读：我有三张桌子.调色板,颜色和关系表palette_color.就像这个样本： http://sqlfiddle.com/#!6/fe832/2 我想计算关系表中具有相同颜色的调色板.正如您在示例中所看到的,我已经在做了.但我相信我的方法效率不高.运行大约需要2秒钟. 我正在使用SQL Server. 这是我

我有三张桌子.调色板,颜色和关系表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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!