假设我需要将“特定类别的可用项目数”与“所有项目数”的比率设置为。请考虑这样一个MySQL表:
/*
mysql> select * from Item;
+----+------------+----------+
| ID | Department | Category |
+----+------------+----------+
| 1 | Popular | Rock |
| 2 | Classical | Opera |
| 3 | Popular | Jazz |
| 4 | Classical | Dance |
| 5 | Classical | General |
| 6 | Classical | Vocal |
| 7 | Popular | Blues |
| 8 | Popular | Jazz |
| 9 | Popular | Country |
| 10 | Popular | New Age |
| 11 | Popular | New Age |
| 12 | Classical | General |
| 13 | Classical | Dance |
| 14 | Classical | Opera |
| 15 | Popular | Blues |
| 16 | Popular | Blues |
+----+------------+----------+
16 rows in set (0.03 sec)
mysql> SELECT Category, COUNT(*) AS Total
-> FROM Item
-> WHERE Department='Popular'
-> GROUP BY Category;
+----------+-------+
| Category | Total |
+----------+-------+
| Blues | 3 |
| Country | 1 |
| Jazz | 2 |
| New Age | 2 |
| Rock | 1 |
+----------+-------+
5 rows in set (0.02 sec)
*/
我需要的是一个与此类似的结果集:
/*
+----------+-------+-----------------------------+
| Category | Total | percentage to the all items | (Note that number of all available items is "9")
+----------+-------+-----------------------------+
| Blues | 3 | 33 | (3/9)*100
| Country | 1 | 11 | (1/9)*100
| Jazz | 2 | 22 | (2/9)*100
| New Age | 2 | 22 | (2/9)*100
| Rock | 1 | 11 | (1/9)*100
+----------+-------+-----------------------------+
5 rows in set (0.02 sec)
*/
如何在单个查询中实现这样的结果集?
提前谢谢。
这应该做到:
这样做的好处是你不必复制
WHERE
条件,这是一个定时炸弹下次有人来更新条件,但没有意识到它在两个不同的地方。避免重复的
WHERE
条件也可以提高可读性,特别是如果WHERE
更复杂(有多个连接等),则可以使用。我不确定MySql语法,但是可以使用子查询,如图所示。
相关问题 更多 >
编程相关推荐