我有兩個表用戶和Get_cal，如下所示：
用戶
| id_user(pk) | com |
|---|---|
| 1004 | 700 |
| 1005 | 700 |
| 1006 | 700 |
| 1010 | 701 |
| 1011 | 701 |
| 1012 | 701 |
| 1013 | 701 |
| 1014 | 800 |
| 1015 | 800 |
| 1016 | 800 |
| 1017 | 800 |
Get_cal
| id_user(fk) | 狀態 |
|---|---|
| 1004 | 阿班 |
| 1004 | 阿班 |
| 1004 | DES |
| 1004 | 讓 |
| 1004 | DES |
| 1004 | 阿班 |
| 1011 | 讓 |
| 1011 | 讓 |
| 1011 | 阿班 |
| 1015 | 阿班 |
| 1015 | DES |
| 1015 | 讓 |
| 1015 | 讓 |
對于狀態列，我有5 種型別 (ABAN,DES,LET,NOANS,OTH)。我想得到如下結果（按狀態獲取用戶計數）：
| id_user | 阿班 | DES | 讓 | 諾安 | 其他 |
|---|---|---|---|---|---|
| 1004 | 3 | 2 | 1 | 0 | 0 |
| 1005 | 0 | 0 | 0 | 0 | 0 |
| 1006 | 0 | 0 | 0 | 0 | 0 |
| 1010 | 0 | 0 | 0 | 0 | 0 |
| 1011 | 1 | 0 | 2 | 0 | 0 |
| 1012 | 0 | 0 | 0 | 0 | 0 |
| 1013 | 0 | 0 | 0 | 0 | 0 |
| 1014 | 0 | 0 | 0 | 0 | 0 |
| 1015 | 1 | 1 | 2 | 0 | 0 |
| 1016 | 0 | 0 | 0 | 0 | 0 |
| 1017 | 0 | 0 | 0 | 0 | 0 |
我不知道如何開始查詢，請問有什么建議嗎？
uj5u.com熱心網友回復：
您需要在此處進行條件聚合：
SELECT
u.id_user,
COUNT(CASE WHEN c.status = 'ABAN' THEN 1 END) AS ABAN,
COUNT(CASE WHEN c.status = 'DES' THEN 1 END) AS DES,
COUNT(CASE WHEN c.status = 'LET' THEN 1 END) AS LET,
COUNT(CASE WHEN c.status = 'NOANS' THEN 1 END) AS NOANS,
COUNT(CASE WHEN c.status NOT IN ('ABAN', 'DES', 'LET', 'NOAN')
THEN 1 END) AS OTH
FROM Users u
LEFT JOIN Get_cal c
ON c.id_user = u.id_user
GROUP BY
u.id_user
ORDER BY
u.id_user;
uj5u.com熱心網友回復：
如果您的狀態在這 5 種情況下始終是靜態的。您可以使用left joinandgroup by和一些 switch case 條件來解決查詢。
SELECT Users.id_user ,
SUM(CASE WHEN Get_cal.status = 'ABAN' THEN 1 ELSE 0 END) AS ABAN ,
SUM(CASE WHEN Get_cal.status = 'DES' THEN 1 ELSE 0 END) AS DES ,
SUM(CASE WHEN Get_cal.status = 'LET' THEN 1 ELSE 0 END) AS LET ,
SUM(CASE WHEN Get_cal.status = 'NOANS' THEN 1 ELSE 0 END) AS NOANS ,
SUM(CASE WHEN Get_cal.status = 'OTH' THEN 1 ELSE 0 END) AS OTH
FROM Users LEFT JOIN Get_cal ON (Users.id_user = Get_cal.id_user) GROUP BY Users.id_user ,Get_cal.id_user
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/429116.html
標籤：mysql sql 数据库 实体框架 phpmyadmin
上一篇：物體框架在比較之前更改列值