组合具有不同条件的count查询

bihw5rsg 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(230)

我正在尝试将我的多个count查询组合成一个查询，但我不知道什么是最好的方法呢？

SELECT DISTINCT n.id as test_count_1 
FROM networks as n
INNER JOIN network_sessions AS ns ON ns.network_id = n.id
LEFT JOIN network_logs AS nl ON nl.network_session_id = ns.id
RIGHT JOIN network_status_logs AS nsl ON nsl.network_id = n.id
WHERE n.status = "LIVE" AND nsl.status = 'LIVE' AND nsl.status_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59'
GROUP BY n.id
HAVING COUNT(nl.id) = 0 ;

SELECT DISTINCT n.id as test_count_2
FROM networks as n
INNER JOIN network_sessions AS ns ON ns.network_id = n.id
LEFT JOIN network_logs AS nl ON nl.network_session_id = ns.id
WHERE n.status = 'LIVE' AND nl.type = "ENTRENCE" AND nl.status_code = 0 AND ns.start_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:59' AND ns.end_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59' AND nl.created_at BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59'
GROUP BY n.id
HAVING COUNT(nl.id) >= 1;

我自己也尝试过这样做（但似乎不对，因为having count似乎做了不同的操作）。我不知道如何在count中使用having count（case when））：

SELECT COUNT( CASE WHEN then 1 else 0 end) as test_count_1,
SELECT COUNT( CASE WHEN then 1 else 0 end) as test_count_2
FROM networks as n
INNER JOIN network_sessions AS ns ON ns.network_id = n.id
LEFT JOIN network_logs AS nl ON nl.network_session_id = ns.id
RIGHT JOIN network_status_logs AS nsl ON nsl.network_id = n.id

如何将它们组合到一个查询中？这些查询具有不同的where和count条件，但它们使用相同的表。感谢您的帮助
所以我想看到这样的结果：

test_count_1    test_count_2
           2               8

sql mysql

来源：https://stackoverflow.com/questions/62994918/combining-count-queries-which-has-different-conditions

3条答案

按热度按时间

sg24os4d1#

使用条件聚合

SELECT COUNT( CASE WHEN n.status = "LIVE" AND nsl.status = 'LIVE' AND nsl.status_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59' then 1 end) as test_count_1,
    SELECT COUNT( CASE WHEN n.status = 'LIVE' AND nl.type = "ENTRENCE" AND nl.status_code = 0 AND ns.start_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:59' 
AND ns.end_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59' 
AND nl.created_at BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59' then 1 end) as test_count_2
    FROM networks as n
    INNER JOIN network_sessions AS ns ON ns.network_id = n.id
    LEFT JOIN network_logs AS nl ON nl.network_session_id = ns.id
    RIGHT JOIN network_status_logs AS nsl ON nsl.network_id = n.id

赞(0）回复(0）举报 2021-07-24

pokxtpni2#

我猜你真的想要这样的东西：

SELECT COUNT(DISTINCT n.id) as total_n_id,
       COUNT(DISTINCT CASE WHEN nl.id IS NULL THEN n.id END) as test_count_1,
       COUNT(DISTINCT CASE WHEN nl.id IS NOT NULL AND nl.type = 'ENTRENCE' THEN n.id END) as test_count_2
FROM networks n INNER JOIN 
     network_sessions ns
     ON ns.network_id = n.id LEFT JOIN
     network_logs nl
     ON nl.network_session_id = ns.id LEFT JOIN
     network_status_logs nsl
     ON nsl.network_id = n.id AND
        nsl.status = 'LIVE' AND
        nsl.status_time >= '2020-07-01' AND
        nsl.status_time < '2020-08-01'
WHERE n.status = 'LIVE'

把常见的情况转移到 ON 或者 WHERE 合同条款和具体条件 CASE 表达。

赞(0）回复(0）举报 2021-07-24

o4hqfura3#

select  pivote.test_count_1,pivote.test_count_2

        from (
        SELECT 'test_count_1' as test_count_name ,
        n.id as count_id
        FROM networks as n
        INNER JOIN network_sessions AS ns ON ns.network_id = n.id
        LEFT JOIN network_logs AS nl ON nl.network_session_id = ns.id
        RIGHT JOIN network_status_logs AS nsl ON nsl.network_id = n.id
        WHERE n.status = "LIVE" AND nsl.status = 'LIVE' AND nsl.status_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59'
        GROUP BY n.id
        HAVING COUNT(nl.id) = 0 
        union all

        SELECT 'test_count_2' as test_count_name,
        n.id as count_id
        FROM networks as n
        INNER JOIN network_sessions AS ns ON ns.network_id = n.id
        LEFT JOIN network_logs AS nl ON nl.network_session_id = ns.id
        WHERE n.status = 'LIVE' AND nl.type = "ENTRENCE" AND nl.status_code = 0 AND ns.start_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:59' AND ns.end_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59' AND nl.created_at BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59'
        GROUP BY n.id
        HAVING COUNT(nl.id) >= 1
        ) as main
        pivot(
        COUNT(count_id) 
            FOR test_count_name IN ([test_count_1],[test_count_2])

        ) as pivote;

赞(0）回复(0）举报 2021-07-24

我来回答

组合具有不同条件的count查询

3条答案

相关问题

热门标签

最新问答