gpt4 book ai didi

mysql - SQL 连接并获取表中的计数

转载 作者:行者123 更新时间:2023-11-29 17:06:04 25 4
gpt4 key购买 nike

这是我的 SQL Fiddle

我现在正在加入useraddress 表。现在我需要对填充了 in_timeout_time 的日志表进行计数

这是我迄今为止的 SQL 查询

select u.id, u.name, a.address from user u 
left join address a on u.id = a.user_id
where u.id = 1

即,输出应该是这样的

id  name    address  total_count proper_count
1 Alpha Chennai 4 3

最佳答案

您距离预期查询仅退了一步。只需要另一个与日志表的连接并使用聚合函数

select u.id, u.name, a.address,
sum(case when in_time is not null and out_time is not null
then 1 else 0 end ) as total_count ,
SUM(CASE WHEN l.in_time = 0 OR l.out_time = 0 THEN 0 ELSE 1 END) AS proper_count
from user u
left join address a on u.id = a.user_id
left join log l on u.id=l.user_id
where u.id = 1
group by u.id, u.name, a.address

http://sqlfiddle.com/#!9/b2efe0/6

id  name    address total_count proper_count
1 Alpha Chennai 4 3

关于mysql - SQL 连接并获取表中的计数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52049471/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com