分类统计解决方案

分类统计
A表 合同基本信息表
ID ContractNO AddDate
1 0120252 2011-12-12
2 0120373 2012-1-5
3 0120389 2012-1-15

B表 合同产品信息表
ID ContractNO ProductName Price PriceUnit
1 0120252 产品1 10.00 元 
2 0120252 产品2 20.00 元 
3 0120373 产品1 5.00 美元
4 0120373 产品2 5.00 美元
5 0120389 产品1 15.00 元

可以按时间段查询出所有产品的价格总计，比如查询2012-1-1至2012-1-10这个时间段的所有产品的价格总计。

[解决办法]

SQL code

select sum(case when priceuint='美元' then price*汇率 else price end)from A表,B表where A.contractno=b.contractno and adddate between '2012-1-1' and '2012-1-10'
[解决办法]
select a.AddDate,SUM(b.Price)as total from a join b on a.ContractNO=b.ContractNO
where a.AddDate between '2012-1-1' and '2012-1-10'
group by a.AddDate
[解决办法]
SQL code
declare @A表 table(ID int,ContractNO varchar(7),AddDate datetime)insert into @A表select 1,'0120252','2011-12-12' union allselect 2,'0120373','2012-1-5' union allselect 3,'0120389','2012-1-15'declare @B表 table (ID int,ContractNO varchar(7),ProductName varchar(5),Price numeric(4,2),PriceUnit varchar(4))insert into @B表select 1,'0120252','产品1',10.00,'元' union allselect 2,'0120252','产品2',20.00,'元' union allselect 3,'0120373','产品1',5.00,'美元' union allselect 4,'0120373','产品2',5.00,'美元' union allselect 5,'0120389','产品1',15.00,'元'select sum(case when PriceUnit='美元' then 6.3161*price else price end) from @A表 a left join @B表 b on a.ContractNO=b.ContractNOwhere a.AddDate between '2012-01-01' and '2012-01-10'/*63.161000*/
[解决办法]
SQL code
declare @a table(id int identity,contractno varchar(10),adddate datetime)insert into @a(contractno,adddate) values('0120252','2011-12-12')insert into @a(contractno,adddate) values('0120373','2012-1-5')insert into @a(contractno,adddate) values('0120389','2012-1-15') declare @b table(id int identity,contractno varchar(10),productname varchar(10),price numeric(4,2),priceunit varchar(4))insert into @b(contractno,productname,price,priceunit) select '0120252','产品1',10.00,'元' union allselect '0120252','产品2',20.00,'元' union allselect '0120373','产品1',5.00,'美元' union allselect '0120373','产品2',5.00,'美元' union allselect '0120389','产品1',15.00,'元'  declare @from datetime,@to datetimeset @from='2011-1-5'set @to='2012-1-15'select SUM(price) total,priceunit from @b where contractno in (select contractno from @a where adddate between @from and @to)group by priceunit
[解决办法]
sum价格总计分类，left join 就可以了。
[解决办法]
SQL code
create table COA(id int,contractno varchar(20),adddate date)create table COB(id int,contractno varchar(20),productname varchar(20),price float,priceUnit varchar(30))insert into COAselect 1,'0120252','2011-12-12' union allselect 2,'0120373','2012-1-5' union allselect 3,'0120389','2012-1-15'insert into COBselect 1,'0120252','产品1',10.00,'元' union allselect 2,'0120252','产品2',20.00,'元' union allselect 3,'0120373','产品1',5.00,'美元' union allselect 4,'0120373','产品2',5.00,'美元' union allselect 5,'0120389','产品1',15.00,'元'select productname ,SUM(case when priceUnit='美元' then price*6.134 else price end) totalPricefrom COAleft join COB on COA.contractno=COB.contractnowhere adddate between '2011-01-01' and '2012-01-10'group by productname;
[解决办法]
不需要转换的：
SQL code 
 
select productname,SUM(price) totalPrice,priceUnitfrom COAleft join COB on COA.contractno=COB.contractnowhere adddate between '2011-01-01' and '2012-01-10'group by productname,priceUnitorder by productname
[解决办法]

select d.ProductName,d.PriceUnit,sum(d.Price) 金额
from A as c,B as d
where c.ContractNo=d.ContractNo and c.AddDate>='2012-1-1' and c.AddDate<='2012-1-10'
group by d.ProductName,d.PriceUnit
order by d.ProductName,d.PriceUnit
[解决办法]
SQL code
select a.contractno,a.adddate,sum(b.price) [total],b.priceunitfrom a left join b on a.id=b.id and a.adddate>='2012-01-01' and a.adddate<='2012-01-10'group by a.contractno,a.adddate,b.priceunitorder by a.adddate asc