Saya ada dua meja
Klik jadual
id |. masa
Jadual Perjalanan
id |. cap masa
Saya ingin mendapatkan data daripada dua jadual KUMPULAN MENGIKUT EKSTRAK(DAY FROMride.timestamp) Tetapi saya akan mendapat data hanya jika saya mempunyai entri dalam kedua-dua jadual untuk hari yang sama, tetapi saya memerlukan data itu tanpa mengira sama ada kedua-dua jadual tidak mempunyai data. Saya tidak tahu sama ada OUTER join adalah jawapan tetapi mysql tidak menyokong OUTER JOIN
Pertanyaan semasa saya hanya mendapat data jika terdapat entri dalam kedua-dua jadual
COUNT(distinct ride.id) AS ride_ads, COUNT(distinct clicks.id) AS clicks FROM ride INNER JOIN clicks ON EXTRACT(DAY FROM ride.timestamp)=EXTRACT(DAY FROM clicks.time) GROUP BY EXTRACT(DAY FROM ride.timestamp), EXTRACT(DAY FROM clicks.time)```
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
SELECT DATE(ride.timestamp) AS Day, COUNT(DISTINCT ride.id) AS ride_ads, COUNT(DISTINCT clicks.id) AS clicks FROM ride LEFT JOIN clicks ON DATE(ride.timestamp) = DATE(clicks.time) WHERE DATE(ride.timestamp) > NOW() - INTERVAL 15 DAY GROUP BY Day UNION SELECT DATE(clicks.time) AS Day, #selecting date from second table since I might have record in this table and I am using group by Day COUNT(DISTINCT ride.id) AS ride_ads, COUNT(DISTINCT clicks.id) AS clicks FROM ride RIGHT JOIN clicks ON DATE(ride.timestamp) = DATE(clicks.time) WHERE DATE(clicks.time) > NOW() - INTERVAL 15 DAY GROUP BY Day ORDER BY Day