我正在嘗試將我的 MySQL 數據庫中的兩列分組並分別顯示它們的計數（作為單獨的列）。有沒有辦法做到這一點，還是我必須加入兩個子查詢？

我知道WITH ROLLUP但無法生成正確的查詢。

這給出了正確的結果：

SELECT A.mid, B.pid, A.count AS mid_count, B.count as pid_count
 FROM (Select mid , COUNT(*) AS count FROM test GROUP BY mid) AS A,
 (Select mid, pid, COUNT(*) AS count FROM test GROUP BY mid, pid) AS B 
 WHERE A.mid = B.mid ORDER BY mid_count DESC

這是預期的輸出

+-----+-----+-----------+-----------+
| mid | pid | mid_count | pid_count |
+-----+-----+-----------+-----------+
|   3 |  12 |         5 |         2 |
|   3 |  14 |         5 |         1 |
|   3 |  15 |         5 |         2 |
|   4 |  12 |         2 |         1 |
|   4 |  17 |         2 |         1 |
+-----+-----+-----------+-----------+

我在以下位置創建了一個小提琴：http ://sqlfiddle.com/#!9/e3ebe1/12

我從您的子查詢中刪除了一個子查詢，但從根本上說，由於 mysql 不支持分區計數等，它需要多次通過表：

Select test.mid, test.pid, A.cnt as midCount, count(*) as pidCount
from test join
(Select mid, count(*) as cnt from test group by mid) A
on test.mid =  A.mid
Group by mid, pid

替代查詢

我有一個查詢將執行以下操作

• 只有一個子查詢
• 一GROUP BY ... WITH ROLLUP
• 這是一個問題：中間計數是單獨的行而不是列

這是查詢

SELECT mid,IFNULL(pid,0) pid,knt count FROM
(
   SELECT mid,pid,COUNT(id) knt FROM test
   GROUP BY mid,pid WITH ROLLUP
) A WHERE mid IS NOT NULL
ORDER BY mid,IFNULL(pid,0)=0;

這是輸出

mysql> SELECT mid,IFNULL(pid,0) pid,knt count FROM
   -> (
   ->     SELECT mid,pid,COUNT(id) knt FROM test
   ->     GROUP BY mid,pid WITH ROLLUP
   -> ) A WHERE mid IS NOT NULL
   -> ORDER BY mid,IFNULL(pid,0)=0;
+------+-----+-------+
| mid  | pid | count |
+------+-----+-------+
|    3 |  15 |     2 |
|    3 |  12 |     2 |
|    3 |  14 |     1 |
|    3 |   0 |     5 |
|    4 |  12 |     1 |
|    4 |  17 |     1 |
|    4 |   0 |     2 |
+------+-----+-------+
7 rows in set (0.00 sec)

mysql>

這是什麼意思

如果你忽略 pid 0 的行結果，你會得到

+------+----+-------+
| mid  | pd | count |
+------+----+-------+
|    3 | 12 |     2 |
|    3 | 14 |     1 |
|    3 | 15 |     2 |
|    4 | 12 |     1 |
|    4 | 17 |     1 |
+------+----+-------+

這些計數與您的輸出相匹配pid_count

如果您查看pid0 的行結果，您會得到

+------+----+-------+
| mid  | pd | count |
+------+----+-------+
|    3 |  0 |     5 |
|    4 |  0 |     2 |
+------+----+-------+

這些計數與您的輸出相匹配mid_count

在不引入另一個子查詢的情況下，這是我建議的查詢。

概括

輸出必須按如下方式使用

+------+-----+-------+
| mid  | pid | count |
+------+-----+-------+
|    3 |  15 |     2 |
|    3 |  12 |     2 |
|    3 |  14 |     1 |
|    3 |   0 |     5 | <-- Count of mid 3
|    4 |  12 |     1 |
|    4 |  17 |     1 |
|    4 |   0 |     2 | <-- Count of mid 4
+------+-----+-------+

引用自：https://dba.stackexchange.com/questions/114967