mysql - 项目和类别 (1 :N) How to get number of total and total active items per category in single SQL query?

Question

项目

item_id
title
active = 1/0

items_categories_map

item_id
category_id

我需要得到结果

category_id
items_total (any value of items.active)
items_active (items.active = 1)
items_inactive (items.active = 0)

是否可以在不使用 UNION 的情况下通过单个 SQL 查询获得这样的结果？

谢谢!

Answer 1

使用如下模板...

COUNT(items.active) AS items_total,
SUM(items.active)   AS items_active,
SUM(1-items.active) AS items_inactive

或者当你的值不是 1 或 0 时...

SUM(CASE WHEN items.active = 0 THEN 1 ELSE 0 END) as items_inactive