27,579
社区成员
发帖
与我相关
我的任务
分享
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1
If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert t1
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
--Select * from t1
-->SQL查询如下:
Select p_id,
avg1=max(case when type=1 then avgprice else 0 end),
avg2=max(case when type=2 then avgprice else 0 end)
From (
Select [type],[p_id],
sum([money]*[num])/case (select sum([num]) from t1) when 0 then 1 else (select sum([num]) from t1) end avgprice
From t1
Group by [type],[p_id]
) as t
Group by p_id
/*
p_id avg1 avg2
----------- --------------------------------------- ---------------------------------------
1 0.350000 0.333333
2 0.122222 0.500000
(2 行受影响)
*/
如果是除以总量的,这样.If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert tb
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
select p_id,MAX(avg1) as avg1 ,MAX(avg2) as avg2
from(
select p_id,
case when TYPE=1 then sum([money]*[num])/sum(num) end as avg1,
case when TYPE=2 then sum([money]*[num])/sum(num) end as avg2
from tb
group by p_id,type) t
group by p_id
/*
1 1.260000 1.200000
2 1.100000 1.500000
*/
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert tb
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
declare @sum int
set @sum=(select SUM(num) from tb )
select p_id,MAX(avg1) as avg1 ,MAX(avg2) as avg2
from(
select p_id,
case when TYPE=1 then sum([money]*[num])/@sum end as avg1,
case when TYPE=2 then sum([money]*[num])/@sum end as avg2
from tb
group by p_id,type) t
group by p_id
/*、
p_id avg1 avg2
1 0.350000 0.333333
2 0.122222 0.500000
*/
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1
If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert t1
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
--Select * from t1
-->SQL查询如下:
Select p_id,
avg1=max(case when type=1 then avgprice else 0 end),
avg2=max(case when type=2 then avgprice else 0 end)
From (
Select [type],[p_id],sum([money]*[num])/case sum([num]) when 0 then 1 else sum([num]) end avgprice
From t1
Group by [type],[p_id]
) as t
Group by p_id
/*
p_id avg1 avg2
----------- --------------------------------------- ---------------------------------------
1 1.260000 1.200000
2 1.100000 1.500000
(2 行受影响)
*/
Select [type],sum([money]*[num])/case sum([num]) when 0 then 1 else sum([num]) end avgprice
From test
Group by type
select sum(money*num)/sum(num) from test group by type
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1
If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert t1
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
--Select * from t1
-->SQL查询如下:
Select [type],[p_id],sum([money]*[num])/case sum([num]) when 0 then 1 else sum([num]) end avgprice
From t1
Group by [type],[p_id]
/*
type p_id avgprice
----------- ----------- ---------------------------------------
1 1 1.260000
2 1 1.200000
1 2 1.100000
2 2 1.500000
(4 行受影响)
*/
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1
If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert t1
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
--Select * from t1
-->SQL查询如下:
Select [type],sum([money]*[num])/case sum([num]) when 0 then 1 else sum([num]) end avgprice
From t1
Group by [type]
/*
type avgprice
----------- ---------------------------------------
1 1.214285
2 1.363636
(2 行受影响)
*/
SELECT
TYPE,TOTAL/NULLIF(NUM,0) FROM
(SELECT SUM(TOTAL)TOTAL,TYPE,SUM(NUM)NUM FROM
(SELECT [money ]*NUM AS TOTAL,type ,NUM FROM TB ) GROUP BY TYPE)AS T1
??