表内容 table1:
号码 件数 金额
a 2 1
a 2 2
a 2 5
b 1 2
b 1 1查询要显示结果:
票数 件数 金额
----------- ---------- --------------
2 3 11
即相同的号码算一票 ,件数根据票数来,如上,a号码算2件,b号码算1件,加起来为3件,而金额全部相加,为11
号码 件数 金额
a 2 1
a 2 2
a 2 5
b 1 2
b 1 1查询要显示结果:
票数 件数 金额
----------- ---------- --------------
2 3 11
即相同的号码算一票 ,件数根据票数来,如上,a号码算2件,b号码算1件,加起来为3件,而金额全部相加,为11
from [Table]
from [Table]
from tb?
select count(distinct 号码) 号码,sum(distinct 件数) 件数,sum(金额) 金额
from [Table]
if object_id('tempdb.dbo.#table1') is not null drop table #table1
create table #table1 (号码 varchar(1),件数 int,金额 int)
insert into #table1
select 'a',2,1 union all
select 'a',2,2 union all
select 'a',2,5 union all
select 'b',1,2 union all
select 'b',1,1select count(distinct 号码) 号码,sum(distinct 件数) 件数,sum(金额) 金额
from #table1
号码 件数 金额
----------- ----------- -----------
2 3 11(1 行受影响)
FROM table1
if object_id('tempdb.dbo.#table1') is not null drop table #table1
create table #table1 (号码 varchar(1),件数 int,金额 int)
insert into #table1
select 'a',1,1 union all
select 'a',1,2 union all
select 'a',1,5 union all
select 'b',1,2 union all
select 'b',1,1select count(distinct 号码),sum(件数),Sum(金额)
from (
select 号码,sum(distinct 件数) 件数,sum(金额) 金额
from #table1
group by 号码
) t
----------- ----------- -----------
2 2 11(1 行受影响)
if object_id('tempdb.dbo.#table1') is not null drop table #table1
create table #table1 (号码 varchar(1),件数 int,金额 int)
insert into #table1
select 'a',2,1 union all
select 'a',2,2 union all
select 'a',2,5 union all
select 'b',1,2 union all
select 'b',1,1select count(distinct 号码),sum(件数),Sum(金额)
from (
select 号码,sum(distinct 件数) 件数,sum(金额) 金额
from #table1
group by 号码
) t
(5 行受影响)
----------- ----------- -----------
2 3 11(1 行受影响)