数据库
id var1 var2 va3 net
0001 100001 X00001 M00001 10
0002 100002 X00001 M00001 10
0003 100003 X00002 M00001 10
0004 100004 X00003 M00001 10
0005 100005 X00003 M00001 10
0006 100006 X00003 M00001 10
0007 100007 X00004 M00002 10
0008 100008 X00004 M00002 10对VAR3进行聚合汇总,并计算不同var2的个数,期待结果如下var3 var2 net
M00001 3 60
M00002 1 20感谢您的指导!!
id var1 var2 va3 net
0001 100001 X00001 M00001 10
0002 100002 X00001 M00001 10
0003 100003 X00002 M00001 10
0004 100004 X00003 M00001 10
0005 100005 X00003 M00001 10
0006 100006 X00003 M00001 10
0007 100007 X00004 M00002 10
0008 100008 X00004 M00002 10对VAR3进行聚合汇总,并计算不同var2的个数,期待结果如下var3 var2 net
M00001 3 60
M00002 1 20感谢您的指导!!
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id varchar(4),var1 int,var2 varchar(6),va3 varchar(6),net int)
insert into #tb
select '0001',100001,'X00001','M00001',10 union all
select '0002',100002,'X00001','M00001',10 union all
select '0003',100003,'X00002','M00001',10 union all
select '0004',100004,'X00003','M00001',10 union all
select '0005',100005,'X00003','M00001',10 union all
select '0006',100006,'X00003','M00001',10 union all
select '0007',100007,'X00004','M00002',10 union all
select '0008',100008,'X00004','M00002',10select va3,
var2=count(distinct var2),
net=sum(net)
from #tb
group by va3va3 var2 net
------ ----------- -----------
M00001 3 60
M00002 1 20(2 行受影响)
if object_id('tb') is not null drop table #tb
go
create table tb (id varchar(4),var1 int,var2 varchar(6),va3 varchar(6),net int)
insert into tb
select '0001',100001,'X00001','M00001',10 union all
select '0002',100002,'X00001','M00001',10 union all
select '0003',100003,'X00002','M00001',10 union all
select '0004',100004,'X00003','M00001',10 union all
select '0005',100005,'X00003','M00001',10 union all
select '0006',100006,'X00003','M00001',10 union all
select '0007',100007,'X00004','M00002',10 union all
select '0008',100008,'X00004','M00002',10select va3,count(distinct var2) var2,sum(net) net
from tb
group by va3va3 var2 net
------ ----------- -----------
M00001 3 60
M00002 1 20(2 行受影响)
create table tb (id varchar(4),var1 int,var2 varchar(6),va3 varchar(6),net int)
insert into tb
select '0001',100001,'X00001','M00001',10 union all
select '0002',100002,'X00001','M00001',10 union all
select '0003',100003,'X00002','M00001',10 union all
select '0004',100004,'X00003','M00001',10 union all
select '0005',100005,'X00003','M00001',10 union all
select '0006',100006,'X00003','M00001',10 union all
select '0007',100007,'X00004','M00002',10 union all
select '0008',100008,'X00004','M00002',10select va3,count(distinct var2) var2,sum(net) net
from tb
group by va3va3 var2 net
------ ----------- -----------
M00001 3 60
M00002 1 20
求个把数据直接转化成sql代码的工具
http://topic.csdn.net/u/20080516/15/3fcf4880-67e9-4a28-844d-05985db51215.html
insert into tb select '0001','100001','X00001','M00001',10
insert into tb select '0002','100002','X00001','M00001',10
insert into tb select '0003','100003','X00002','M00001',10
insert into tb select '0004','100004','X00003','M00001',10
insert into tb select '0005','100005','X00003','M00001',10
insert into tb select '0006','100006','X00003','M00001',10
insert into tb select '0007','100007','X00004','M00002',10
insert into tb select '0008','100008','X00004','M00002',10
go
select a.var3,b.countvar3,sum(net) from tb a inner join(
select var3,count(*)as countvar3 from(
select distinct var2,var3 from tb
) t group by var3
)b on a.var3=b.var3
group by a.var3,b.countvar3
go
drop table tb
/*
var3 countvar3
---------- ----------- -----------
M00001 3 60
M00002 1 20(2 行受影响)*/
select va3,
count(distinct var2) var2,
sum(net) net
from #tb
group by va3