SQL常用查询，分页，行列转换
1.假如目前有三个表,员工表(EB) ,字段有员工编号(ECODE)，姓名(ENAME):考核科目分数表(KB) ,字段有员工编号(ECODE)，科目编号（KECODE），分数:科目表(MB) ,字段有科目编号(KECODE)，科目名称(KENAME)。
统计每门科目的员工选择科目人数(超过5人的科目才统计)，要求输出科目编号和选择科目人数，查询结果按人数降序排序，若人数相同，按科目编号升序排序。
答：解题思路 输出科目编号和选择科目人数 按科目编号分组
SELECT KECODE,COUNT(*) FROM KB GROUP BY KECODE HAVING COUNT(*)>5 ORDER BY COUNT(*) DESC ,KECODE ASC
2.使用分【100-85】【85-70】【70-60】 ,[<60]米统计各科分数，分别统计:各分数段人数，科目编号和科目名称。
答： 解题思路：按分数段分组，先把kb每个分数做一个分数段标识，再按分数段分组
SELECT A.KEcode,MB.KENAME,A.GRADE,COUNT(*) FROM ( SELECT CASE WHEN SCORE >= 85 and SCORE<=100 THEN 'A' WHEN SCORE >= 75 and SCORE< 85 THEN 'B' WHEN SCORE >= 60 and SCORE< 75 THEN 'C' WHEN SCORE < 60 THEN 'D' END 'GRADE',* FROM KB ) A LEFT JOIN MB ON A.KECODE =MB.KECODE GROUP BY A.GRADE, A.KEcode,MB.KENAME
3.查询维修完全部课程的同学姓名编号
解题思路： 查询 所修科目数 （小于） 科目总数 的学生信息
SELECT * FROM EB WHERE ( (SELECT COUNT(*) FROM KB WHERE EB.ECode=KB.ECODE) <(SELECT COUNT(*) FROM MB ))
4.行转列（存储过程实现）
declare @sql varchar(500) set @sql='select userid' select @sql=@sql+',max(case Code when '''+code+''' then Price else 0 end) ['+code+']' from(select distinct code from MobilePhone)a--同from tb group by课程，默认按课程名排序 print(@sql) set @sql=@sql+' from MobilePhone group by userid' print(@sql) exec (@sql)
打印出来的结果：
select userid,max(case Code when 'HWP30' then Price else 0 end) [HWP30], max(case Code when 'MZ5' then Price else 0 end) [MZ5], max(case Code when 'XM8' then Price else 0 end) [XM8], max(case Code when 'XM9' then Price else 0 end) [XM9], max(case Code when 'XMK20' then Price else 0 end) [XMK20] from MobilePhone group by userid
执行结果如图：这是没有加上max()取最大值，按userid分组
转载： https://www.cnblogs.com/guwei4037/p/17158658.html
http://www.mobiletrain.org/about/BBS/130368.html
在SQL 2005之后有了新的用法
1. 什么是行转列
行转列是一种将行数据转换为列数据的操作。在数据库中，通常情况下，每一行代表一个记录，每一列代表一个属性。但在某些情况下，我们需要将某些属性的值作为新的列，以便更好地展示和分析数据。行转列操作可以将这些属性的值转换为新的列，使得数据更加直观和易于分析。
2. 行转列的操作方法
在SQL Server中，行转列操作可以通过使用PIVOT关键字来实现。具体操作步骤如下：
1. 选择需要进行行转列操作的表，并确定需要转换的列和转换后的列名。
2. 使用PIVOT关键字，将需要转换的列名作为PIVOT的参数。
3. 在PIVOT关键字后面，使用FOR子句指定需要转换的列。
4. 使用IN子句指定需要转换的列的值。
5. 使用AS子句指定转换后的列名。
6. 使用SELECT语句查询转换后的结果。
3. 行转列的应用场景
行转列操作在实际应用中有很多场景，下面列举几个常见的应用场景：
1. 统计报表：行转列可以将原始数据转换为统计报表所需的格式，使得数据更加直观和易于分析。
2. 数据分析：行转列可以将某些属性的值作为新的列，以便更好地进行数据分析和比较。
3. 数据展示：行转列可以将原始数据转换为适合展示的格式，使得数据更加美观和易于理解。
4. 数据导出：行转列可以将原始数据转换为适合导出的格式，方便数据在不同系统之间的传递和使用。
4. 行转列的示例
下面通过一个具体的示例来演示行转列的操作方法：
假设有一个学生成绩表，包含学生姓名、科目和成绩三个字段。现在需要将每个学生的成绩转换为新的列，以便更好地进行分析。
创建一个名为"Scores"的表，并插入一些示例数据：
CREATE TABLE Scores ( StudentName VARCHAR(50), Subject VARCHAR(50), Score INT ); INSERT INTO Scores VALUES ('张三', '语文', 90); INSERT INTO Scores VALUES ('张三', '数学', 80); INSERT INTO Scores VALUES ('张三', '英语', 70); INSERT INTO Scores VALUES ('李四', '语文', 85); INSERT INTO Scores VALUES ('李四', '数学', 75); INSERT INTO Scores VALUES ('李四', '英语', 65);
一、SQL行转列
SELECT * FROM ( SELECT StudentName, Subject, Score FROM Scores ) AS SourceTable PIVOT ( AVG(Score) FOR Subject IN ([语文], [数学], [英语]) ) AS PivotTable;
结果：
二、SQL列转行，需要使用unpivot
SQL实现：
select StudentName,Subject, Score from ( SELECT * FROM ( SELECT StudentName, Subject, Score FROM Scores ) AS SourceTable PIVOT ( AVG(Score) FOR Subject IN ([语文], [数学], [英语]) ) AS PivotTable ) as ptable unpivot (Score for Subject in([语文],[数学],[英语])) as u order by StudentName desc
结果：
5. 行转列的注意事项
在进行行转列操作时，需要注意以下几点：
1. 转换后的列名需要在PIVOT关键字的IN子句中指定，且需要使用方括号括起来。
2. 转换后的列名不能与原始表中的列名重复，否则会导致语法错误。
3. 转换后的列的数据类型由PIVOT关键字自动推断，通常为数值型或字符型。
4. 如果需要对转换后的列进行聚合操作，可以在PIVOT关键字的参数中指定聚合函数，如SUM、AVG等。
行转列是SQL Server中常用的数据转换操作，可以将行数据转换为列数据，使得数据更加直观和易于分析。通过使用PIVOT关键字，可以实现行转列操作。行转列在统计报表、数据分析、数据展示和数据导出等场景中有广泛的应用。在进行行转列操作时，需要注意转换后的列名、数据类型和聚合函数的使用。通过掌握行转列的操作方法和应用场景，可以更好地进行数据处理和分析。
以上是简单的原理实现，在实际运用中，多用于报表统计,按日期，类别统计,
@monthstr 需要拼接
示例：
declare @b_date varchar(50)='2023-08-26' declare @e_date varchar(50)='2023-08-30' declare @sql varchar(max) declare @monthstr varchar(max)='' select @monthstr=@monthstr+',"'+monthstr+'"' from( select '2023-08' monthstr union select '2023-09' monthstr )a --print(@monthstr) select @monthstr=SUBSTRING(@monthstr,2,len(@monthstr)) print(@monthstr) set @sql='select ''pass_qty'' typestr,* from ( --type 1 select a.monthstr,count(1) pass_qty from (select convert(char(7) ,check_date , 120) monthstr,check_result from A where check_date >'''+@b_date+''' and check_date<'''+@e_date+''' ) a WHERE check_result=1 group by a.monthstr ) as stable PIVOT( SUM(pass_qty) FOR [monthstr] in('+@monthstr+')) as ptable union select ''fail_qty'' typestr,* from ( --type 2 select a.monthstr,count(1) fail_qty from ( select convert(char(7) ,check_date , 120) monthstr,check_result from A where check_date >'''+@b_date+''' and check_date<'''+@e_date+''' ) a WHERE check_result=2 group by a.monthstr ) as stable PIVOT( SUM(fail_qty) FOR monthstr in('+@monthstr+')) as ptable ' print(cast(len(@sql) as varchar)) print(@sql) exec (@sql)
结果：
5.分页
CREATE PROCEDURE paging_procedure @pageIndex int, -- 第几页 @pageSize int -- 每页包含的记录数 as begin select top (@pageSize) * -- 这里注意一下，不能直接把变量放在这里，要用select top () from (select row_number() over(order by id desc) as rownumber,* from [dbo].[MobilePhone]) temp_row where rownumber>(@pageIndex-1)*@pageSize; end
6. 多行数据拼凑成一格 STUFF函数 类似于 replace（），用于替换字符串的内容， For XML Path 将查询结果集以XML形式展现，将多行的结果，展示在同一行 ，两者可结合使用
转载：https://www.cnblogs.com/liuchenxing/p/9253897.html
select WorkflowSchema, ActionName=(STUFF((select ',' + ActionName from [dbo].[Workflow_Action] a where a.WorkflowSchema=b.WorkflowSchema for xml path('')),1,1,'')) --where条件必须加上 from [dbo].[Workflow_Action] b group by WorkflowSchema
7.求多条记录的最大值
SELECT A.[parent_number] part_no ,A.[parent_version] part_ver ,[child_number] component ,[child_version] ,[qty] ,[unit] ,[parent_type] ,[replace_group] ,[child_part_type] ,[potxl] ,[cate] ,[desigator] ,[form] ,[to] ,[doc_no] ,[type] ,[ver] ,[change_no] ,[all_r] ,[component_desc] ,[scrap] ,[item_id] FROM TABLEA A where A.child_number=@child_number AND parent_version=(SELECT MAX(parent_version) FROM TABLEA where child_number=@child_number AND parent_number=A.parent_number)
