表一:
userid age money
ABC-A 10 500
ABC-B 20 600
BCD-F 35 1200
BCD-E 22 3400
表二:
userid firstname lastname
ABC Zhang San
BCD Zhao Wu
请问大家,我怎么得到下面的结果:userid firstname lastname age money
ABC Zhang San 30 1100
BCD Zhao Wu 57 4600
userid age money
ABC-A 10 500
ABC-B 20 600
BCD-F 35 1200
BCD-E 22 3400
表二:
userid firstname lastname
ABC Zhang San
BCD Zhao Wu
请问大家,我怎么得到下面的结果:userid firstname lastname age money
ABC Zhang San 30 1100
BCD Zhao Wu 57 4600
left join
(
select substr(userid,1,3) as us,sum(age) as sa,sum(money) as sm from b1
group by substr(userid,1,3)) c
on a.userid=c.us
from 表一 as a inner join 表二 as b on a.userid=b.userid
group by b.userid,b.firstname,b.lastname.
[align=center]==== 思想重于技巧 ====
[/align]
.
贴子分数<20:对自已的问题不予重视。
贴子大量未结:对别人的回答不予尊重。
.
SQL 1992
MySQL 5.1 Reference Manual
ActiveX Data Objects 2.5 Reference 下载
数据库系统概论PPT.
[align=center]==== 思想重于技巧 ====
[/align]
.
贴子分数<20:对自已的问题不予重视。
贴子大量未结:对别人的回答不予尊重。
.
create table t1 (userid char(5),age int,money int)
insert into t1 values ('ABC-A', 10 ,500),('ABC-B', 20 , 600),('BCD-F', 35,1200),('BCD-E', 22 , 3400)#建立表2
create table t2 (userid char(3), firstname varchar(10), lastname varchar(10))
insert into t2 values ('ABC','Zhang', 'San'),('BCD','Zhao', 'Wu')#查询
select t2.userid,t2.firstname,t2.lastname,sum(t1.age) age,sum(t1.money) money
from (select left(userid,3) userid,age,money from t1) t1 inner join t2 on t1.userid= t2.userid
group by t2.userid,t2.firstname,t2.lastname#结果
userid firstname lastname age money
ABC Zhang San 30 1100
BCD Zhao Wu 57 4600
#建立表1
create table t1 (userid char(5),age int,money int)
insert into t1 values ('ABC-A', 10 ,500),('ABC-B', 20 , 600),('BCD-F', 35,1200),('BCD-E', 22 , 3400)#建立表2
create table t2 (userid char(3), firstname varchar(10), lastname varchar(10))
insert into t2 values ('ABC','Zhang', 'San'),('BCD','Zhao', 'Wu')#查询
select t2.userid,firstname,lastname,sum(age) age,sum(money) money
from t1,t2
where left(t1.userid,3)=t2.userid
group by t2.userid;
from 表一 a, 表二 b
where a.userid=b.userid按此sql語句查詢就OK了.