很久没写SQL。今天试着写个,能查出我想要的数据,但是这样写总觉得有问题。有没有更效率简便的语句呢?SELECT u.id, u.user_name, f.name, (SELECT count( * )
FROM generals
WHERE user_id = u.id
), (SELECT count( * )
FROM pay_history
WHERE user_id = u.id), (SELECT sum( pay_fee )
FROM pay_history
WHERE user_id = u.id)
FROM users u, family f, pay_history p
WHERE u.family_id = f.id
GROUP BY u.id 没用什么
FROM generals
WHERE user_id = u.id
), (SELECT count( * )
FROM pay_history
WHERE user_id = u.id), (SELECT sum( pay_fee )
FROM pay_history
WHERE user_id = u.id)
FROM users u, family f, pay_history p
WHERE u.family_id = f.id
GROUP BY u.id 没用什么
select u.id,u.user_name,f.name,SUM(case when g.user_id IS not null then 1 else 0 end)
,count(case when p.user_id IS not null then 1 else 0 end),SUM(p.pay_fee)
from users u left join generals g on u.id=g.user_id
left join family f on u.family_id=f.id
left join pay_history p on u.id=p.user_id
group by u.id,u.user_name,f.name