求一sql查询

Mark杨 2010-10-15 10:29:41



declare @sales table (wp nvarchar(10),amount int,price money)

declare @detail table (wp nvarchar(10),total int,price money)



insert @detail 

select 'A1',9,12.4 union all

select 'A2',9,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A5',5,11 union all

select 'A6',2,16



insert @sales 

select 'A1',6,12.4 union all

select 'A1',3,12.4 union all

select 'A2',7,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A4',6,3.5 union all

select 'A5',5,11 





/* 要的结果

wp  TPrice

A1   111.6  --(12.4*9)

A2   59.5   --(8.5*7)

A3   50.4   --(16.8*3)

A4   21.0   --(3.5*6)

A5   55     --(11*5)



说明: 

	1.sales表中wp有重复记录的,amount取detail表中的total。例如A1,A4

	2.sales表中的amount不一定等于detail表中的total。例如A2

*/



--我自己写的 感觉速度有点慢 有什么更好的方法吗？

select * from 

(

	select d.wp,sum(price*total) tprice from @detail d where d.wp in

	(

	select wp from @sales group by wp having count(1) > 1

	) group by d.wp



	union all



	select s.wp,sum(s.price*amount) tprice from @sales s inner join @detail d on s.wp = d.wp where s.wp not in

	(

	select wp from @sales group by wp having count(1) > 1

	) group by s.wp

) t



/*

wp         tprice                

---------- --------------------- 

A1         111.6000

A4         21.0000

A2         59.5000

A3         50.4000

A5         55.0000

*/

...全文

125 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

fpzgm 2010-10-15

打赏
举报



  declare @sales table (wp nvarchar(10),amount int,price money)

declare @detail table (wp nvarchar(10),total int,price money)



insert @detail 

select 'A1',9,12.4 union all

select 'A2',9,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A5',5,11 union all

select 'A6',2,16



insert @sales 

select 'A1',6,12.4 union all

select 'A1',3,12.4 union all

select 'A2',7,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A4',6,3.5 union all

select 'A5',5,11 

Administrator 11:01:44

   declare @sales table (wp nvarchar(10),amount int,price money)

declare @detail table (wp nvarchar(10),total int,price money)



insert @detail 

select 'A1',9,12.4 union all

select 'A2',9,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A5',5,11 union all

select 'A6',2,16



insert @sales 

select 'A1',6,12.4 union all

select 'A1',3,12.4 union all

select 'A2',7,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A4',6,3.5 union all

select 'A5',5,11 





select a.wp,case when a.num=1 then a.tprice else b.price*b.total end as tprice

from ( select wp,COUNT(*) num,SUM(amount*price) tprice from @sales group by wp) a

left join @detail b on a.wp=b.wp



/*

wp         tprice                

---------- --------------------- 

A1         111.6000

A2         59.5000

A3         50.4000

A4         21.0000

A5         55.0000



（所影响的行数为 5 行）

*/

chuifengde 2010-10-15

打赏
举报

declare @sales table (wp nvarchar(10),amount int,price money)

declare @detail table (wp nvarchar(10),total int,price money)



insert @sales 

select 'A1',6,12.4 union all

select 'A1',3,12.4 union all

select 'A2',7,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A4',6,3.5 union all

select 'A5',5,11 



insert @detail 

select 'A1',9,12.4 union all

select 'A2',9,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A5',5,11 union all

select 'A6',2,16



SELECT a.wp,CASE WHEN COUNT(1)>1 THEN MAX(b.total)*max(b.price) ELSE sum(a.amount*a.price) END tPirce

FROM @sales a LEFT JOIN @detail b ON a.wp=b.wp

GROUP BY a.wp



/*wp         tPirce                

---------- --------------------- 

A1         111.6000

A2         59.5000

A3         50.4000

A4         21.0000

A5         55.0000



（所影响的行数为 5 行）



*/

dawugui 2010-10-15

打赏
举报

貌似这样简单点,如果用case when好象更麻烦.帮顶.

Mark杨 2010-10-15

打赏
举报



/*

wp         tprice                

---------- --------------------- 

A1         111.6000

A4         21.0000

A2         59.5000

A3         50.4000

A5         55.0000

*/



我现在要统计所有的tprice

select sum(p) from

(

select wp , sum(amount * price) p TPrice from @sales t group by wp having count(1) = 1

union all

select wp , sum(total*price) p TPrice from @detail m where wp in (select wp from @sales m group by wp having count(1) > 1)

group by wp

order by wp

) t



除了这种方法 还有别的吗？

Mark杨 2010-10-15

打赏
举报

[Quote=引用 1 楼 chuifengde 的回复:]
是不是这个意思？
salse表中相同的，amount 就sum
salse与detail相同的就取amount为小的？
[/Quote]
不是的

salse表中相同的取detail表中的total
salse表中只有一个的就直接取amount

可以这样理解：
salse表是销售数据,detail表物品详细表

dawugui 2010-10-15

打赏
举报

declare @sales table (wp nvarchar(10),amount int,price money)

declare @detail table (wp nvarchar(10),total int,price money)

insert @detail 

select 'A1',9,12.4 union all

select 'A2',9,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A5',5,11 union all

select 'A6',2,16

insert @sales 

select 'A1',6,12.4 union all

select 'A1',3,12.4 union all

select 'A2',7,8.5 union all

select 'A3',3,16.8 union all

select 'A4',6,3.5 union all

select 'A4',6,3.5 union all

select 'A5',5,11 



select wp , sum(amount * price) TPrice from @sales t group by wp having count(1) = 1

union all

select wp , sum(total*price) TPrice from @detail m where wp in (select wp from @sales m group by wp having count(1) > 1)

group by wp

order by wp



/*

wp         TPrice                

---------- --------------------- 

A1         111.6000

A2         59.5000

A3         50.4000

A4         21.0000

A5         55.0000



（所影响的行数为 5 行）

*/