select CradNo,
count(ClientID) as 发行量,
sum(Balance) as 结余金额,
cast(sum(Balance)/(select sum(Balance) from cBooking) as numeric(18,4)) as 占比率,
count(distinct ClientID) as 持有人数from cBookinggroup by CradNo
count(ClientID) as 发行量,
sum(Balance) as 结余金额,
cast(sum(Balance)/(select sum(Balance) from cBooking) as numeric(18,4)) as 占比率,
count(distinct ClientID) as 持有人数from cBookinggroup by CradNo
CradNo,
发行量 = count(ClientID),
结余金额 = sum(Balance),
占比率 = cast(sum(Balance)*1.0/(select sum(Balance) from cBooking) as numeric(5,4)),
持有人数 = count(distinct ClientID)
from
cBooking
group by
CradNo
order by
CradNo
insert cBooking select '000001', 'BB' , 1200
insert cBooking select '000001', 'CC' , 8500
insert cBooking select '000001', 'EE' , 0
insert cBooking select '000002', 'AA' , 3000
insert cBooking select '000002', 'DD' , 5000
insert cBooking select '000002', 'BB' , 120
insert cBooking select '000002', 'EE' , 5
insert cBooking select '000002', 'FF' , 0
insert cBooking select '000002', 'AA' , 10/* 查询 */select CradNo,
count(ClientID) as 发行量,
sum(Balance) as 结余金额,
cast((sum(Balance)+0.0)/(select sum(Balance) from cBooking) as numeric(18,4)) as 占比率,
count(distinct ClientID) as 持有人数
from cBookinggroup by CradNo/* 结果 */CradNo 发行量 结余金额 占比率 持有人数
------ ----------- ----------- -------------------- -----------
AA 3 3910 .2087 2
BB 2 1320 .0705 2
CC 1 8500 .4537 1
DD 1 5000 .2669 1
EE 2 5 .0003 2
FF 1 0 .0000 1(所影响的行数为 6 行)
declare @cBooking table(ClientID varchar(10),CradNo varchar(10),Balance int)
insert into @cBooking select '000001','AA',900
insert into @cBooking select '000001','BB',1200
insert into @cBooking select '000001','CC',8500
insert into @cBooking select '000001','EE',0
insert into @cBooking select '000002','AA',3000
insert into @cBooking select '000002','DD',5000
insert into @cBooking select '000002','BB',120
insert into @cBooking select '000002','AA',10
insert into @cBooking select '000002','EE',5
insert into @cBooking select '000002','FF',0--执行统计查询过程
select
CradNo,
发行量 = count(ClientID),
结余金额 = sum(Balance),
占比率 = cast(sum(Balance)*1.0/(select sum(Balance) from @cBooking) as numeric(5,4)),
持有人数 = count(distinct ClientID)
from
@cBooking
group by
CradNo
order by
CradNo--输出结果:
/*
CradNo 发行量 结余金额 占比率 持有人数
----------------------------------------------------------
AA 3 3910 .2087 2
BB 2 1320 .0705 2
CC 1 8500 .4537 1
DD 1 5000 .2669 1
EE 2 5 .0003 2
FF 1 0 .0000 1
*/
我试了一下。通过!!!要是再加一个条件呢?
条件:只统计 ShopNo = 101 的数据,那怎么改呢?等会就结帐!!!
要是再加一个条件呢?
条件:只统计 ShopNo = 101 的数据,那怎么改呢?等会就结帐!!!
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
a.CradNo,
发行量 = count(a.ClientID),
结余金额 = sum(a.Balance),
占比率 = cast(sum(a.Balance)*1.0/(select sum(Balance) from cBooking ClientID=a.ClientID) as numeric(5,4)),
持有人数 = count(distinct a.ClientID)
from
cBooking a,Cimain b
where
a.ClientID=b.ClientID and b.ShopNo = 101
group by
a.CradNo
order by
a.CradNo
declare @cBooking table(ClientID varchar(10),CradNo varchar(10),Balance int)
insert into @cBooking select '000001','AA',900
insert into @cBooking select '000001','BB',1200
insert into @cBooking select '000001','CC',8500
insert into @cBooking select '000001','EE',0
insert into @cBooking select '000002','AA',3000
insert into @cBooking select '000002','DD',5000
insert into @cBooking select '000002','BB',120
insert into @cBooking select '000002','AA',10
insert into @cBooking select '000002','EE',5
insert into @cBooking select '000002','FF',0declare @Cimain table(ClientID varchar(10),ClientName varchar(10),ShopNo int)
insert into @Cimain select '000001','sa ',101
insert into @Cimain select '000002','coco',102
insert into @Cimain select '000003','lisa',101--执行统计查询过程
select
a.CradNo,
发行量 = count(a.ClientID),
结余金额 = sum(a.Balance),
占比率 = cast(sum(a.Balance)*1.0/(select sum(Balance) from @cBooking where ClientID in(select ClientID from @Cimain where ShopNo = 101)) as numeric(5,4)),
持有人数 = count(distinct a.ClientID)
from
@cBooking a,@Cimain b
where
a.ClientID=b.ClientID and b.ShopNo = 101
group by
a.CradNo
order by
a.CradNo/*
AA 1 900 .0849 1
BB 1 1200 .1132 1
CC 1 8500 .8019 1
EE 1 0 .0000 1
*/
感谢 libin_ftsafe(子陌红尘|潇湘剑公子@dev-club) 的热心帮助!