废话不多说,有如下的表:
declare @t table
(
id int primary key identity(1,1),
name varchar(20),
addre varchar(20),
age int,
money1 int,
money2 int,
money3 int
)插入如下的数据:
insert into @t (name,addre,age,money1,money2,money3) values ('matt','toronto',12,100,200,300)
insert into @t (name,addre,age,money1,money2,money3) values ('tom','shanghai',14,150,250,350)
insert into @t (name,addre,age,money1,money2,money3) values ('bush','shanghai',23,500,500,500)
insert into @t (name,addre,age,money1,money2,money3) values ('zila','toronto',52,10,50,90)
insert into @t (name,addre,age,money1,money2,money3) values ('lulu','beijing',42,50,40,60)
insert into @t (name,addre,age,money1,money2,money3) values ('gogo','beijing',28,100,290,40)
写下如下的选择语句:
select t.name,t1.* from @t as t join
(select addre,SUM(age) sAge,SUM(money1) sMoney1,SUM(money2) sMoney2,SUM(money3) sMoney3 from @t group by addre) as t1
on t.addre = t1.addre得到如下的结果:
name addre sAge sMoney1 sMoney2 sMoney3
lulu beijing 70 150 330 100
gogo beijing 70 150 330 100
tom shanghai 37 650 750 850
bush shanghai 37 650 750 850
zila toronto 64 110 250 390
matt toronto 64 110 250 390我的问题是:我只想得到每一个 group by 之后的第一个record,也就是说,我想得到的结果是这样的
name addre sAge sMoney1 sMoney2 sMoney3
lulu beijing 70 150 330 100
tom shanghai 37 650 750 850
zila toronto 64 110 250 390该如何写这个SQL,还是根本不可能写出来?只能用那该死的Cursor?多谢
declare @t table
(
id int primary key identity(1,1),
name varchar(20),
addre varchar(20),
age int,
money1 int,
money2 int,
money3 int
)插入如下的数据:
insert into @t (name,addre,age,money1,money2,money3) values ('matt','toronto',12,100,200,300)
insert into @t (name,addre,age,money1,money2,money3) values ('tom','shanghai',14,150,250,350)
insert into @t (name,addre,age,money1,money2,money3) values ('bush','shanghai',23,500,500,500)
insert into @t (name,addre,age,money1,money2,money3) values ('zila','toronto',52,10,50,90)
insert into @t (name,addre,age,money1,money2,money3) values ('lulu','beijing',42,50,40,60)
insert into @t (name,addre,age,money1,money2,money3) values ('gogo','beijing',28,100,290,40)
写下如下的选择语句:
select t.name,t1.* from @t as t join
(select addre,SUM(age) sAge,SUM(money1) sMoney1,SUM(money2) sMoney2,SUM(money3) sMoney3 from @t group by addre) as t1
on t.addre = t1.addre得到如下的结果:
name addre sAge sMoney1 sMoney2 sMoney3
lulu beijing 70 150 330 100
gogo beijing 70 150 330 100
tom shanghai 37 650 750 850
bush shanghai 37 650 750 850
zila toronto 64 110 250 390
matt toronto 64 110 250 390我的问题是:我只想得到每一个 group by 之后的第一个record,也就是说,我想得到的结果是这样的
name addre sAge sMoney1 sMoney2 sMoney3
lulu beijing 70 150 330 100
tom shanghai 37 650 750 850
zila toronto 64 110 250 390该如何写这个SQL,还是根本不可能写出来?只能用那该死的Cursor?多谢
from @t a
where not exists (select 1 from @t where addre=a.addre and id>a.id);
(
id int primary key identity(1,1),
name varchar(20),
addre varchar(20),
age int,
money1 int,
money2 int,
money3 int
)
insert into @t (name,addre,age,money1,money2,money3) values ('matt','toronto',12,100,200,300)
insert into @t (name,addre,age,money1,money2,money3) values ('tom','shanghai',14,150,250,350)
insert into @t (name,addre,age,money1,money2,money3) values ('bush','shanghai',23,500,500,500)
insert into @t (name,addre,age,money1,money2,money3) values ('zila','toronto',52,10,50,90)
insert into @t (name,addre,age,money1,money2,money3) values ('lulu','beijing',42,50,40,60)
insert into @t (name,addre,age,money1,money2,money3) values ('gogo','beijing',28,100,290,40)select t.name,t1.* from @t as t join
(select addre,SUM(age) sAge,SUM(money1) sMoney1,SUM(money2) sMoney2,SUM(money3) sMoney3 from @t group by addre) as t1
on t.addre = t1.addre
and name=(select top 1 name from @t where addre=t.addre)
/*
name addre sAge sMoney1 sMoney2 sMoney3
-------------------- -------------------- ----------- ----------- ----------- -----------
lulu beijing 70 150 330 100
tom shanghai 37 650 750 850
matt toronto 64 110 250 390(3 行受影响)
*/
(
id int primary key identity(1,1),
name varchar(20),
addre varchar(20),
age int,
money1 int,
money2 int,
money3 int
)
insert into @t (name,addre,age,money1,money2,money3) values ('matt','toronto',12,100,200,300)
insert into @t (name,addre,age,money1,money2,money3) values ('tom','shanghai',14,150,250,350)
insert into @t (name,addre,age,money1,money2,money3) values ('bush','shanghai',23,500,500,500)
insert into @t (name,addre,age,money1,money2,money3) values ('zila','toronto',52,10,50,90)
insert into @t (name,addre,age,money1,money2,money3) values ('lulu','beijing',42,50,40,60)
insert into @t (name,addre,age,money1,money2,money3) values ('gogo','beijing',28,100,290,40)
;with f as
(
select
t.name,t1.*
from
@t as t
join
(select addre,SUM(age) sAge,SUM(money1) sMoney1,SUM(money2) sMoney2,SUM(money3) sMoney3 from @t group by addre) as t1
on
t.addre = t1.addre
)select
name,addre,sAge,sMoney1,sMoney2,sMoney3
from
(
select id=row_number()over(partition by addre order by getdate()),* from f
)t
where
id=(select min(id) from (select id=row_number()over(partition by addre order by getdate()),* from f)a where addre=t.addre)
/*name addre sAge sMoney1 sMoney2 sMoney3
-------------------- -------------------- ----------- ----------- ----------- -----------
lulu beijing 70 150 330 100
tom shanghai 37 650 750 850
zila toronto 64 110 250 390(3 行受影响)
*/
(select addre,SUM(age) sAge,SUM(money1) sMoney1,SUM(money2) sMoney2,SUM(money3) sMoney3 from @t group by addre) as t1
on t.addre = t1.addre
and name=(select top 1 name from @t where addre=t.addre order by id)
最好再加个 order by ID
(select * from @t as t where not exists(select * from @t where addre=t.addre and name>t.name)) as t
join
(select addre,SUM(age) sAge,SUM(money1) sMoney1,SUM(money2) sMoney2,SUM(money3) sMoney3 from @t group by addre) as t1
on t.addre = t1.addre