mysql - 计算来自不同sql表的同名列

Question

我有 3 个表 scan_1、scan_2、scan_3。下面是 SQL 模式的结构:

scan_1:                 scan_2:                   scan_3:
P_no work               P_no work                 P_no work
1    YES                1    YES                  1    NO
2    NO                 2    NO                   2    NO
3    YES                3    YES                  3    NO

我想计算P_no，其中work ='YES'。但是如果 yes 出现在 2 个位置 LIKE for P_no = 1 其中在 scan_1 和 scan_2 中有 YES 则它必须算作 1。

我的查询是:

SELECT count(`P_no`) AS `ab1` FROM 
`scan_1`,`scan_2`,`scan_3` WHERE 
((`scan_1`.`work`= 'YES') OR 
(`scan_2`.`work`= 'YES') OR 
(`scan_3`.`work`= 'YES')) 

Answer 1

试试这个:

SELECT COUNT(*) AS total_count 
FROM
    (
        SELECT P_no, work FROM scan_1 WHERE work = 'YES'
        UNION
        SELECT P_no, work FROM scan_2 WHERE work = 'YES'
        UNION
        SELECT P_no, work FROM scan_3 WHERE work = 'YES'
    ) AS total