select min(id) as id,a.name,a.age,a.sex,sum(b.one) as one,sum(b.two) as two
from table1 a
join table2 b on a.id = b.pid
group by a.name,a.age,a.sex
from table1 a
join table2 b on a.id = b.pid
group by a.name,a.age,a.sex
你的表1是不是有点不合理?重复的记录是同一个人的吗?如果是同名的人,相加就不正确吧,如果是同一个人,有多个记录是怎么回事?
大哥:你误会了,其实表一是多个表,表二是存储表一数据的.
我现在表达不清楚,我现在很疼苦!
我要做的是,有几个类似于表一的几个表,除ID不同外,其他项相同的记录合成一条记录,将表二中相同项的记录相加!
有没有更好的方法????
select min(id) as id,a.name,a.age,a.sex,sum(b.one) as one,sum(b.two) as two
from table1 a
join table2 b on a.id = b.pid
group by a.name,a.age,a.sex
修改如下:
select distinct(id) as id,a.name,a.age,a.sex,sum(b.one) as one,sum(b.two) as two
from table1 a
join table2 b on a.id = b.pid
group by a.name,a.age,a.sex
------------------------------
1 LPF 20 N
2 FY 20 X
3 LPF 20 N
表二:
ID PID ONE TWOselect a.name ,a.age,a.sex,sum(b.one) as s1,sum(b.two) as s2
from table1 a left join table2 b
on a.id=b.pid
group by a.name,a.age,a.sex
from table1 a left join table2 b
on a.id=b.pid
group by a.name,a.age,a.sex