针对同一表获取不同条件下的不同计数
针对同一表获取不同条件下的不同计数
提问于 2018-02-08 03:52:17
我已经检查过并尝试过这个solution，但它对我不起作用。简单的查询是
SELECT COUNT(ims.`installation_id`) AS 'total_images', COUNT(ims.`image_name`)
AS 'images_uploaded'
FROM `installation_images_site` ims
INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE ims.`installation_id` = 1 AND ims.`image_upload_flag` = 1输出是
在上述结果中，total_images为2，但实际上为4，images_uploaded的输出是正确的。但我想得到不同的结果。下面是我尝试过的
SELECT COUNT(ims.`installation_id`) AS 'total_images'
FROM `installation_images_site` ims
INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE ims.`installation_id` = 1
GROUP BY ims.`installation_id`
UNION ALL
SELECT COUNT(ims.`id`) AS 'images_uploaded'
FROM `installation_images_site` ims
INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE ims.`image_upload_flag` = 1 AND ims.`installation_id` = 1我现在得到的输出是
计数正常，但我想要两个单独的列total_images和images_uploaded
我怎样才能做到这一点？任何帮助都将不胜感激。
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-02-08 03:56:28
实际上，可以使用条件聚合函数简化查询。
SELECT SUM(CASE WHEN ims.`installation_id` = 1 THEN 1 ELSE 0 END) AS total_images,
SUM(CASE WHEN ims.`image_upload_flag` = 1 AND ims.`installation_id` = 1 THEN 1 ELSE 0 END) AS images_uploaded
FROM `installation_images_site` ims
INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE (ims.`image_upload_flag` = 1 AND ims.`installation_id` = 1)
OR (ims.`installation_id` = 1)
GROUP BY ims.`installation_id`或将查询包装在子查询中，该子查询提供相同的结果。
SELECT MAX(CASE WHEN RN = 1 THEN total_count ELSE 0 END) AS total_images,
MAX(CASE WHEN RN = 2 THEN total_count ELSE 0 END) AS images_uploaded
FROM
(
SELECT COUNT(ims.`installation_id`) AS 'total_count', 1 AS RN
FROM `installation_images_site` ims
INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE ims.`installation_id` = 1
GROUP BY ims.`installation_id`
UNION ALL
SELECT COUNT(ims.`id`) AS 'total_count', 2 AS RN
FROM `installation_images_site` ims
INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE ims.`image_upload_flag` = 1 AND ims.`installation_id` = 1
) a页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48677258
复制相关文章
相似问题
腾讯云开发者
