count函数的分条件统计

最新推荐文章于 2025-10-25 23:18:46 发布

原创最新推荐文章于 2025-10-25 23:18:46 发布 · 3k 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询统计了不同状态的数量，并通过子查询的方式筛选出特定条件下的故障记录。此案例涉及多表连接、联合查询、条件筛选及聚合函数等多种SQL技巧。

SELECT
COUNT(b.com_sts) AS countall,
COUNT(
CASE
WHEN b.com_sts = '0' THEN
b.com_sts
END
) countsts,
COUNT(
CASE
WHEN b.com_sts = '1' THEN
b.com_sts
END
) AS countout,
(
SELECT
count(1)
FROM
(
SELECT
bci.id,
bci.fault_sts,
bci.com_sts,
bci.province,
bci.leave_no,
bce.longitude,
bce.latitude
FROM
bz_car_info bci,
bz_car_extend_info bce
WHERE
bci.id = bce.car_id
UNION
SELECT
bci.id,
bci.fault_sts,
bci.com_sts,
bci.province,
bci.leave_no,
bceh.longitude,
bceh.latitude
FROM
bz_car_info bci,
bz_car_extend_info_hp bceh
WHERE
bci.id = bceh.car_id
) a
WHERE
a.fault_sts = '1'
AND a.com_sts = '0'
) AS countfault
FROM
(
SELECT
bci.id,
bci.fault_sts,
bci.com_sts,
bci.province,
bci.leave_no,
bce.longitude,
bce.latitude
FROM
bz_car_info bci,
bz_car_extend_info bce
WHERE
bci.id = bce.car_id
UNION
SELECT
bci.id,
bci.fault_sts,
bci.com_sts,
bci.province,
bci.leave_no,
bceh.longitude,
bceh.latitude
FROM
bz_car_info bci,
bz_car_extend_info_hp bceh
WHERE
bci.id = bceh.car_id
) b