22,210
社区成员
发帖
与我相关
我的任务
分享求获取特定格式数据的SQL语句
请各位帮忙。看如何写编写如下SQL
有用户志愿表，保存的是用户填写的志愿：
（用户志愿表）
ID 批次 志愿代码 学校 用户
1 第一批 A 学校A 小明
2 第一批 B 学校B 小明
3 第二批 A 学校C 小明
4 第一批 A 学校A 小红
5 第一批 A 学校A 小毛
6 第一批 B 学校B 小毛
………………
用户表
ID 用户 分数
1 小明 578
2 小红 595
3 小毛 562
………………
其中用户志愿表的用户字段和用户表的用户字段关联，需要的结果是
获取某用户所有的志愿，且填报相同志愿的总人数，以及最高分和最低分
如小明查看自己填写的所有志愿，则得出如下结果
批次 志愿代码 学校 填报人数 最高分 最低分
第一批 A 学校A 3 595 562
第一批 B 学校B 2 578 562
第二批 A 学校C 1 578 578
………………
请大家帮忙，看看怎么组织SQL语句完成我描述的结果。谢谢
PS：数据库SQL SERVER 2005
有用户志愿表，保存的是用户填写的志愿：
（用户志愿表）
ID 批次 志愿代码 学校 用户
1 第一批 A 学校A 小明
2 第一批 B 学校B 小明
3 第二批 A 学校C 小明
4 第一批 A 学校A 小红
5 第一批 A 学校A 小毛
6 第一批 B 学校B 小毛
………………
用户表
ID 用户 分数
1 小明 578
2 小红 595
3 小毛 562
………………
其中用户志愿表的用户字段和用户表的用户字段关联，需要的结果是
获取某用户所有的志愿，且填报相同志愿的总人数，以及最高分和最低分
如小明查看自己填写的所有志愿，则得出如下结果
批次 志愿代码 学校 填报人数 最高分 最低分
第一批 A 学校A 3 595 562
第一批 B 学校B 2 578 562
第二批 A 学校C 1 578 578
………………
请大家帮忙，看看怎么组织SQL语句完成我描述的结果。谢谢
PS：数据库SQL SERVER 2005
...全文
请发表友善的回复…
发表回复
还在加载中灬 2015-05-07
- 打赏
- 举报
仔细想想，确实这样，多谢指教
最初是考虑 连接的代价也不小，以后找时间造些数据研究研究
Tiger_Zhao 2015-05-07
- 打赏
- 举报
[Quote=引用 15 楼 ky_min 的回复:]都一样要聚合了，相比聚合后再一次JOIN，我觉得应该少一次JOIN[/Quote]
可是学校D、学校E在查询中是不需要聚合的。
开窗函数只适合统计范围和过滤范围一致的时候，否则一不小心就过度统计了。
我平时都不大敢用。
可是学校D、学校E在查询中是不需要聚合的。
开窗函数只适合统计范围和过滤范围一致的时候，否则一不小心就过度统计了。
我平时都不大敢用。
Microsoft-笨笨 2015-05-07
- 打赏
- 举报
@还在加载中灬 谢谢
@-小蕾- 谢谢
感谢所有热心回复问题的朋友，确实感激不尽。由于分数限制。只能聊表心意，谢谢大家的支持，让我走在进步的路上
-小蕾- 2015-05-07
- 打赏
- 举报
你好，我有个地方没看明白，其实我列出来的指示很少的测试数据。。对于这个地方
SELECT '1','第一批','A','学校A','小明' UNION ALL
SELECT '2','第一批','B','学校B','小明' UNION ALL
SELECT '3','第二批','A','学校C','小明' UNION ALL
SELECT '4','第一批','A','学校A','小红' UNION ALL
SELECT '5','第一批','A','学校A','小毛' UNION ALL
SELECT '6','第一批','B','学校B','小毛'
是怎么处理？，不会有多少写多少吧[/quote]
你就直接把查询语句改成你对应的数据库表和字段。
WITH 用户志愿表(ID,批次,志愿代码,学校,用户) AS (
SELECT '1','第一批','A','学校A','小明' UNION ALL
SELECT '2','第一批','B','学校B','小明' UNION ALL
SELECT '3','第二批','A','学校C','小明' UNION ALL
SELECT '4','第一批','A','学校A','小红' UNION ALL
SELECT '5','第一批','A','学校A','小毛' UNION ALL
SELECT '6','第一批','B','学校B','小毛'
)
,用户表(ID,用户,分数) AS (
SELECT '1','小明',578 UNION ALL
SELECT '2','小红',595 UNION ALL
SELECT '3','小毛',562
)
这些可以不要了
还在加载中灬 2015-05-07
- 打赏
- 举报
只有这一段是要给你的
SELECT A.批次, A.志愿代码, A.学校,
COUNT(*) 填报人数,
MAX(C.分数) 最高分,
MIN(C.分数) 最低分
FROM 用户志愿表 A
JOIN 用户志愿表 B
ON A.批次 = B.批次
AND A.志愿代码 = B.志愿代码
AND A.学校 = B.学校
JOIN 用户表 C
ON B.用户 = C.用户
WHERE A.用户='小明'
GROUP BY A.批次, A.志愿代码, A.学校
ORDER BY A.批次, A.志愿代码, A.学校Microsoft-笨笨 2015-05-07
- 打赏
- 举报
你好，我有个地方没看明白，其实我列出来的指示很少的测试数据。。对于这个地方
SELECT '1','第一批','A','学校A','小明' UNION ALL
SELECT '2','第一批','B','学校B','小明' UNION ALL
SELECT '3','第二批','A','学校C','小明' UNION ALL
SELECT '4','第一批','A','学校A','小红' UNION ALL
SELECT '5','第一批','A','学校A','小毛' UNION ALL
SELECT '6','第一批','B','学校B','小毛'
是怎么处理？，不会有多少写多少吧
还在加载中灬 2015-05-07
- 打赏
- 举报
都一样要聚合了，相比聚合后再一次JOIN，我觉得应该少一次JOIN
-小蕾- 2015-05-07
- 打赏
- 举报
with table1 as
(
select 1 ID, '第一批' 批次, 'A' 志愿代码, '学校A' 学校, '小明' 用户 union all
select 2 ID, '第一批' 批次, 'B' 志愿代码, '学校B' 学校, '小明' 用户 union all
select 3 ID, '第二批' 批次, 'A' 志愿代码, '学校C' 学校, '小明' 用户 union all
select 4 ID, '第一批' 批次, 'A' 志愿代码, '学校A' 学校, '小红' 用户 union all
select 5 ID, '第一批' 批次, 'A' 志愿代码, '学校A' 学校, '小毛' 用户 union all
select 6 ID, '第一批' 批次, 'B' 志愿代码, '学校B' 学校, '小毛' 用户
)
,
table2 as
(
select 1 ID, '小明' 用户, 578 分数 union all
select 2 ID, '小红' 用户, 595 分数 union all
select 3 ID, '小毛' 用户, 562 分数
)
select n.* from table1 as m,
(select a.批次, a.志愿代码, a.学校, COUNT(*) 人数, MAX(分数) 最高分, MIN(分数) 最低分
from table1 a,table2 b where a.用户=b.用户 group by a.批次, a.志愿代码, a.学校) as n
where m.志愿代码=n.志愿代码 and m.学校=n.学校 and m.批次=n.批次 and m.用户='小明'
Tiger_Zhao 2015-05-07
- 打赏
- 举报
不过从你的应用来看，你应该把全体的统计结果作为永久表保存下来，效率更高。
-小蕾- 2015-05-07
- 打赏
- 举报
with table1 as
(
select 1 ID, '第一批' 批次, 'A' 志愿代码, '学校A' 学校, '小明' 用户 union all
select 2 ID, '第一批' 批次, 'B' 志愿代码, '学校B' 学校, '小明' 用户 union all
select 3 ID, '第二批' 批次, 'A' 志愿代码, '学校C' 学校, '小明' 用户 union all
select 4 ID, '第一批' 批次, 'A' 志愿代码, '学校A' 学校, '小红' 用户 union all
select 5 ID, '第一批' 批次, 'A' 志愿代码, '学校A' 学校, '小毛' 用户 union all
select 6 ID, '第一批' 批次, 'B' 志愿代码, '学校B' 学校, '小毛' 用户
)
,
table2 as
(
select 1 ID, '小明' 用户, 578 分数 union all
select 2 ID, '小红' 用户, 595 分数 union all
select 3 ID, '小毛' 用户, 562 分数
)
select n.* from table1 as m,
(select a.批次, a.志愿代码, a.学校, COUNT(*) 人数, MAX(分数) 最高分, MIN(分数) 最低分
from table1 a,table2 b where a.用户=b.用户 group by a.批次, a.志愿代码, a.学校) as n
where m.志愿代码=n.志愿代码 and m.学校=n.学校 and m.用户='小明'
Microsoft-笨笨 2015-05-07
- 打赏
- 举报
@ Tiger_Zhao，谢谢
江南小鱼 2015-05-07
- 打赏
- 举报
撸主特意注明sql server2005，哥还当2005不支持PARTITION BY呢
Tiger_Zhao 2015-05-07
- 打赏
- 举报
#3: 就算 PARTITION BY 里加上 [i]批次,志愿代码,[i]，还是全不统计完后再取小明，数据量大的时候太浪费了。
#5: 根本没有用小明过滤啊。
还是需要两次 JOIN 的。
#5: 根本没有用小明过滤啊。
还是需要两次 JOIN 的。
还在加载中灬 2015-05-07
- 打赏
- 举报
主要是主间的三个开窗
,COUNT(1)OVER(PARTITION BY 学校)[填报人数]
,MAX(T2.分数)OVER(PARTITION BY 学校)[最高分]
,MIN(T2.分数)OVER(PARTITION BY 学校)[最低分]
PARTITION BY 学校 对学校分组进行统计 人数及最高分低分，是SQL2005的新特性，一次扫描就可以把需要的数据处理好
WHERE 用户='小明'
然后，再过滤出属于小明的数据
Tiger_Zhao 2015-05-07
- 打赏
- 举报
“填报相同志愿”是指（批次,志愿代码,学校）都相同吧。
WITH 用户志愿表(ID,批次,志愿代码,学校,用户) AS (
SELECT '1','第一批','A','学校A','小明' UNION ALL
SELECT '2','第一批','B','学校B','小明' UNION ALL
SELECT '3','第二批','A','学校C','小明' UNION ALL
SELECT '4','第一批','A','学校A','小红' UNION ALL
SELECT '5','第一批','A','学校A','小毛' UNION ALL
SELECT '6','第一批','B','学校B','小毛'
)
,用户表(ID,用户,分数) AS (
SELECT '1','小明',578 UNION ALL
SELECT '2','小红',595 UNION ALL
SELECT '3','小毛',562
)
SELECT A.批次, A.志愿代码, A.学校,
COUNT(*) 填报人数,
MAX(C.分数) 最高分,
MIN(C.分数) 最低分
FROM 用户志愿表 A
JOIN 用户志愿表 B
ON A.批次 = B.批次
AND A.志愿代码 = B.志愿代码
AND A.学校 = B.学校
JOIN 用户表 C
ON B.用户 = C.用户
WHERE A.用户='小明'
GROUP BY A.批次, A.志愿代码, A.学校
ORDER BY A.批次, A.志愿代码, A.学校
批次 志愿代码 学校 填报人数 最高分 最低分
------ -------- ----- ----------- ----------- -----------
第二批 A 学校C 1 578 578
第一批 A 学校A 3 595 562
第一批 B 学校B 2 578 562Microsoft-笨笨 2015-05-07
- 打赏
- 举报
@zbdzjx 谢谢
zbdzjx 2015-05-07
- 打赏
- 举报
with table1 as
(
select 1 ID, '第一批' 批次, 'A' 志愿代码, '学校A' 学校, '小明' 用户 union all
select 2 ID, '第一批' 批次, 'B' 志愿代码, '学校B' 学校, '小明' 用户 union all
select 3 ID, '第二批' 批次, 'A' 志愿代码, '学校C' 学校, '小明' 用户 union all
select 4 ID, '第一批' 批次, 'A' 志愿代码, '学校A' 学校, '小红' 用户 union all
select 5 ID, '第一批' 批次, 'A' 志愿代码, '学校A' 学校, '小毛' 用户 union all
select 6 ID, '第一批' 批次, 'B' 志愿代码, '学校B' 学校, '小毛' 用户
)
,
table2 as
(
select 1 ID, '小明' 用户, 578 分数 union all
select 2 ID, '小红' 用户, 595 分数 union all
select 3 ID, '小毛' 用户, 562 分数
)
--select * from table1 a left join table2 b on a.用户=b.用户
select a.批次, a.志愿代码, a.学校, COUNT(*) 人数, MAX(分数) 最高分, MIN(分数) 最低分 from table1 a left join table2 b on a.用户=b.用户 group by a.批次, a.志愿代码, a.学校Microsoft-笨笨 2015-05-07
- 打赏
- 举报
额，大神，不知道能否耽搁你点时间，详细解释解释
还在加载中灬 2015-05-07
- 打赏
- 举报
SELECT 批次,志愿代码,学校,填报人数,最高分,最低分
FROM (
SELECT T1.*
,COUNT(1)OVER(PARTITION BY 学校)[填报人数]
,MAX(T2.分数)OVER(PARTITION BY 学校)[最高分]
,MIN(T2.分数)OVER(PARTITION BY 学校)[最低分]
FROM 用户志愿表 T1
LEFT JOIN 用户表 T2 ON T1.用户=T2.用户
)T
WHERE 用户='小明'
Microsoft-笨笨 2015-05-07
- 打赏
- 举报
想要的就是存储过程。用临时表肯定是可以的。只是不晓得怎么操作
加载更多回复（1）
