请教各位大侠,我在开发中遇到个问题,有三个表,user(用户)表,order(订单)表,app(订单评价)表,现在的需求是要用一个SQL联合查询出某个用户发不过的订单总数,及订单总金额,以及该用户的订单评价总条数。SQL我是这样写的:
SELECT u.id,u.`name`,COUNT( DISTINCT a.id ),COUNT( DISTINCT o.id ),SUM(o.money)
FROM `user` u
INNER JOIN (SELECT id,to_user_id FROM `app` GROUP BY id) a ON u.id = a.to_user_id
LEFT JOIN (SELECT id,use_id,money FROM `order` GROUP BY id) o ON u.id = o.use_id
WHERE u.id =7;但是查询出来的SUM值不对,比如正确的应该是10000,但实际返回的结果里,这个值里重复计算了很多次,变成60000这样。请问这样的需求,语句该怎么写?
SELECT u.id,u.`name`,COUNT( DISTINCT a.id ),COUNT( DISTINCT o.id ),SUM(o.money)
FROM `user` u
INNER JOIN (SELECT id,to_user_id FROM `app` GROUP BY id) a ON u.id = a.to_user_id
LEFT JOIN (SELECT id,use_id,money FROM `order` GROUP BY id) o ON u.id = o.use_id
WHERE u.id =7;但是查询出来的SUM值不对,比如正确的应该是10000,但实际返回的结果里,这个值里重复计算了很多次,变成60000这样。请问这样的需求,语句该怎么写?
SELECT u.id,u.`name`,COUNT( DISTINCT a.id ),COUNT( DISTINCT o.id ),max(o.ma)
FROM `user` u
INNER JOIN (SELECT id,to_user_id FROM `app` GROUP BY id,to_user_id) a ON u.id = a.to_user_id
LEFT JOIN (SELECT id,use_id,sum(money) as ma FROM `order` GROUP BY id,use_id) o ON u.id = o.use_id
WHERE u.id =7;
FROM `user` u
INNER JOIN (SELECT id,to_user_id FROM `app` GROUP BY id,to_user_id) a ON u.id = a.to_user_id
LEFT JOIN (SELECT id,use_id,sum(money) as ma FROM `order` GROUP BY id,use_id) o ON u.id = o.use_id
WHERE u.id =7 droup by u.id,u.`name`;
user表结构及数据:
id,name
7,seeky
order表结构及数据:
id,user_id,money
1,7,1000
2,7,2000
3,5,2000
app表结构及数据:
id,to_user_id,order_id
1, 7, 1
2, 7, 2
3, 7, 3
这里需要实现的结果是查询出用户ID为7的order表中订单总数和总金额,以及app表该用户(to_user_id字段)的总数。大概是这样子,由于涉及公司的保密问题,建表语句就不能发了,望海涵!
(select user_id,sum(money) as ma from order group by user_id) b
on a.id=b.user_id
left join
(select to_user_id,count(*) as mc from app group by to_user_id) c
on a.id=b.to_user_id
(select count(*) from `order` where user_id=u.id) as 订单总数,
(select sum(money) from `order` where user_id=u.id) as 总金额,
(select count(*) from `app` where user_id=u.id) as 总数
from user u
where id=7