select id,money1=min(money1),money2=sum(money2) from 表 group by id
--------合在一起的 select id,money1=sum(money1),money2=(select sum(money1) from 表) from 表 group by id
各位,这个问题其实非常有趣 ,我再拓展一下: 如下结构的表: name lid id money1 money2 ww 1 1 50.00 100.00 ww 2 3 50.00 100.00 ww 2 4 50.00 100.00 xx 3 5 50.00 100.00 yy 4 6 50.00 100.00 返回的结果是: name money1 money2 ww 100.00 300.00 xx 50.00 100.00 yy 50.00 100.00也就是说,依name分组,lid不同则合计money1,money2则全部合计在内。
select name,money1=case when min(lid)=max(lid) then min(money1) else sum(money1) end, sum(money2) as money2 from 表 group by name
select name,money1=sum(money1),money2=sum(money2) from (select name,lid,money1=min(money1),money2=sum(money2) from 表 group by name,lid) a group by name
TO lsxaa(小李铅笔刀) 还是不行啊,多加了50啊!!!!!
select name,money1=sum(money1),money2=sum(money2) from (select name,lid,money1,money2=sum(money2) from 表 group by name,lid,money1) a group by name 这个应该可以阿
create table #tmp(name varchar(20) , lid int , id int , money1 money, money2 money) insert into #tmp select 'ww', 1, 1, 50.00, 100.00 union all select 'ww', 2, 3, 50.00, 100.00 union all select 'ww', 2, 4, 50.00, 100.00 union all select 'xx', 3, 5, 50.00, 100.00 union all select 'yy', 4, 6, 50.00, 100.00select a.name,sum(b.money1)money1,sum(a.money2) money2 from #tmp a join (select lid,money1 from #tmp group by lid,money1) b on a.lid=b.lid group by a.namedrop table #t(所影响的行数为 5 行)name money1 money2 -------------------- --------------------- --------------------- ww 150.0000 300.0000 xx 50.0000 100.0000 yy 50.0000 100.0000(所影响的行数为 3 行)
create table #tmp(name varchar(20) , lid int , id int , money1 money, money2 money) insert into #tmp select 'ww', 1, 1, 50.00, 100.00 union all select 'ww', 2, 3, 50.00, 100.00 union all select 'ww', 2, 4, 50.00, 100.00 union all select 'xx', 3, 5, 50.00, 100.00 union all select 'yy', 4, 6, 50.00, 100.00 ----------------------------------------------------------------------------------------- select name,money1=sum(money1),money2=sum(money2) from (select name,lid,money1=min(money1),money2=sum(money2) from #tmp group by name,lid) a group by name ----------------結果------------------------ name money1 money2 ww 100.00 300.00 xx 50.00 100.00 yy 50.00 100.00
select id,money1=sum(money1),money2=(select sum(money1) from 表) from 表 group by id
ID,
money1 = sum(money1),
money2 = (select sum(money2) from t)
from
t
group by
ID
ID,
money1 = sum(money1),
money2 = sum(money2)
from
t
group by
ID
id sum(money1) sum(money2)
1 50.00 100.00
2 50.00 200.00
3 50.00 100.00
表:
id money1 money2
1 50.00 100.00
2 40.00 100.00
2 50.00 100.00
3 50.00 100.00
---------要得到這個結果
id sum(money1) sum(money2)
1 50.00 100.00
2 40.00 200.00
3 50.00 100.00
-------則改一下
select id,money1=min(money1),money2=sum(money2) from 表 group by id
當然可叫我們大叔了,
小李子 你不當,還有我們這幫長 輩要當呀
select id,money1=min(money1),money2=sum(money2) from 表 group by id
select id,money1=sum(money1),money2=(select sum(money1) from 表) from 表 group by id
如下结构的表:
name lid id money1 money2
ww 1 1 50.00 100.00
ww 2 3 50.00 100.00
ww 2 4 50.00 100.00
xx 3 5 50.00 100.00
yy 4 6 50.00 100.00
返回的结果是:
name money1 money2
ww 100.00 300.00
xx 50.00 100.00
yy 50.00 100.00也就是说,依name分组,lid不同则合计money1,money2则全部合计在内。
sum(money2) as money2
from 表
group by name
(select name,lid,money1=min(money1),money2=sum(money2) from 表
group by name,lid) a
group by name
还是不行啊,多加了50啊!!!!!
(select name,lid,money1,money2=sum(money2) from 表 group by name,lid,money1) a
group by name
这个应该可以阿
create table #tmp(name varchar(20) , lid int , id int , money1 money, money2 money)
insert into #tmp
select 'ww', 1, 1, 50.00, 100.00 union all
select 'ww', 2, 3, 50.00, 100.00 union all
select 'ww', 2, 4, 50.00, 100.00 union all
select 'xx', 3, 5, 50.00, 100.00 union all
select 'yy', 4, 6, 50.00, 100.00select a.name,sum(b.money1)money1,sum(a.money2) money2
from #tmp a join
(select lid,money1 from #tmp group by lid,money1) b
on a.lid=b.lid group by a.namedrop table #t(所影响的行数为 5 行)name money1 money2
-------------------- --------------------- ---------------------
ww 150.0000 300.0000
xx 50.0000 100.0000
yy 50.0000 100.0000(所影响的行数为 3 行)
insert into #tmp
select 'ww', 1, 1, 50.00, 100.00 union all
select 'ww', 2, 3, 50.00, 100.00 union all
select 'ww', 2, 4, 50.00, 100.00 union all
select 'xx', 3, 5, 50.00, 100.00 union all
select 'yy', 4, 6, 50.00, 100.00
-----------------------------------------------------------------------------------------
select name,money1=sum(money1),money2=sum(money2) from
(select name,lid,money1=min(money1),money2=sum(money2) from #tmp
group by name,lid) a
group by name
----------------結果------------------------
name money1 money2
ww 100.00 300.00
xx 50.00 100.00
yy 50.00 100.00