第一张表没看到怎么用
set nocount on
if object_id('ta')is not null drop table ta
go
create table ta(BillID varchar(25),RuzhangTime datetime)
insert ta select 'SPXSSK081205005','2008-12-05'
insert ta select 'SPXSSK081205006','2008-12-05'
if object_id('tb')is not null drop table tb
go
create table tb(ID int,BillNO varchar(25),Huilv float)
insert tb select 1,'SPXSSK081205005',1.0
insert tb select 2,'SPXSSK081205006',7.0
if object_id('tc')is not null drop table tc
go
create table tc(SPXSShouKuanDanID int,ProductsID int,ShouKuanMoney money)
insert tc select 1,1 , 1000
insert tc select 1,2 , 2000
insert tc select 1,3 , 500
insert tc select 1,5 , 100
insert tc select 1,6 , 100
insert tc select 2,1 , 1000
insert tc select 2,2 , 5000
insert tc select 2,3 , 400
insert tc select 2,4 , 300
insert tc select 2,6 , 200
select ProductsID,sum(ShouKuanMoney*Huilv)TotalMoney ,rtrim(cast(sum(ShouKuanMoney*Huilv)*100.0/
(select sum(ShouKuanMoney*Huilv)from tc ,tb where tb.id=tc.SPXSShouKuanDanID) as decimal(19,2)))+'%' [Percent]
from tc ,tb where tb.id=tc.SPXSShouKuanDanID group by ProductsID
/*ProductsID TotalMoney Percent
----------- ---------------------- ------------------------------------------
1 8000 15.38%
2 37000 71.15%
3 3300 6.35%
4 2100 4.04%
5 100 0.19%
6 1500 2.88%*/
set nocount on
if object_id('ta')is not null drop table ta
go
create table ta(BillID varchar(25),RuzhangTime datetime)
insert ta select 'SPXSSK081205005','2008-12-05'
insert ta select 'SPXSSK081205006','2008-12-05'
if object_id('tb')is not null drop table tb
go
create table tb(ID int,BillNO varchar(25),Huilv float)
insert tb select 1,'SPXSSK081205005',1.0
insert tb select 2,'SPXSSK081205006',7.0
if object_id('tc')is not null drop table tc
go
create table tc(SPXSShouKuanDanID int,ProductsID int,ShouKuanMoney money)
insert tc select 1,1 , 1000
insert tc select 1,2 , 2000
insert tc select 1,3 , 500
insert tc select 1,5 , 100
insert tc select 1,6 , 100
insert tc select 2,1 , 1000
insert tc select 2,2 , 5000
insert tc select 2,3 , 400
insert tc select 2,4 , 300
insert tc select 2,6 , 200
select ProductsID,sum(ShouKuanMoney*Huilv)TotalMoney ,rtrim(cast(sum(ShouKuanMoney*Huilv)*100.0/
(select sum(ShouKuanMoney*Huilv)from tc ,tb where tb.id=tc.SPXSShouKuanDanID) as decimal(19,2)))+'%' [Percent]
from tc ,tb where tb.id=tc.SPXSShouKuanDanID group by ProductsID
/*ProductsID TotalMoney Percent
----------- ---------------------- ------------------------------------------
1 8000 15.38%
2 37000 71.15%
3 3300 6.35%
4 2100 4.04%
5 100 0.19%
6 1500 2.88%*/
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-12-05 22:28:37
-------------------------------------- Test Data: tc
If object_id('tc') is not null
Drop table tc
Go
Create table tc(SPXSShouKuanDanID int,ProductsID int,ShouKuanMoney int)
Go
Insert into tc
select 1,1,1000 union all
select 1,2,2000 union all
select 1,3,500 union all
select 1,5,100 union all
select 1,6,100 union all
select 2,1,1000 union all
select 2,2,5000 union all
select 2,3,400 union all
select 2,4,300 union all
select 2,6,200
Go
-- Test Data: tb
If object_id('tb') is not null
Drop table tb
Go
Create table tb(ID int,BillNO nvarchar(15),Huilv numeric(2,1))
Go
Insert into tb
select 1,'SPXSSK081205005',1.0 union all
select 2,'SPXSSK081205006',7.0
Go
-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(BillID nvarchar(15),RuzhangTime smalldatetime)
Go
Insert into ta
select 'SPXSSK081205005','2008-12-05'
union all
select 'SPXSSK081205006','2008-12-05'
Go
--Start
--ProductsID TotalMoney(销售总金额) Percent(该商品在整个商品销售额的百分比)
Select
productsid,
TotalMoney = sum(ShouKuanMoney * Huilv),
[Percent]= ltrim(cast(sum(ShouKuanMoney * Huilv)*100.0/(select sum(ShouKuanMoney * Huilv)
from tc c
left join tb b on c.SPXSShouKuanDanID = b.ID
right join ta a on a.BillID = b.BillNO
) as numeric(12,2)))+'%'
from tc c
left join tb b on c.SPXSShouKuanDanID = b.ID
right join ta a on a.BillID = b.BillNO
group by productsid
--Result:
/*productsid TotalMoney Percent
----------- ---------------------------------------- -----------------------------------------
1 8000.0 15.38%
2 37000.0 71.15%
3 3300.0 6.35%
4 2100.0 4.04%
5 100.0 0.19%
6 1500.0 2.88%(所影响的行数为 6 行)
*/
--End
insert jzBillHuihe
select 'SPXSSK081205005','2008-12-05' union all
select 'SPXSSK081205006','2008-12-05'select * from jzBillHuihe
create table jzSPXSShouKuanDan(ID int ,BillNO varchar(32),Huilv float)
insert jzSPXSShouKuanDan
select 1, 'SPXSSK081205005',1.0 union all
select 2,'SPXSSK081205006',7.0select * from jzSPXSShouKuanDancreate table jzSPXSShouKuanDetail(SPXSShouKuanDanID int ,ProductID int,ShouKuanMoney float)
insert jzSPXSShouKuanDetail
select 1, 1,1000 union all
select 1, 2,2000 union all
select 1, 3,500 union all
select 1, 5,100 union all
select 1, 6,100 union all
select 2, 1,1000 union all
select 2, 2,5000 union all
select 2, 3,400 union all
select 2, 4,300 union all
select 2, 6,200
select * from jzSPXSShouKuanDetail
我将建表的sql供上,方便大家测试,希望各位前辈帮忙
set nocount on
if object_id('ta')is not null drop table ta
go
create table ta(BillID varchar(25),RuzhangTime datetime)
insert ta select 'SPXSSK081205005','2008-12-05'
insert ta select 'SPXSSK081205006','2008-12-05'
if object_id('tb')is not null drop table tb
go
create table tb(ID int,BillNO varchar(25),Huilv float)
insert tb select 1,'SPXSSK081205005',1.0
insert tb select 2,'SPXSSK081205006',7.0
if object_id('tc')is not null drop table tc
go
create table tc(SPXSShouKuanDanID int,ProductsID int,ShouKuanMoney money)
insert tc select 1,1 , 1000
insert tc select 1,2 , 2000
insert tc select 1,3 , 500
insert tc select 1,5 , 100
insert tc select 1,6 , 100
insert tc select 2,1 , 1000
insert tc select 2,2 , 5000
insert tc select 2,3 , 400
insert tc select 2,4 , 300
insert tc select 2,6 , 200
select ProductsID,sum(ShouKuanMoney*Huilv)TotalMoney ,rtrim(cast(sum(ShouKuanMoney*Huilv)*100.0/
(select sum(ShouKuanMoney*Huilv)from tc ,tb where tb.id=tc.SPXSShouKuanDanID and tb.billno in(select billno from ta)) as decimal(19,2)))+'%' [Percent]
from tc ,tb where tb.id=tc.SPXSShouKuanDanID and tb.billno in(select billno from ta) group by ProductsID
/*ProductsID TotalMoney Percent
----------- ---------------------- ------------------------------------------
1 8000 15.38%
2 37000 71.15%
3 3300 6.35%
4 2100 4.04%
5 100 0.19%
6 1500 2.88%*/
from tc c
left join tb b on c.SPXSShouKuanDanID = b.ID
right join ta a on a.BillID = b.BillNO可以把这个和先放在一个变量里