在线求解分组统计占比复杂sql语句问题

时间：2022-09-01 02:59:05
现在有表如下： 


--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2

求下面结果
教师  1分占比     2分占比      3分占比       4分占比       5分占比
张三    0.6667        0           0            0             0.3333
李四      0         0.3333      0.3333         0.3333         0
王五     0            1            0            0             0



占比=该名老师得某分出现次数 / 该老师得分出现总次数  
例如：张三   得1分2次  5分1 次 那么 1分占比=2/3

求一句sql实现，最好不用临时表

15 个解决方案

#1

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2



select

  教师,

  cast(sum(case 评分 when 1 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '1分占比',

  cast(sum(case 评分 when 2 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '2分占比',

  cast(sum(case 评分 when 3 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '3分占比',

  cast(sum(case 评分 when 4 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '4分占比',

  cast(sum(case 评分 when 5 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '5分占比'

from

  tb

group by

  教师

/*教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五   0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三   0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)

*/

#2

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2



SELECT *

FROM (

	SELECT 教师,

		评分=LTRIM(评分)+'分占比',

		分占比例=CAST(COUNT(1)OVER(PARTITION BY [教师],评分)*1./COUNT(1)OVER(PARTITION BY [教师]) AS DEC(9,4))

	FROM TB

) A

	PIVOT(MAX(分占比例) FOR 评分 IN([1分占比],[2分占比],[3分占比],[4分占比],[5分占比])) P

/*

教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   NULL                                    0.3333                                  0.3333                                  0.3333                                  NULL

王五   NULL                                    1.0000                                  NULL                                    NULL                                    NULL

张三   0.6667                                  NULL                                    NULL                                    NULL                                    0.3333


(3 行受影响)

*/

#3

select 教师,isnull([1分占比],0) as [1分占比]
,isnull([2分占比],0) as [2分占比]
,isnull([3分占比],0) as [3分占比]
,isnull([4分占比],0) as [4分占比]
,isnull([5分占比],0) as [5分占比]
from (
select 教师,ltrim(评分)+'分占比' as 评分,cast(1.0*count(*) over(partition by 教师,评分)/count(*) over(partition by 教师)  as decimal(18,4)) as rate 
from [TB]
) a
pivot(max(rate) for 评分 in([1分占比],[2分占比],[3分占比],[4分占比],[5分占比]))pvt
教师                                      1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比
---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
李四                                    0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000
王五                                    0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000
张三                                    0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333

(3 行受影响)

#4

select 教师,isnull([1分占比],0) as [1分占比]

,isnull([2分占比],0) as [2分占比]

,isnull([3分占比],0) as [3分占比]

,isnull([4分占比],0) as [4分占比]

,isnull([5分占比],0) as [5分占比]

from (

select 教师,ltrim(评分)+'分占比' as 评分,cast(1.0*count(*) over(partition by 教师,评分)/count(*) over(partition by 教师)  as decimal(18,4)) as rate 

from [TB]

) a

pivot(max(rate) for 评分 in([1分占比],[2分占比],[3分占比],[4分占比],[5分占比]))pvt

教师                                      1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四                                    0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五                                    0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三                                    0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)

#5

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select [教师],

       sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) '1分占比',

       sum(case when [评分]=2 then 1 else 0 end)*1.0/count(*) '2分占比',

       sum(case when [评分]=3 then 1 else 0 end)*1.0/count(*) '3分占比',

       sum(case when [评分]=4 then 1 else 0 end)*1.0/count(*) '4分占比',

       sum(case when [评分]=5 then 1 else 0 end)*1.0/count(*) '5分占比'

from [TB]

group by [教师]


教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.000000000000                          0.333333333333                          0.333333333333                          0.333333333333                          0.000000000000

王五   0.000000000000                          1.000000000000                          0.000000000000                          0.000000000000                          0.000000000000

张三   0.666666666666                          0.000000000000                          0.000000000000                          0.000000000000                          0.333333333333


(3 行受影响)

#6

declare @t table(fn varchar(4),fc int)

insert @t

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select *

from @t


select fn

,sum(case when fc=1 then 1 else 0 end)*1.0/count(*) f1

,sum(case when fc=2 then 1 else 0 end)*1.0/count(*) f2

,sum(case when fc=3 then 1 else 0 end)*1.0/count(*) f3

,sum(case when fc=4 then 1 else 0 end)*1.0/count(*) f4

,sum(case when fc=5 then 1 else 0 end)*1.0/count(*) f5

from @t

group by fn

#7

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select [教师],

       cast(sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '1分占比',

       cast(sum(case when [评分]=2 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '2分占比',

       cast(sum(case when [评分]=3 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '3分占比',

       cast(sum(case when [评分]=4 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '4分占比',

       cast(sum(case when [评分]=5 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '5分占比'

from [TB]

group by [教师]


教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五   0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三   0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)

#8

fn	f1	f2	f3	f4	f5

李四	0.000000000000	0.333333333333	0.333333333333	0.333333333333	0.000000000000

王五	0.000000000000	1.000000000000	0.000000000000	0.000000000000	0.000000000000

张三	0.666666666666	0.000000000000	0.000000000000	0.000000000000	0.333333333333

#9

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


DECLARE @S VARCHAR(8000),@S1 VARCHAR(8000)

SELECT @S=ISNULL(@S+',','')+'['+LTRIM([评分])+'分占比]' FROM TB GROUP BY [评分]

SELECT @S1=ISNULL(@S1+',','')+'['+LTRIM([评分])+'分占比]=ISNULL(['+LTRIM([评分])+'分占比],0)' FROM TB GROUP BY 评分

EXEC('

	SELECT 教师,'+@S1+'

	FROM (

		SELECT 教师,

			评分=LTRIM(评分)+''分占比'',

			分占比例=CAST(COUNT(1)OVER(PARTITION BY [教师],评分)*1./COUNT(1)OVER(PARTITION BY [教师]) AS DEC(9,4))

		FROM TB

	) A

		PIVOT(MAX(分占比例) FOR 评分 IN('+@S+')) P'

)

/*

教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五   0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三   0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)


*/
SQL2005动态

#10

忘记说了，分数是不定的，最大是100分，所以
像 


select [教师],

       cast(sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '1分占比',

       cast(sum(case when [评分]=2 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '2分占比',

       cast(sum(case when [评分]=3 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '3分占比',

       cast(sum(case when [评分]=4 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '4分占比',

       cast(sum(case when [评分]=5 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '5分占比'

from [TB]

这样的语句是不行的，最好是动态的，我的数据库是sql2k

#11

引用 10 楼 aawwmate 的回复:
忘记说了，分数是不定的，最大是100分，所以
像

SQL code

select [教师],
       cast(sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '1分占比',
       cast(sum(case when [评分]=2 then 1 else 0 end)*1……

那就用动态的

#12

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('张三' , '语文' , 74)

insert into tb values('张三' , '数学' , 83)

insert into tb values('张三' , '物理' , 93)

insert into tb values('李四' , '语文' , 74)

insert into tb values('李四' , '数学' , 84)

insert into tb values('李四' , '物理' , 94)



select UserName,sum(case when Subject= '数学' then Score else 0 end) [数学],sum(case when Subject= '物理' then Score else 0 end) [物理],sum(case when Subject= '语文' then Score else 0 end) [语文]

declare @sql varchar(1000)

set @sql='select UserName'

select @sql=@sql+',sum(case when Subject= ''' +Subject+ ''' then Score else 0 end) ['+Subject+']' from (select distinct Subject from tb)a


set @sql = @sql + ' from tb group by UserName'

print @sql

exec(@sql) 



--讲解:


--这个是第一次执行

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], 

--这个是第二次

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , 

--这个是第三次

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , max(case 课程 when '语文' then 分数 else 0 end) [语文] 

--这个的数量来自于

(select distinct 课程 from tb)--这里只有3们课程




create table tb(id int, value varchar(10))

insert into tb values(1, 'aa')

insert into tb values(1, 'bb')

insert into tb values(2, 'aaa')

insert into tb values(2, 'bbb')

insert into tb values(2, 'ccc')

go


create function dbo.f_str(@id int) returns varchar(100)

as

begin

    declare @str varchar(1000)

    set @str = ''

    select @str = @str + ',' + cast(value as varchar) from tb where id = @id

    set @str = right(@str , len(@str) - 1)

    return @str

end

go


--调用函数

select id , value = dbo.f_str(id) from tb group by id


drop function dbo.f_str

drop table tb



我只说一个地方 

select @str = @str + ',' + cast(value as varchar) from tb where id = @id 

你把这个看懂就明白了 

例如当@id=1 

select @str = @str + ',' + cast(value as varchar) from tb where id = 1 

把满足id=1的str值通过','累加 

当id是动态的就是1或者2...是当满足1的查询完了,把值付给str之后 

在查询满足2的直到所有的ID完为止 

这样明白了吧 





本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/03/17/3999394.aspx

#13

引用 10 楼 aawwmate 的回复:
忘记说了，分数是不定的，最大是100分


那分数不能放在列上了 

declare @t table(fn varchar(4),fc int)

insert @t

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select *

from @t


select n.fn,s.fc,isnull(a.f*1.0/b.f,0) f

from 

(

select distinct fc

from @t

) s 

left join

(

select distinct fn

from @t

) n on 1=1 

left join 

(

select

fn,fc,count(*) f

from @t

group by fn,fc

) a on a.fc=s.fc and a.fn=n.fn

left join (

select fn,count(*) f

from @t

group by fn

) b on a.fn=b.fn

order by n.fn,s.fc


结果：

fn	fc	f

李四	1	0.000000000000

李四	2	0.333333333333

李四	3	0.333333333333

李四	4	0.333333333333

李四	5	0.000000000000

王五	1	0.000000000000

王五	2	1.000000000000

王五	3	0.000000000000

王五	4	0.000000000000

王五	5	0.000000000000

张三	1	0.666666666666

张三	2	0.000000000000

张三	3	0.000000000000

张三	4	0.000000000000

张三	5	0.333333333333

#14



if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select 教师,(count(case when 评分=1 then 1 end)*1.0/count(*)) as [1分占比],

(count(case when 评分=2 then 1 end)*1.00/count(*)) as [2分占比],

(count(case when 评分=3 then 1 end)*1.00/count(*)) as [3分占比],

(count(case when 评分=4 then 1 end)*1.00/count(*)) as [4分占比],

(count(case when 评分=5 then 1 end)*1.00/count(*)) as [5分占比]

from TB

group by 教师


#15

引用 10 楼 aawwmate 的回复:
忘记说了，分数是不定的，最大是100分，所以
像

SQL code

select [教师],
       cast(sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '1分占比',
       cast(sum(case when [评分]=2 then 1 else 0 end)*1…… 


--> 测试数据：[TB]

IF OBJECT_ID('[TB]') IS NOT NULL DROP TABLE [TB]

CREATE TABLE [TB]([教师] VARCHAR(4),[评分] INT)

INSERT [TB]

SELECT '张三',1 UNION ALL

SELECT '李四',2 UNION ALL

SELECT '王五',2 UNION ALL

SELECT '张三',1 UNION ALL

SELECT '李四',3 UNION ALL

SELECT '张三',5 UNION ALL

SELECT '李四',4 UNION ALL

SELECT '王五',2 UNION ALL

SELECT '王五',2


DECLARE @S VARCHAR(8000),@S1 VARCHAR(8000)

SELECT @S='SELECT 教师'

SELECT @S=@S+',CAST(SUM(CASE [评分] WHEN '+ LTRIM(评分) +' THEN 1 ELSE 0 END)*1.0/COUNT(*) AS DECIMAL(9,4))['+LTRIM(评分)+'分占比]'

FROM TB GROUP BY 评分

SELECT @S=@S+' FROM TB GROUP BY 教师 '

EXEC (@S)

/*

教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五   0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三   0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)

*/

SQL2000动态

#1

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2



select

  教师,

  cast(sum(case 评分 when 1 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '1分占比',

  cast(sum(case 评分 when 2 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '2分占比',

  cast(sum(case 评分 when 3 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '3分占比',

  cast(sum(case 评分 when 4 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '4分占比',

  cast(sum(case 评分 when 5 then 1 else 0 end)*1.0/count(1) as dec(18,4)) as '5分占比'

from

  tb

group by

  教师

/*教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五   0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三   0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)

*/

#2

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2



SELECT *

FROM (

	SELECT 教师,

		评分=LTRIM(评分)+'分占比',

		分占比例=CAST(COUNT(1)OVER(PARTITION BY [教师],评分)*1./COUNT(1)OVER(PARTITION BY [教师]) AS DEC(9,4))

	FROM TB

) A

	PIVOT(MAX(分占比例) FOR 评分 IN([1分占比],[2分占比],[3分占比],[4分占比],[5分占比])) P

/*

教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   NULL                                    0.3333                                  0.3333                                  0.3333                                  NULL

王五   NULL                                    1.0000                                  NULL                                    NULL                                    NULL

张三   0.6667                                  NULL                                    NULL                                    NULL                                    0.3333


(3 行受影响)

*/

#3

select 教师,isnull([1分占比],0) as [1分占比]
,isnull([2分占比],0) as [2分占比]
,isnull([3分占比],0) as [3分占比]
,isnull([4分占比],0) as [4分占比]
,isnull([5分占比],0) as [5分占比]
from (
select 教师,ltrim(评分)+'分占比' as 评分,cast(1.0*count(*) over(partition by 教师,评分)/count(*) over(partition by 教师)  as decimal(18,4)) as rate 
from [TB]
) a
pivot(max(rate) for 评分 in([1分占比],[2分占比],[3分占比],[4分占比],[5分占比]))pvt
教师                                      1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比
---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
李四                                    0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000
王五                                    0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000
张三                                    0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333

(3 行受影响)

#4

select 教师,isnull([1分占比],0) as [1分占比]

,isnull([2分占比],0) as [2分占比]

,isnull([3分占比],0) as [3分占比]

,isnull([4分占比],0) as [4分占比]

,isnull([5分占比],0) as [5分占比]

from (

select 教师,ltrim(评分)+'分占比' as 评分,cast(1.0*count(*) over(partition by 教师,评分)/count(*) over(partition by 教师)  as decimal(18,4)) as rate 

from [TB]

) a

pivot(max(rate) for 评分 in([1分占比],[2分占比],[3分占比],[4分占比],[5分占比]))pvt

教师                                      1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四                                    0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五                                    0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三                                    0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)

#5

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select [教师],

       sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) '1分占比',

       sum(case when [评分]=2 then 1 else 0 end)*1.0/count(*) '2分占比',

       sum(case when [评分]=3 then 1 else 0 end)*1.0/count(*) '3分占比',

       sum(case when [评分]=4 then 1 else 0 end)*1.0/count(*) '4分占比',

       sum(case when [评分]=5 then 1 else 0 end)*1.0/count(*) '5分占比'

from [TB]

group by [教师]


教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.000000000000                          0.333333333333                          0.333333333333                          0.333333333333                          0.000000000000

王五   0.000000000000                          1.000000000000                          0.000000000000                          0.000000000000                          0.000000000000

张三   0.666666666666                          0.000000000000                          0.000000000000                          0.000000000000                          0.333333333333


(3 行受影响)

#6

declare @t table(fn varchar(4),fc int)

insert @t

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select *

from @t


select fn

,sum(case when fc=1 then 1 else 0 end)*1.0/count(*) f1

,sum(case when fc=2 then 1 else 0 end)*1.0/count(*) f2

,sum(case when fc=3 then 1 else 0 end)*1.0/count(*) f3

,sum(case when fc=4 then 1 else 0 end)*1.0/count(*) f4

,sum(case when fc=5 then 1 else 0 end)*1.0/count(*) f5

from @t

group by fn

#7

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select [教师],

       cast(sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '1分占比',

       cast(sum(case when [评分]=2 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '2分占比',

       cast(sum(case when [评分]=3 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '3分占比',

       cast(sum(case when [评分]=4 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '4分占比',

       cast(sum(case when [评分]=5 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '5分占比'

from [TB]

group by [教师]


教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五   0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三   0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)

#8

fn	f1	f2	f3	f4	f5

李四	0.000000000000	0.333333333333	0.333333333333	0.333333333333	0.000000000000

王五	0.000000000000	1.000000000000	0.000000000000	0.000000000000	0.000000000000

张三	0.666666666666	0.000000000000	0.000000000000	0.000000000000	0.333333333333

#9

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


DECLARE @S VARCHAR(8000),@S1 VARCHAR(8000)

SELECT @S=ISNULL(@S+',','')+'['+LTRIM([评分])+'分占比]' FROM TB GROUP BY [评分]

SELECT @S1=ISNULL(@S1+',','')+'['+LTRIM([评分])+'分占比]=ISNULL(['+LTRIM([评分])+'分占比],0)' FROM TB GROUP BY 评分

EXEC('

	SELECT 教师,'+@S1+'

	FROM (

		SELECT 教师,

			评分=LTRIM(评分)+''分占比'',

			分占比例=CAST(COUNT(1)OVER(PARTITION BY [教师],评分)*1./COUNT(1)OVER(PARTITION BY [教师]) AS DEC(9,4))

		FROM TB

	) A

		PIVOT(MAX(分占比例) FOR 评分 IN('+@S+')) P'

)

/*

教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五   0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三   0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)


*/
SQL2005动态

#10

忘记说了，分数是不定的，最大是100分，所以
像 


select [教师],

       cast(sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '1分占比',

       cast(sum(case when [评分]=2 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '2分占比',

       cast(sum(case when [评分]=3 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '3分占比',

       cast(sum(case when [评分]=4 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '4分占比',

       cast(sum(case when [评分]=5 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '5分占比'

from [TB]

这样的语句是不行的，最好是动态的，我的数据库是sql2k

#11

引用 10 楼 aawwmate 的回复:
忘记说了，分数是不定的，最大是100分，所以
像

SQL code

select [教师],
       cast(sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '1分占比',
       cast(sum(case when [评分]=2 then 1 else 0 end)*1……

那就用动态的

#12

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('张三' , '语文' , 74)

insert into tb values('张三' , '数学' , 83)

insert into tb values('张三' , '物理' , 93)

insert into tb values('李四' , '语文' , 74)

insert into tb values('李四' , '数学' , 84)

insert into tb values('李四' , '物理' , 94)



select UserName,sum(case when Subject= '数学' then Score else 0 end) [数学],sum(case when Subject= '物理' then Score else 0 end) [物理],sum(case when Subject= '语文' then Score else 0 end) [语文]

declare @sql varchar(1000)

set @sql='select UserName'

select @sql=@sql+',sum(case when Subject= ''' +Subject+ ''' then Score else 0 end) ['+Subject+']' from (select distinct Subject from tb)a


set @sql = @sql + ' from tb group by UserName'

print @sql

exec(@sql) 



--讲解:


--这个是第一次执行

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], 

--这个是第二次

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , 

--这个是第三次

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , max(case 课程 when '语文' then 分数 else 0 end) [语文] 

--这个的数量来自于

(select distinct 课程 from tb)--这里只有3们课程




create table tb(id int, value varchar(10))

insert into tb values(1, 'aa')

insert into tb values(1, 'bb')

insert into tb values(2, 'aaa')

insert into tb values(2, 'bbb')

insert into tb values(2, 'ccc')

go


create function dbo.f_str(@id int) returns varchar(100)

as

begin

    declare @str varchar(1000)

    set @str = ''

    select @str = @str + ',' + cast(value as varchar) from tb where id = @id

    set @str = right(@str , len(@str) - 1)

    return @str

end

go


--调用函数

select id , value = dbo.f_str(id) from tb group by id


drop function dbo.f_str

drop table tb



我只说一个地方 

select @str = @str + ',' + cast(value as varchar) from tb where id = @id 

你把这个看懂就明白了 

例如当@id=1 

select @str = @str + ',' + cast(value as varchar) from tb where id = 1 

把满足id=1的str值通过','累加 

当id是动态的就是1或者2...是当满足1的查询完了,把值付给str之后 

在查询满足2的直到所有的ID完为止 

这样明白了吧 





本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/03/17/3999394.aspx

#13

引用 10 楼 aawwmate 的回复:
忘记说了，分数是不定的，最大是100分


那分数不能放在列上了 

declare @t table(fn varchar(4),fc int)

insert @t

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select *

from @t


select n.fn,s.fc,isnull(a.f*1.0/b.f,0) f

from 

(

select distinct fc

from @t

) s 

left join

(

select distinct fn

from @t

) n on 1=1 

left join 

(

select

fn,fc,count(*) f

from @t

group by fn,fc

) a on a.fc=s.fc and a.fn=n.fn

left join (

select fn,count(*) f

from @t

group by fn

) b on a.fn=b.fn

order by n.fn,s.fc


结果：

fn	fc	f

李四	1	0.000000000000

李四	2	0.333333333333

李四	3	0.333333333333

李四	4	0.333333333333

李四	5	0.000000000000

王五	1	0.000000000000

王五	2	1.000000000000

王五	3	0.000000000000

王五	4	0.000000000000

王五	5	0.000000000000

张三	1	0.666666666666

张三	2	0.000000000000

张三	3	0.000000000000

张三	4	0.000000000000

张三	5	0.333333333333

#14



if object_id('[TB]') is not null drop table [TB]

create table [TB]([教师] varchar(4),[评分] int)

insert [TB]

select '张三',1 union all

select '李四',2 union all

select '王五',2 union all

select '张三',1 union all

select '李四',3 union all

select '张三',5 union all

select '李四',4 union all

select '王五',2 union all

select '王五',2


select 教师,(count(case when 评分=1 then 1 end)*1.0/count(*)) as [1分占比],

(count(case when 评分=2 then 1 end)*1.00/count(*)) as [2分占比],

(count(case when 评分=3 then 1 end)*1.00/count(*)) as [3分占比],

(count(case when 评分=4 then 1 end)*1.00/count(*)) as [4分占比],

(count(case when 评分=5 then 1 end)*1.00/count(*)) as [5分占比]

from TB

group by 教师


#15

引用 10 楼 aawwmate 的回复:
忘记说了，分数是不定的，最大是100分，所以
像

SQL code

select [教师],
       cast(sum(case when [评分]=1 then 1 else 0 end)*1.0/count(*) as decimal(19,4)) '1分占比',
       cast(sum(case when [评分]=2 then 1 else 0 end)*1…… 


--> 测试数据：[TB]

IF OBJECT_ID('[TB]') IS NOT NULL DROP TABLE [TB]

CREATE TABLE [TB]([教师] VARCHAR(4),[评分] INT)

INSERT [TB]

SELECT '张三',1 UNION ALL

SELECT '李四',2 UNION ALL

SELECT '王五',2 UNION ALL

SELECT '张三',1 UNION ALL

SELECT '李四',3 UNION ALL

SELECT '张三',5 UNION ALL

SELECT '李四',4 UNION ALL

SELECT '王五',2 UNION ALL

SELECT '王五',2


DECLARE @S VARCHAR(8000),@S1 VARCHAR(8000)

SELECT @S='SELECT 教师'

SELECT @S=@S+',CAST(SUM(CASE [评分] WHEN '+ LTRIM(评分) +' THEN 1 ELSE 0 END)*1.0/COUNT(*) AS DECIMAL(9,4))['+LTRIM(评分)+'分占比]'

FROM TB GROUP BY 评分

SELECT @S=@S+' FROM TB GROUP BY 教师 '

EXEC (@S)

/*

教师   1分占比                                    2分占比                                    3分占比                                    4分占比                                    5分占比

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

李四   0.0000                                  0.3333                                  0.3333                                  0.3333                                  0.0000

王五   0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000

张三   0.6667                                  0.0000                                  0.0000                                  0.0000                                  0.3333


(3 行受影响)

*/

SQL2000动态
标签：

相关文章