有两张表,如下:
表1
name | code | item | data | result
a 01 i1 60 合格
b 02 i2 30 不合格
c 03 i1 90 合格
b 02 i2 65 合格
a 01 i1 50 不合格
c 03 i1 79 合格
b 02 i1 80 合格表2
code | value
01 1
02 2
03 1
我要得到当item=i1时,下面这几个数据:
1.name
2.每种name所有的data的平均值*code所对应的value值,比如说,
select t1.name,t1.code,t2.value,t1.data from 表1 t1 left join 表2 t2 on t2.code=t1.code order by t1.name 得到
name | code | value | data
a 01 1 60
a 01 1 50
b 02 2 80
c 03 1 90
c 03 1 79
而我要的是这样的结果
name | value | data
a 1 55------->即(60+50)/2 * 1 即avg(data)group by name
b 2 160------>即(80)/1 * 2
c 1 84.5---------->同上
3.跟上面类似,但加了一个条件,即每种name中result=‘合格’的data占所有data数的比重*code所对应的value值,同上:
select t1.name,t1.code,t2.value,t1.data,t1.result from 表1 t1 left join 表2 t2 on t2.code=t1.code order by t1.name 得到
name | code | value | data | result
a 01 1 60 合格
a 01 1 50 不合格
b 02 2 80 合格
c 03 1 90 合格
c 03 1 79 合格
我要的结果是
name | value | data
a 1 30------->即(60)/2 * 1 即sum(data)/count(data) where result='合格' group by name
b 2 160------>同上
c 1 84.5---------->同上
4.跟上面的一样,不过条件改为result=‘不合格’
如果要把上面的放到一起,即去的如下几个字段:
name | value | data1(全部) | data2(合格) | data3(不合格)
a 1 55 30 25
b 2 160 160
c 1 84.5 84.5
各位大哥大姐,请问我该怎么做
表1
name | code | item | data | result
a 01 i1 60 合格
b 02 i2 30 不合格
c 03 i1 90 合格
b 02 i2 65 合格
a 01 i1 50 不合格
c 03 i1 79 合格
b 02 i1 80 合格表2
code | value
01 1
02 2
03 1
我要得到当item=i1时,下面这几个数据:
1.name
2.每种name所有的data的平均值*code所对应的value值,比如说,
select t1.name,t1.code,t2.value,t1.data from 表1 t1 left join 表2 t2 on t2.code=t1.code order by t1.name 得到
name | code | value | data
a 01 1 60
a 01 1 50
b 02 2 80
c 03 1 90
c 03 1 79
而我要的是这样的结果
name | value | data
a 1 55------->即(60+50)/2 * 1 即avg(data)group by name
b 2 160------>即(80)/1 * 2
c 1 84.5---------->同上
3.跟上面类似,但加了一个条件,即每种name中result=‘合格’的data占所有data数的比重*code所对应的value值,同上:
select t1.name,t1.code,t2.value,t1.data,t1.result from 表1 t1 left join 表2 t2 on t2.code=t1.code order by t1.name 得到
name | code | value | data | result
a 01 1 60 合格
a 01 1 50 不合格
b 02 2 80 合格
c 03 1 90 合格
c 03 1 79 合格
我要的结果是
name | value | data
a 1 30------->即(60)/2 * 1 即sum(data)/count(data) where result='合格' group by name
b 2 160------>同上
c 1 84.5---------->同上
4.跟上面的一样,不过条件改为result=‘不合格’
如果要把上面的放到一起,即去的如下几个字段:
name | value | data1(全部) | data2(合格) | data3(不合格)
a 1 55 30 25
b 2 160 160
c 1 84.5 84.5
各位大哥大姐,请问我该怎么做
set nocount on ;
if not object_id('Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([name] nvarchar(1),[code] nvarchar(2),[item] nvarchar(2),[data] int,[result] nvarchar(3))
Insert #T1
select N'a',N'01',N'i1',60,N'合格' union all
select N'b',N'02',N'i2',30,N'不合格' union all
select N'c',N'03',N'i1',90,N'合格' union all
select N'b',N'02',N'i2',65,N'合格' union all
select N'a',N'01',N'i1',50,N'不合格' union all
select N'c',N'03',N'i1',79,N'合格' union all
select N'b',N'02',N'i1',80,N'合格'
Goif not object_id('Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([code] nvarchar(2),[value] int)
Insert #T2
select N'01',1 union all
select N'02',2 union all
select N'03',1
Go
Select
a.name,b.[value],cast( avg(a.[data]*1.)*b.[value] as decimal(18,2))[data]
from
#T1 a
left join
#T2 b on a.[code]=b.[code]
where item=N'i1'
group by all a.name,b.[value]
order by a.[name]Select
a.name,b.[value],cast(sum(case when [result]=N'合格' then a.[data]*b.[value] else 0 end)*1./count(1) as decimal(18,2))[data]
from
#T1 a
left join
#T2 b on a.[code]=b.[code]
where item=N'i1'
group by all a.name,b.[value]
order by a.[name]
Select
a.name,b.[value],
cast(sum(case when [result]=N'合格' then a.[data]*b.[value] else 0 end)*1./count(1) as decimal(18,2))[合格],
cast(sum(case when [result]='不合格' then a.[data]*b.[value] else 0 end)*1./count(1) as decimal(18,2))[不合格]
from
#T1 a
left join
#T2 b on a.[code]=b.[code]
where item=N'i1'
group by all a.name,b.[value]
order by a.[name]/*
name value data
---- ----------- ---------------------------------------
a 1 55.00
b 2 160.00
c 1 84.50name value data
---- ----------- ---------------------------------------
a 1 30.00
b 2 160.00
c 1 84.50
name value 合格 不合格
---- ----------- --------------------------------------- ---------------------------------------
a 1 30.00 25.00
b 2 160.00 0.00
c 1 84.50 0.00
*/