以下是我兩個table ...
(TABLE _ 1)
userid username
001 AAA
002 BBB
003 CCC
004 DDD
005 EEE
(TABLE _ 2)
username logein
AAA 1
AAA 1
CCC 1
DDD 1
DDD 1
DDD 1
登入次數加起來 , 沒登入的顯示0
我需要的結果 :
userid login
AAA 2
BBB 0
CCC 1
DDD 3
請問BBB 查不出來但要顯示為0該怎麼作呢 , 拜託幫忙了....;超感謝...
我是用MYSQL . + ASP.NET
已邀請的邦友 {{ invite_list.length }}/5
用子查詢...
SELECT table\_1.username AS userid,(SELECT SUM(logein) AS login FROM table\_2 WHERE table\_2.user\_name=tb1.f2) AS login FROM tb1
不過，這個查詢，沒有登入過的，login欄位會是null...
SELECT a.username, IFNULL(b.cntlogin,0) AS logincount
FROM TABLE_1 a LEFT JOIN (SELECT username, SUM(login) AS cntlogin
FROM TABLE_2
GROUP BY username) b
ON a.username = b.username;
我是會這樣寫
SELECT TABLE_1.username, Count(TABLE_2.logein) AS PenNumber
FROM
TABLE_1 LEFT JOIN TABLE_2 ON
TABLE_1.username = TABLE_2.username
GROUP BY TABLE_1.username
iThome鐵人賽
看影片追技術