34,575
社区成员
发帖
与我相关
我的任务
分享
create table tb(id nvarchar(10),var1 nvarchar(10),var2 nvarchar(10),var3 nvarchar(10),net int)
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 行受影响)
*/
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',10
select va3,count(distinct var2) var2,sum(net) net
from tb
group by va3
va3 var2 net
------ ----------- -----------
M00001 3 60
M00002 1 20
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',10
select va3,count(distinct var2) var2,sum(net) net
from tb
group by va3
va3 var2 net
------ ----------- -----------
M00001 3 60
M00002 1 20
(2 行受影响)
--> 测试数据: #tb
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',10
select va3,
var2=count(distinct var2),
net=sum(net)
from #tb
group by va3
va3 var2 net
------ ----------- -----------
M00001 3 60
M00002 1 20
(2 行受影响)