mysql - 选择少数列的不同值和少数列的多个值

Question

我必须沿着同一表的几列的多个值选择几列的不同值。我的 table 是这样的

当像这样显示此表时，我想选择first_name和last_name列的不同值以及主题列的所有值

是否可以显示名字和姓氏的不同值？

Select DISTINCT `first_name` , `last_name` from table1

Answer 1

我尝试过使用分区子句来处理给定的场景。请检查下面的示例代码片段。它有效。

CREATE TABLE #Student                                                                  
(                                                                             
     first_name VARCHAR(100),                                                  
     last_name VARCHAR(100),                                                   
     [subject] VARCHAR(100)                                                            
)

INSERT INTO #Student (first_name, last_name, [subject]) VALUES ('a','b','maths')  
INSERT INTO #Student (first_name, last_name, [subject]) VALUES ('a','b','science')
INSERT INTO #Student (first_name, last_name, [subject]) VALUES ('e','f','bio')
INSERT INTO #Student (first_name, last_name, [subject]) VALUES ('e','f','eng')


SELECT CASE WHEN reportTbl.RowNo = 1 THEN first_name ELSE '' END as first_name,   
CASE WHEN reportTbl.RowNo = 1 THEN last_name ELSE '' END as last_name, 
[Subject]  FROM 
(
     SELECT first_name, last_name, [Subject],
     ROW_NUMBER() OVER (PARTITION BY first_name, last_name  ORDER BY subject) RowNo 
     FROM #Student
) 
reportTbl

Actual Table after added RowNo coloumn with partition by clause

Below is the final output of the query