22,209
社区成员
发帖
与我相关
我的任务
分享
我用do熊的数据
create table A(A_Code int, A_Name varchar(16), A_Amount1 int, A_Amount2 int, A_Amount3 int)
insert A select 1001, '张三',1000,2000,3000
union all select 2001,'李四',4000,5000,6000
go
create table B(A_Code int, B_Code int, B_Amount int)
insert B select 1001,10011,1000
union all select 1001,10012,2000
union all select 2001,20011,2000
union all select 2001,20012,3000
union all select 2001,20013,5000
go
create view v_test
as
select A.A_Code, A_Name
,C_TatolNum=Num
,C_Amount=Amount
,C_Per=rtrim(cast(Amount*100.0/(A_Amount1+A_Amount2+A_Amount3) as decimal(5,2)))+'%'
from A join
(
select A_Code, Num=sum(case when B_Amount>0 then 1 else 0 end), Amount=sum(B_Amount) from B group by A_Code
) B on A.A_Code=B.A_Code
go
rtrim(cast(Amount*100.0/(A_Amount1+A_Amount2+A_Amount3) as decimal(5,2)))+'%'
create table ta(A_Code int,A_Name varchar(20),A_Amount1 int,A_Amount2 int,A_Amount3 int)
insert into ta select 1001,'张三',1000,2000,3000
insert into ta select 2001,'李四',4000,5000,6000
create table tb(A_Code int,B_Code int,B_Amount int)
insert into tb select 1001,10011,1000
insert into tb select 1001,10012,2000
insert into tb select 2001,20011,2000
insert into tb select 2001,20012,3000
insert into tb select 2001,20013,5000
select a.A_Code,a.A_Name,b.C_TotalNum,b.B_Amount,
cast(cast(b.B_Amount*100.0/c.num as decimal(10,2)) as varchar)+'%' C_Per
from ta a,(select A_Code,sum(B_Amount) B_Amount,count(*) C_TotalNum from tb group by A_Code) b
,(select A_Code,sum(num) num
from(select A_Code,A_Amount1 as num from ta
union all select A_Code,A_Amount2 from ta
union all select A_Code,A_Amount3 from ta) d
group by A_Code) c
where a.A_Code=b.A_Code and a.A_Code=c.A_Code
drop table ta,tb
/*
A_Code A_Name C_TotalNum B_Amount C_Per
----------- -------------------- ----------- ----------- -------------------------------
1001 张三 2 3000 50.00%
2001 李四 3 10000 66.67%
(所影响的行数为 2 行)
*/
create table A(A_Code int, A_Name varchar(16), A_Amount1 int, A_Amount2 int, A_Amount3 int)
insert A select 1001, '张三',1000,2000,3000
union all select 2001,'李四',4000,5000,6000
go
create table B(A_Code int, B_Code int, B_Amount int)
insert B select 1001,10011,1000
union all select 1001,10012,2000
union all select 2001,20011,2000
union all select 2001,20012,3000
union all select 2001,20013,5000
go
select A.A_Code, A_Name
,C_TatolNum=Num
,C_Amount=Amount
,C_Per=rtrim(cast(Amount*100.0/(A_Amount1+A_Amount2+A_Amount3) as decimal(5,2)))+'%'
from A join
(
select A_Code, Num=sum(case when B_Amount>0 then 1 else 0 end), Amount=sum(B_Amount) from B group by A_Code
) B on A.A_Code=B.A_Code
/*
A_Code A_Name C_TatolNum C_Amount C_Per
----------- ---------------- ----------- ----------- ------------------------------------------
1001 张三 2 3000 50.00%
2001 李四 3 10000 66.67%
(2 row(s) affected)
*/
drop table A,B
create table ta(A_Code int,A_Name varchar(20),A_Amount1 int,A_Amount2 int,A_Amount3 int)
insert into ta select 1001,'张三',1000,2000,3000
insert into ta select 2001,'李四',4000,5000,6000
create table tb(A_Code int,B_Code int,B_Amount int)
insert into tb select 1001,10011,1000
insert into tb select 1001,10012,2000
insert into tb select 2001,20011,2000
insert into tb select 2001,20012,3000
insert into tb select 2001,20013,5000
select a.A_Code,a.A_Name,b.C_TotalNum,b.B_Amount,cast(b.B_Amount*100.0/c.num as decimal(10,2)) C_Per
from ta a,(select A_Code,sum(B_Amount) B_Amount,count(*) C_TotalNum from tb group by A_Code) b
,(select A_Code,sum(num) num
from(select A_Code,A_Amount1 as num from ta
union all select A_Code,A_Amount2 from ta
union all select A_Code,A_Amount3 from ta) d
group by A_Code) c
where a.A_Code=b.A_Code and a.A_Code=c.A_Code
drop table ta,tb
/*
A_Code A_Name C_TotalNum B_Amount C_Per
----------- -------------------- ----------- ----------- ------------
1001 张三 2 3000 50.00
2001 李四 3 10000 66.67
(所影响的行数为 2 行)
*/