34,590
社区成员
发帖
与我相关
我的任务
分享求关于sql语句取客户最高分的3条数据总分
rn 分数 姓名
1 8.0 王五
2 7.0 王五
3 4.0 王五
1 10.0 张三
2 9.0 张三
3 9.0 张三
4 8.0 张三
1 9.0 李四
以上的表的结构，想通过上面这张表取出客户最高分的3条记录的总分进行排名，如果小于3条记录则不记录排名。求sql语句，谢谢。
1 8.0 王五
2 7.0 王五
3 4.0 王五
1 10.0 张三
2 9.0 张三
3 9.0 张三
4 8.0 张三
1 9.0 李四
以上的表的结构，想通过上面这张表取出客户最高分的3条记录的总分进行排名，如果小于3条记录则不记录排名。求sql语句，谢谢。
...全文
请发表友善的回复…
发表回复
xiaodongni 2014-09-07
- 打赏
- 举报
WITH test(rn,分数,姓名) AS (
SELECT 1, 8.0,'王五' UNION ALL
SELECT 2, 7.0,'王五' UNION ALL
SELECT 3, 4.0,'王五' UNION ALL
SELECT 1,10.0,'张三' UNION ALL
SELECT 2, 9.0,'张三' UNION ALL
SELECT 3, 9.0,'张三' UNION ALL
SELECT 4, 8.0,'张三' UNION ALL
SELECT 1, 9.0,'李四'
)
select top 3 姓名,SUM(分数) as sums from (select *,COUNT(*)over(PARTITIon by 姓名) as counts from test as t ) as t1
where counts>=3
group by 姓名
order by sums desc
linhua51 2014-09-06
- 打赏
- 举报
如果我是王五想知道自己的排名，这样的语句如何写，将上面查询出来作为一张表a,select * from a where 姓名=‘王五' 查询语句就会出错。
Tiger_Zhao 2014-09-05
- 打赏
- 举报
解决问题请结帖。
linhua51 2014-09-04
- 打赏
- 举报
明白了，谢了，已经解决
唐诗三百首 2014-09-04
- 打赏
- 举报
create table test
(rn int,分数 decimal(5,1),姓名 varchar(10))
insert into test
select 1,8.0,'王五' union all
select 2,7.0,'王五' union all
select 3,4.0,'王五' union all
select 1,10.0,'张三' union all
select 2,9.0,'张三' union all
select 3,9.0,'张三' union all
select 4,8.0,'张三' union all
select 1,9.0,'李四'
select row_number() over(order by sum(分数) desc) '排名',
姓名,
sum(分数) '总分'
from
(select 姓名,
分数,
row_number() over(partition by 姓名 order by 分数 desc) 'rn'
from [test]) t
where rn<=3
group by 姓名
having count(1)=3
/*
排名 姓名 总分
-------------------- ---------- --------------
1 张三 28.0
2 王五 19.0
(2 row(s) affected)
*/
Tiger_Zhao 2014-09-04
- 打赏
- 举报
WITH s(姓名,c) AS (
...
后面的不变
...
后面的不变
linhua51 2014-09-04
- 打赏
- 举报
第一次接触这个，能否帮我写一个真实环境下的sql，我测试一下，这条语句有点看不懂，不知道如何修改。
Tiger_Zhao 2014-09-04
- 打赏
- 举报
演示中用公共表 test 代替了表 test。
真实环境下 test 这一段不需要了。
真实环境下 test 这一段不需要了。
linhua51 2014-09-04
- 打赏
- 举报
WITH test(rn,分数,姓名) ,
s(姓名,c) AS (
SELECT 姓名,COUNT(*)
FROM test
GROUP BY 姓名
HAVING COUNT(*) >= 3
),
o(分数,姓名,n) AS (
SELECT 分数,姓名,
ROW_NUMBER() OVER (PARTITION BY 姓名 ORDER BY 分数 DESC)
FROM test
)
SELECT s.姓名,
SUM(o.分数) 总分数,
RANK() OVER (ORDER BY SUM(o.分数) DESC) 排名
FROM s
JOIN o
ON s.姓名 = o.姓名
WHERE o.n<=3
GROUP BY s.姓名
ORDER BY 排名 这样也出错。
Tiger_Zhao 2014-09-04
- 打赏
- 举报
公用表，类似于子查询。
前面必须加 WITH。
前面必须加 WITH。
linhua51 2014-09-04
- 打赏
- 举报
请问里面的 s(),o() 什么意思，如果我的表名是test，
s(姓名,c) AS (
SELECT 姓名,COUNT(*)
FROM test
GROUP BY 姓名
HAVING COUNT(*) >= 3
),
o(分数,姓名,n) AS (
SELECT 分数,姓名,
ROW_NUMBER() OVER (PARTITION BY 姓名 ORDER BY 分数 DESC)
FROM test
)
SELECT s.姓名,
SUM(o.分数) 总分数,
RANK() OVER (ORDER BY SUM(o.分数) DESC) 排名
FROM s
JOIN o
ON s.姓名 = o.姓名
WHERE o.n<=3
GROUP BY s.姓名
ORDER BY 排名 。执行这样的语句会出错。
Tiger_Zhao 2014-09-04
- 打赏
- 举报
WITH test(rn,分数,姓名) AS (
SELECT 1, 8.0,'王五' UNION ALL
SELECT 2, 7.0,'王五' UNION ALL
SELECT 3, 4.0,'王五' UNION ALL
SELECT 1,10.0,'张三' UNION ALL
SELECT 2, 9.0,'张三' UNION ALL
SELECT 3, 9.0,'张三' UNION ALL
SELECT 4, 8.0,'张三' UNION ALL
SELECT 1, 9.0,'李四'
),
s(姓名,c) AS (
SELECT 姓名,COUNT(*)
FROM test
GROUP BY 姓名
HAVING COUNT(*) >= 3
),
o(分数,姓名,n) AS (
SELECT 分数,姓名,
ROW_NUMBER() OVER (PARTITION BY 姓名 ORDER BY 分数 DESC)
FROM test
)
SELECT s.姓名,
SUM(o.分数) 总分数,
RANK() OVER (ORDER BY SUM(o.分数) DESC) 排名
FROM s
JOIN o
ON s.姓名 = o.姓名
WHERE o.n<=3
GROUP BY s.姓名
ORDER BY 排名姓名 总分数 排名
---- --------------------------------------- --------------------
张三 28.0 1
王五 19.0 2
