查询二个表并分组

来源：百度知道 编辑：UC知道 时间：2024/06/23 14:26:35
SELECT user1.*, tjyongjin.Wages AS swa
FROM tjyongjin INNER JOIN
user1 ON tjyongjin.user1id = user1.userid
WHERE (user1.zhuangtai = 1) AND (tjyongjin.Flag = 0) AND (tjyongjin.user1id = 123)

查询出来有四条记录.怎样才能统计成swa
由于user1表的字段过多不可能group by 全表字段
SELECT user1.*, sum(tjyongjin.Wages) AS swa

FROM tjyongjin INNER JOIN
user1 ON tjyongjin.user1id = user1.userid
WHERE (user1.zhuangtai = 1) AND (tjyongjin.Flag = 0) AND (tjyongjin.user1id = 123)

总之我想统计tjyongjin.Wages总数.帮高手帮帮忙
SELECT user1.*, tjyongjin.Wages AS swa
FROM tjyongjin INNER JOIN
user1 ON tjyongjin.user1id = user1.userid
WHERE (user1.zhuangtai = 1) AND (tjyongjin.Flag = 0) AND (tjyongjin.user1id = 123)

这个是可以的.不过有四条数据.这样就不能统计123的总值.我现统计这个总值

SELECT user1.*, sum(tjyongjin.Wages)
错在这里,group by后有些字段不能显现
select a.userid,sum(a.Wages)
FROM tjyongjin a INNER JOIN
user1 b ON a.user1id = b.userid
WHERE (b.zhuangtai = 1) AND (a.Flag = 0) AND (a.user1id = 123)
group by a.userid

你就只查一个userid，然后就group by不就可以了。
SELECT user1.userid , sum(tjyongjin.Wages) AS swa
FROM tjyongjin INNER JOIN
user1 ON tjyongjin.user1id = user1.userid
WHERE (user1.zhuangtai = 1) AND (tjyongjin.Flag = 0) AND (tjyongjin.user1id = 123)
Group by user1.userid

select distinct user1.* ,(select sum(Wages ) from tjyongjin where tjyongjin.userid=user1.userid and tjyongjin.flag=0) as
swa FROM tjyongjin INNER JOIN
user1 ON tjyongjin.user1id = user1.userid
WHERE (user1.zhuangtai = 1) AND (tjyongjin.Flag = 0) AND (tjyongjin.user1id = 123)