1粥帚、實(shí)現(xiàn)效果
使用去重或分組港准，數(shù)據(jù)庫默認(rèn)按字符排序，特定場景下需自定義排序
原本思路如绸，先去重完域，再聯(lián)表保證自定義順序
改進(jìn)思路软吐，將 distinct 替換為 group by，再使用 order by 排序中使用 Min關(guān)鍵詞
2吟税、原始數(shù)據(jù)
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'數(shù)學(xué)', 95.5)
GO
INSERT [dbo].[T_Bas_Achievement] ([id], [name], [subject], [achievement]) VALUES (2, N'李四', N'數(shù)學(xué)', 80)
GO
INSERT [dbo].[T_Bas_Achievement] ([id], [name], [subject], [achievement]) VALUES (3, N'王五', N'數(shù)學(xué)', 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凹耙、模擬實(shí)現(xiàn)
-- 查看初始數(shù)據(jù)
select * from T_Bas_Achievement
-- 場景一姿现，分組添加排序
select name,subject from T_Bas_Achievement
group by name,subject
-- 直接分組添加排序，異常
/*
-- 直接添加 id 排序肖抱，觸發(fā)異常
select name,subject from T_Bas_Achievement
group by name,subject
order by id
-- ORDER BY 子句中的列 "T_Bas_Achievement.id" 無效备典，因?yàn)樵摿袥]有包含在聚合函數(shù)或 GROUP BY 子句中。
*/
-- 使用 order by 排序需添加關(guān)鍵字
select name,subject from T_Bas_Achievement
group by name,subject
order by min(id)
-- 場景二意述，行轉(zhuǎn)列添加排序
-- 行轉(zhuǎn)列
select b.name,max(b.數(shù)學(xué)) as 數(shù)學(xué),max(b.語文) as 語文 from (select * from T_Bas_Achievement) a pivot (max(achievement) for subject in (數(shù)學(xué),語文)) b
group by b.name
-- 行轉(zhuǎn)列添加排序
select b.name,max(b.數(shù)學(xué)) as 數(shù)學(xué),max(b.語文) as 語文 from (select * from T_Bas_Achievement) a pivot (max(achievement) for subject in (數(shù)學(xué),語文)) b
group by b.name
order by min(id)
-- 擴(kuò)展提佣，根據(jù)其他數(shù)據(jù)排序
select ROW_NUMBER() OVER (ORDER BY achievement desc) AS id,name,subject,achievement from T_Bas_Achievement
select b.name,max(b.數(shù)學(xué)) as 數(shù)學(xué),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 (數(shù)學(xué),語文)) b
group by b.name
order by min(id)
