22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT t1.学校,
COUNT(1) AS 人数,
RTRIM( (CONVERT(FLOAT, COUNT(1)) /
(
SELECT COUNT(1) FROM (SELECT TOP 1000 * from #T ORDER BY 总分 desc)t2
)
) * 100
) + '%' AS 比例
FROM (SELECT TOP 1000 * from #T ORDER BY 总分 desc)t1
GROUP BY t1.学校;
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([序号] int,[学校] nvarchar(23),[姓名] nvarchar(22),[语文] int,[数学] int,[总分] int)
Insert #T
select 1,N'学校1',N'张1',88,77,165 union all
select 2,N'学校1',N'张2',88,88,176 union all
select 3,N'学校2',N'张3',88,99,187
Go
--测试数据结束
;WITH cte AS(
SELECT TOP 1000 * from #T ORDER BY 总分 desc
)
SELECT cte.学校,
COUNT(1) AS 人数,
RTRIM( (CONVERT(FLOAT, COUNT(1)) /
(
SELECT COUNT(1) FROM cte
)
) * 100
) + '%' AS 比例
FROM cte
GROUP BY cte.学校;
select distinct 学校,
SUM(case when seq<=1000 then 1 else 0 end) over (partition by 学校) as 人数,
cast(cast(((SUM(case when seq<=1000 then 1 else 0 end) over (partition by 学校)*1.0)/SUM(case when seq<=1000 then 1 else 0 end) over ())*100 as decimal(12,2)) as varchar)+'%' as 比例
from
(select *,DENSE_RANK() over (order by 总分 desc) as seq from #T) as A
SELECT t1.学校,
COUNT(1) AS 人数,
RTRIM( (CONVERT(FLOAT, COUNT(1)) /
(
SELECT COUNT(1) FROM (SELECT TOP 1000 * from #T ORDER BY 总分 desc)t2
)
) * 100
) + '%' AS 比例
FROM (SELECT TOP 1000 * from #T ORDER BY 总分 desc)t1
GROUP BY t1.学校;
[/quote]
想问一下这里为什么要区分t1和t2呢[/quote]
习惯……不喜欢把临时表的名字写成一样的,虽然有时候没错SELECT t1.学校,
COUNT(1) AS 人数,
RTRIM( (CONVERT(FLOAT, COUNT(1)) /
(
SELECT COUNT(1) FROM (SELECT TOP 1000 * from #T ORDER BY 总分 desc)t2
)
) * 100
) + '%' AS 比例
FROM (SELECT TOP 1000 * from #T ORDER BY 总分 desc)t1
GROUP BY t1.学校;
[/quote]
想问一下这里为什么要区分t1和t2呢