加入显示重复行的 SQL Server

debugcn 发表于 Dev

阿里夫·阿菲安斯亚

我想问一些关于加入查询的问题。我有一个这样的查询：

SELECT b.compilecodingid, 
       a.subjobfamily, 
       b.position, 
       b.nocoding, 
       ( CASE 
           WHEN (SELECT Count(0) 
                 FROM   trlspbia 
                 WHERE  learningsystemid = a.learningsystemid 
                        AND compilecodingid = b.compilecodingid 
                        AND moduleid = '2018081616230361362303614'
                        AND learningroadmap = 'Basic') > 0 THEN 1 
           ELSE 0 
         END ) AS CountPickPBIA 
FROM   trlsplanning a, 
       trcompilecodingheader b 
WHERE  a.learningsystemid = b.learningsystemid 
       AND a.position = b.position 
       AND a.learningsystemid = '2018081513283162000000001'
       order by CountPickPBIA desc

结果在这里

我知道这是因为表 TrLsPlanning 上的列位置有 1 个以上的数据，任何人都可以帮我找到解决方案吗？谢谢你。

戈登·利诺夫

最简单的解决方案可能是select distinct：

SELECT cch.compilecodingid, p.subjobfamily, cch.position,  cch.nocoding, 
       (CASE WHEN EXISTS (SELECT 1
                          FROM trlspbia s
                          WHERE s.learningsystemid = p.learningsystemid AND
                                s.compilecodingid = ccb.compilecodingid AND
                                s.moduleid = '2018081616230361362303614' AND
                                s.learningroadmap = 'Basic'
                         )
             THEN 1 
             ELSE 0 
        END) AS CountPickPBIA 
FROM  trlsplanning p JOIN
      trcompilecodingheader cch
      ON p.learningsystemid = cch.learningsystemid AND
         p.position = cch.position
WHERE p.learningsystemid = '2018081513283162000000001'
ORDER BY CountPickPBIA DESC;

SELECT DISTINCT会产生自己的开销。但是没有更多关于表的结构和内容的信息，这是最简单的解决方案。

注意查询中的其他更改：