获取多个表中的数据时，如何在SQL分组中根据表中的条目进行查询-PHP中文网问答

文章专题 AI工具学习下载问答源码最近更新

PHP

会员中心讲师中心微信公众号

获取多个表中的数据时，如何在SQL分组中根据表中的条目进行查询

P粉038856725 2024-04-04 08:58:17

[MySQL讨论组]

487

我有两张桌子

点击 2.ride

点击表

id |时间

乘车表

id |时间戳

我想从两个表中获取数据 GROUP BY EXTRACT(DAY FROMride.timestamp) 但只有当我在同一天的两个表中都有条目时，我才会获取数据，但无论两个表是否没有数据，我都需要数据。我不知道 OUTER join 是否是一个答案，但 mysql 不支持 OUTER JOIN

我当前的查询仅当两个表中都有条目时才获取数据

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)```

P粉038856725

全部回复(1)

P粉5292450502024-04-05 00:40:32 1楼

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

赞 +0

添加回复

专题推荐

更多>

热门话题

热门教程

更多>