完全连接:选择我加入的键

Question

这是我从这里开始的桌子

CMS   |   defect_status 
________________________
1     |   true
2     |   false
3     |   true
3     |   false

这就是我想要的

   CMS    |      true_defects    |   false_defects
   1      |      1               |   0
   2      |      0               |   1
   3      |      1               |   1

这是我的密码

SELECT DISTINCT
    false_table.CMS, 
    true_table.true_defects,
    false_table.false_defects

FROM(
    SELECT DISTINCT 
        CMS,
        COUNT(*) AS true_defects 
    FROM data_table
    WHERE defect_status = 'true'
    GROUP BY CMS 
) as true_table

FULL JOIN(
    SELECT DISTINCT 
        CMS,
        COUNT(*) AS false_defects 
    FROM data_table
    WHERE defect_status = 'false'
    GROUP BY CMS 
) as false_table
ON true_table.CMS = false_table.CMS

我想选择所有的CMS，那些在"false_table“和"true_table”。如果我选择"false_table.CMS“(如上面的代码中所示)，下面是我得到的内容：

CMS    |      true_defects    |   false_defects
2      |      0               |   1
3      |      1               |   1

CMS "1“消失只是因为它不在列false_table.CMS中

谢谢

Answer 1

您可以通过简单的case和group by实现它，如下所示：

SELECT t1.CMS
    ,sum(CASE defect_status
            WHEN true
                THEN 1
            ELSE 0
            END) AS true_status
    ,sum(CASE defect_status
            WHEN false
                THEN 1
            ELSE 0
            END) AS false_status
FROM Table1 AS t1
GROUP BY CMS

演示链接：http://sqlfiddle.com/#!17/8478d/1