求获取特定格式数据的SQL语句

2015-05-07 02:58:15 390 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL格式化神器：如何写出优雅易读的SQL语句编写优雅易读的 SQL 语句，能够显著提升团队协作和代码的可维护性。关键点说明缩进和排版规范化缩进和换行，让查询结构更加清晰关键字大小写使用大写字母书写 SQL 关键字逻辑顺序遵循标准的 SQL 语句顺序，保持一致性别名使用别名简化表名和字段名工具和插件使用 SQL 格式化工具提升效率通过这些技巧和工具，你可以在团队中编写出高质量的 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

...全文

三相SST固态变压器详细设计案例本案例针对400kVA、30kHz三相固态变压器（SST）开展详细设计，核心采用输入串联输出并联（ISOP）拓扑架构（模块化多电平矩阵变换器M3C简化版），重点完成高频变压器的参数计算、绕组设计、损耗分析及液冷温升验证，同时覆盖系统级架构设计、半导体损耗估算及整机效率优化，全流程基于当前电力电子技术可行性展开。阅读详情

小小宝er

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

还在加载中灬 2015-05-07

打赏
举报

引用 20 楼 Tiger_Zhao 的回复:

[Quote=引用 15 楼 ky_min 的回复:]都一样要聚合了，相比聚合后再一次JOIN，我觉得应该少一次JOIN[/Quote] 可是学校D、学校E在查询中是不需要聚合的。开窗函数只适合统计范围和过滤范围一致的时候，否则一不小心就过度统计了。我平时都不大敢用。

仔细想想，确实这样，多谢指教最初是考虑连接的代价也不小，以后找时间造些数据研究研究

Tiger_Zhao 2015-05-07

打赏
举报

[Quote=引用 15 楼 ky_min 的回复:]都一样要聚合了，相比聚合后再一次JOIN，我觉得应该少一次JOIN[/Quote]
可是学校D、学校E在查询中是不需要聚合的。
开窗函数只适合统计范围和过滤范围一致的时候，否则一不小心就过度统计了。
我平时都不大敢用。

小小宝er 2015-05-07

打赏
举报

@还在加载中灬谢谢 @-小蕾- 谢谢感谢所有热心回复问题的朋友，确实感激不尽。由于分数限制。只能聊表心意，谢谢大家的支持，让我走在进步的路上

-小蕾- 2015-05-07

打赏
举报

引用 16 楼 MS_CSDN 的回复:

[quote=引用 7 楼 Tiger_Zhao 的回复:] “填报相同志愿”是指（批次,志愿代码,学校）都相同吧。

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         562

你好，我有个地方没看明白，其实我列出来的指示很少的测试数据。。对于这个地方 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.学校

那些是模拟数据

小小宝er 2015-05-07

打赏
举报

引用 7 楼 Tiger_Zhao 的回复:

“填报相同志愿”是指（批次,志愿代码,学校）都相同吧。

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         562

还在加载中灬 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.用户='小明'

小小宝er 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 的。

还在加载中灬 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         562

小小宝er 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.学校

小小宝er 2015-05-07

打赏
举报

引用 3 楼 ky_min 的回复:

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 用户='小明'

额，大神，不知道能否耽搁你点时间，详细解释解释

还在加载中灬 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 用户='小明'

小小宝er 2015-05-07

打赏
举报

引用 1 楼 lovelj2012 的回复:

一条语句写起来有点小复杂，使用存储过程，创建个临时表来实现，条理看起来比较清晰。

想要的就是存储过程。用临时表肯定是可以的。只是不晓得怎么操作

加载更多回复（1）

求获取特定格式数据的SQL语句

相关推荐