Confirmations table:
+---------+---------------------+-----------+
| user_id | time_stamp | action |
+---------+---------------------+-----------+
| 3 | 2021-01-06 03:30:46 | timeout |
| 3 | 2021-07-14 14:00:00 | timeout |
| 7 | 2021-06-12 11:57:29 | confirmed |
| 7 | 2021-06-13 12:58:28 | confirmed |
| 7 | 2021-06-14 13:59:27 | confirmed |
| 2 | 2021-01-22 00:00:00 | confirmed |
| 2 | 2021-02-28 23:59:59 | timeout |
+---------+---------------------+-----------+
select ye.* from Confirmations a
left join Confirmations ye on a.user_id = ye.user_id and ye.action = 'confirmed'
Output
| user_id | time_stamp | action |
|---|---|---|
| null | null | null |
| null | null | null |
| 7 | 2021-06-14 13:59:27 | confirmed |
| 7 | 2021-06-13 12:58:28 | confirmed |
| 7 | 2021-06-12 11:57:29 | confirmed |
| 7 | 2021-06-14 13:59:27 | confirmed |
| 7 | 2021-06-13 12:58:28 | confirmed |
| 7 | 2021-06-12 11:57:29 | confirmed |
| 7 | 2021-06-14 13:59:27 | confirmed |
| 7 | 2021-06-13 12:58:28 | confirmed |
| 7 | 2021-06-12 11:57:29 | confirmed |
| 2 | 2021-01-22 00:00:00 | confirmed |
| 2 | 2021-01-22 00:00:00 | confirmed |
請問為什麼OUTPUT的部份會有資料重複的情況發生?我知道要怎麼去重複,但想知道導致資料重複的原因><
已邀請的邦友 {{ invite_list.length }}/5
看圖可能易理解
或者將SQL改成這樣, 你再看DATA會明白
select ye.*, a.* from Confirmations a
left join Confirmations ye on a.user_id = ye.user_id and ye.action = 'confirmed'
SELECT *
FROM Confirmations
WHERE user_id IN (
SELECT user_id
FROM Confirmations
WHERE action = 'confirmed'
GROUP BY user_id
HAVING COUNT(*) > 1
)
AND action = 'confirmed';
| user_id | time_stamp | action |
|---------|----------------------|-----------|
| 7 | 2021-06-12 11:57:29 | confirmed |
| 7 | 2021-06-13 12:58:28 | confirmed |
| 7 | 2021-06-14 13:59:27 | confirmed |
iThome鐵人賽
看影片追技術