-- 使用 PostgreSQL
-- 簡單化 只用一個Table

create table ithelp190508 (
  sname text not null
, score int not null
);

insert into ithelp190508 values
('學生D', 85),('學生B', 88),
('學生C', 88),('學生A', 90);

-- dense_rank()
-- https://www.postgresql.org/docs/current/functions-window.html
select *
     , dense_rank() over (order by score desc)
  from ithelp190508;

+-------+-------+------------+
| sname | score | dense_rank |
+-------+-------+------------+
| 學生A |    90 |          1 |
| 學生B |    88 |          2 |
| 學生C |    88 |          2 |
| 學生D |    85 |          3 |
+-------+-------+------------+
(4 rows)

-- string_agg()
-- https://www.postgresql.org/docs/current/functions-aggregate.html
with t1 as (
select *
     , dense_rank() over (order by score desc) as dns_rnk
  from ithelp190508
)
select dns_rnk
     , score
     , string_agg(sname, ',')
  from t1
 group by dns_rnk, score
 order by dns_rnk;
 
+---------+-------+-------------+
| dns_rnk | score | string_agg  |
+---------+-------+-------------+
|       1 |    90 | 學生A       |
|       2 |    88 | 學生B,學生C |
|       3 |    85 | 學生D       |
+---------+-------+-------------+
(3 rows)

MSSQL

CREATE TABLE #Student
(
 class nvarchar(20),
 course nvarchar(20),
 studentname nvarchar(20),
 score int
)
insert into #Student select class = '甲班',course = '國文' , studentname = '學生A' , score = 90
insert into #Student select class = '甲班',course = '國文' , studentname = '學生B' , score = 88
insert into #Student select class = '甲班',course = '國文' , studentname = '學生C' , score = 88
insert into #Student select class = '甲班',course = '國文' , studentname = '學生D' , score = 85

SELECT DISTINCT ROW_NUMBER() OVER(order by score desc) as 排名,
  score,
  studentname=
  (
   SELECT cast(studentname AS NVARCHAR ) + ' ' 
   from #Student 
   where score=t0.score --把name一樣的加起來
   FOR XML PATH('')
  )
  from #Student t0
  group by score

[SQL]將多筆資料合併為一筆顯示(FOR XML PATH)
https://dotblogs.com.tw/kevinya/2012/06/01/72553

MSSQL:

select top 3 ROW_NUMBER() OVER(ORDER BY score desc) as Number,
score,
left(studentnameNew,len(studentnameNew)-1) as studentnameComb
from (
SELECT distinct score,(SELECT cast(studentname AS NVARCHAR ) + ',' from Student 
where score = stu.score
FOR XML PATH('')) as studentnameNew
from Student stu
where 1=1
) as MM

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=123b6c62282cf294f08c25dced34fc90

大大你好，最近工作較忙所以晚看到了這題
而問題也早已經被優秀的邦友們解決了
但看著這問題我想了下，想說能否不用特殊函數(FOR XML PATH...等)解決這問題
因此花了點時間撰寫，後來採用迴圈的方式實現，這當然不是最佳實踐方案，但應該是通用性最佳的方案吧....XD
附上完整資料表結構
測試環境為 Microsoft SQL Server 2016 Standard edition

/*
	創建測試環境
    測試環境為 Microsoft SQL Server 2016 Standard edition
*/
--測試用資料庫
USE [TestDB]
GO

--創建測試資料表
--Class
IF  EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[class]'))
DROP TABLE [dbo].[class]
GO
CREATE TABLE [dbo].[class]
(
	[Id] INT  IDENTITY(1,1),--流水號
	[ClasseNo] NVARCHAR(5) NOT NULL,--班級代號
	[ClassName] NVARCHAR(20) NOT NULL,--班級名稱	
)
GO
--寫入測試資料
INSERT INTO [dbo].[class] VALUES (N'C01', N'比奇堡駕訓班')

--Course
IF  EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[course]'))
DROP TABLE [dbo].[course]
GO
CREATE TABLE [dbo].[course]
(
	[Id] INT  IDENTITY(1,1),--流水號
	[CourseNo] NVARCHAR(20) NOT NULL,--課程代號
	[CourseName] NVARCHAR(20) NOT NULL,--課程名稱
	[StudentNo] NVARCHAR(10) NOT NULL--學號
)
GO
--寫入測試資料
INSERT INTO [dbo].[course] VALUES (N'P01', N'泡芙駕訓課', N'A001')
INSERT INTO [dbo].[course] VALUES (N'P01', N'泡芙駕訓課', N'A002')
INSERT INTO [dbo].[course] VALUES (N'P01', N'泡芙駕訓課', N'A003')
INSERT INTO [dbo].[course] VALUES (N'P01', N'泡芙駕訓課', N'A004')

--Studentname
IF  EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[Studentname]'))
DROP TABLE [dbo].[Studentname]
GO
CREATE TABLE [dbo].[Studentname]
(
	[Id] INT IDENTITY(1,1),--流水號
	[StudentNo] NVARCHAR(10) NOT NULL,--學號
	[StudentName] NVARCHAR(20) NOT NULL,--學生姓名
	[ClasseNo] NVARCHAR(5) NOT NULL,--班級代號
)
GO
--寫入測試資料
INSERT INTO [dbo].[Studentname] VALUES (N'A001', N'海綿寶寶', N'C01')
INSERT INTO [dbo].[Studentname] VALUES (N'A002', N'派大星', N'C01')
INSERT INTO [dbo].[Studentname] VALUES (N'A003', N'章魚哥', N'C01')
INSERT INTO [dbo].[Studentname] VALUES (N'A004', N'珊迪', N'C01')

--Score
IF  EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[Score]'))
DROP TABLE [dbo].[Score]
GO
CREATE TABLE [dbo].[Score]
(
	[Id] INT  IDENTITY(1,1),--流水號
	[StudentNo] NVARCHAR(10) NOT NULL,--學號
	[Score] INT NOT NULL,--分數
)
GO
--寫入測試資料
INSERT INTO [dbo].[Score] VALUES (N'A001', 90)--海綿寶寶
INSERT INTO [dbo].[Score] VALUES (N'A002', 88)--派大星
INSERT INTO [dbo].[Score] VALUES (N'A003', 88)--章魚哥
INSERT INTO [dbo].[Score] VALUES (N'A004', 85)--珊迪

/*
	題目:
		Student Table column
		class 班級
		course 課程
		studentname 學生名字
		score 分數
		找出前三位最大分數,並且同樣分數的學生顯示在同一列

	原始資料:
		海綿寶寶  90
		派大星    88
		章魚哥    88
		珊迪      85

	所期望輸出:
		第一 90 海綿寶寶
		第二 88 派大星、章魚哥
		第三 85 珊迪

	目標:不用任何特殊函數完成任務

*/
--資料觀察
SELECT * FROM [dbo].[class]
SELECT * FROM [dbo].[course]
SELECT * FROM [dbo].[Studentname]
SELECT * FROM [dbo].[score]

--開始實作
DECLARE @i int = 1--以1為開始對應流水號第1位的同學
DECLARE @iMax int = (SELECT COUNT(*) FROM [dbo].[Studentname])--取得學生總數
DECLARE @str NVARCHAR(MAX) = N''--準備存取學生姓名
DECLARE @Score INT = 0--分數

--創建答案輸出表
CREATE TABLE #Ans
(
	[Score] INT NOT NULL,--分數
	[Student] NVARCHAR(MAX)--姓名
)

--將最高的三個分數取出來
INSERT INTO #Ans
SELECT DISTINCT TOP 3 [Score] AS [Score] ,--找出最高的三個分數
			N'' AS [Student]--先準備好姓名欄位
FROM [dbo].[Score]--從分數表取得
ORDER BY [Score] DESC--降冪排列

--透過迴圈寫資料
WHILE @i <= @iMax
BEGIN	
		SELECT @str = std.[StudentName],--將變數指定為姓名
				@Score = sc.[Score]--將變數指定為分數
		FROM [dbo].[Studentname] AS std
		LEFT JOIN [dbo].[Score] AS sc
			ON std.StudentNo = sc.StudentNo
		WHERE std.[Id] = @i--指定流水號		

		--如果分數是符合最高的三個分數，就將名子更新進去
		IF @Score IN (SELECT TOP 3 [Score] FROM #Ans ORDER BY [Score] DESC)
		BEGIN
			UPDATE #Ans
			SET [Student] = ([Student] + N', ' + @str)		
			FROM #Ans
			WHERE [Score] = @Score
		END
	SET @i = @i + 1
END
SELECT [Score], 
	SUBSTRING([Student],2 ,LEN([Student])) AS [Student]--去除最前面的逗號
FROM #Ans
DROP TABLE #ANS

答案輸出: