MySQL条件计数 [英] Mysql Conditional Count

问题描述

我有此资产表:

asset_id      | mediumint(8)
asset_type_id | mediumint(8)
asset_name    | varchar(200)

和一个查找表:

product_id | mediumint(8)
asset_id   | mediumint(8)
sequence   | smallint(3)

基本上，查找表可以为一个项目包含多个资产条目.您可以在资产表中获得项目描述.

Basically, the lookup table can have multiple entries of assets for an item. And you can get the item description in assets table.

根据资产的asset_type_id将资产分类为图像，视频，声音等.这个asset_type_id由1到16的整数组成.

The assets is categorized as an image, video, sound, etc based on its asset_type_id. This asset_type_id is comprises of integers 1 to 16.

现在，我只想计算特定项目的图片(1)，视频(2)和声音(3)的数量.无论如何，我可以使用一个选择查询"来做到这一点吗?

Now, I want to count the number of images(1), video(2) and sound(3) ONLY for a particular item. Is there anyway I can do this using one SELECT QUERY?

我不能将选择范围限制为asset_type_id = 1或2或3，因为我仍然需要其他资产数据.

I can't limit my selection to just asset_type_id = 1 or 2 or 3 because I still need the other assets data.

推荐答案

SELECT 
   l.product_id, t.asset_type_id, COUNT(*)
FROM
   lookup l
   CROSS JOIN
   (SELECT 1 AS asset_type_id UNION ALL SELECT 2 UNION ALL SELECT 3) t
   LEFT JOIN
   assets a ON l.asset_id = a.asset_id AND t.asset_type_id = a.asset_type_id
GROUP BY
   l.product_id, t.asset_type_id;

作为单独的列

SELECT 
   l.product_id, 
   COUNT(CASE WHEN a.asset_type_id = 1 THEN 1 END) AS asset_count_image,
   COUNT(CASE WHEN a.asset_type_id = 2 THEN 1 END) AS asset_count_video,
   COUNT(CASE WHEN a.asset_type_id = 3 THEN 1 END) AS asset_count_sound
FROM
   lookup l
   LEFT JOIN
   assets a1 ON l.asset_id = a.asset_id AND a.asset_type_id IN (1, 2, 3)
GROUP BY
   l.product_id;

这篇关于MySQL条件计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！