Sql 分组自定义排序

1、实现效果

使用去重或分组，数据库默认按字符排序，特定场景下需自定义排序
原本思路，先去重，再联表保证自定义顺序
改进思路，将 distinct 替换为 group by，再使用 order by 排序中使用 Min关键词

2、原始数据

CREATE TABLE [dbo].[T_Bas_Achievement](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [subject] [nvarchar](50) NULL,
    [achievement] [float] NULL,
 CONSTRAINT [PK_T_Bas_Achievement] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[T_Bas_Achievement] ON 

GO
INSERT [dbo].[T_Bas_Achievement] ([id], [name], [subject], [achievement]) VALUES (1, N'张三', N'数学', 95.5)
GO
INSERT [dbo].[T_Bas_Achievement] ([id], [name], [subject], [achievement]) VALUES (2, N'李四', N'数学', 80)
GO
INSERT [dbo].[T_Bas_Achievement] ([id], [name], [subject], [achievement]) VALUES (3, N'王五', N'数学', 99)
GO
INSERT [dbo].[T_Bas_Achievement] ([id], [name], [subject], [achievement]) VALUES (4, N'张三', N'语文', 77)
GO
INSERT [dbo].[T_Bas_Achievement] ([id], [name], [subject], [achievement]) VALUES (5, N'李四', N'语文', 88)
GO
INSERT [dbo].[T_Bas_Achievement] ([id], [name], [subject], [achievement]) VALUES (6, N'王五', N'语文', 95)
GO
SET IDENTITY_INSERT [dbo].[T_Bas_Achievement] OFF
GO

3、模拟实现

-- 查看初始数据
select * from T_Bas_Achievement

-- 场景一，分组添加排序
select name,subject from T_Bas_Achievement
group by name,subject

-- 直接分组添加排序，异常
/*
-- 直接添加 id 排序，触发异常
select name,subject from T_Bas_Achievement
group by name,subject
order by id
-- ORDER BY 子句中的列 "T_Bas_Achievement.id" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
*/

-- 使用 order by 排序需添加关键字
select name,subject from T_Bas_Achievement
group by name,subject
order by min(id)

-- 场景二，行转列添加排序
-- 行转列
select b.name,max(b.数学) as 数学,max(b.语文) as 语文 from (select * from T_Bas_Achievement) a pivot (max(achievement) for subject in (数学,语文)) b
group by b.name

-- 行转列添加排序
select b.name,max(b.数学) as 数学,max(b.语文) as 语文 from (select * from T_Bas_Achievement) a pivot (max(achievement) for subject in (数学,语文)) b
group by b.name
order by min(id)

-- 扩展，根据其他数据排序
select ROW_NUMBER() OVER (ORDER BY achievement desc) AS id,name,subject,achievement from T_Bas_Achievement

select b.name,max(b.数学) as 数学,max(b.语文) as 语文 from (select ROW_NUMBER() OVER (ORDER BY achievement desc) AS id,name,subject,achievement from T_Bas_Achievement) a pivot (max(achievement) for subject in (数学,语文)) b
group by b.name
order by min(id)