假設有兩個表:
表A(mail會員)
表B(寄送狀態)
為一對多的關係,
想取出所有有成功寄送過的會員的話,sql要怎麼寫呢?
表A(mail會員)
id | user_id |
---|---|
1 | 1 |
2 | 2 |
3 | 3 |
4 | 4 |
表B(寄送狀態)
id | user_id | send_at | status |
---|---|---|---|
1 | 1 | 20190701 | done |
2 | 2 | 20190701 | |
3 | 1 | 20190702 | done |
4 | 3 | 20190702 | done |
5 | 3 | 20190703 | |
6 | 4 | 20190703 | |
7 | 1 | 20190704 | done |
想取得的結果
id | user_id |
---|---|
1 | 1 |
3 | 3 |
SELECT a.id, a.user_id
FROM a JOIN b ON a.user_id=b.user_id and b.status='done'
GROUP BY a.id
用這個sql的話是可以取出想要的結果,但覺得應該有比group by更好的方法。
如果妳的Table A
user_id
資料不重複可以使用exists
Query 1:
SELECT a.id, a.user_id
FROM a
WHERE exists(
select 1
from b
where a.user_id = b.user_id and b.status='done'
)
| id | user_id |
|----|---------|
| 1 | 1 |
| 3 | 3 |
http://sqlfiddle.com/#!9/e1233e/15
提供你另一個思路, 用subquery
SELECT id, user_id
FROM `mail`
WHERE user_id IN (SELECT user_id FROM `status` WHERE `status` = 'done');
使用 distinct 吧。
SELECT distinct a.user_id
FROM a JOIN b ON a.user_id=b.user_id and b.status='done'
SELECT A.* FROM B
LEFT JOIN A ON A.ID = B.ID
WHERE B.DATE >= '2019/04/01'