显示正在参加一门以上科目的学生

Z时代
2024-01-10
分类：问答

比方说，我有这样一张桌子。显示正在参加一门以上科目的学生

StudentID | SubCode | SubName ----------------------------- 1 M1 Math 1 S1 Science 1 E1 English 2 H1 History 2 G2 Geography 4 M1 Math

我想要一个查询，会显示这样的数据..

StudentID | SubCode | SubName ----------------------------- 1 M1 Math S1 Science E1 English 2 H1 History G2 Geography 4 M1 Math

编辑：

这里是我创造请看看你是否了解一些查询。非常感谢您回应

SELECT 
EI.[EmpCode], EI.[FirstName]+' '+EI.[LastName] [EmployeeName], EI.[FatherName], Dpt.[FullName] [Department], 
[Desig].[FullName] [Designation], EI.DOB [DateOfBirth], EI.DOJ [JoiningDate], EI.NCNIC [CNIC], EI.Mobile, EI.OfficialEmail, 
EI.Email, EI.Address, [Status].FullName [Status], [Company].FullName [Company], EI.EmpImage [Image], 
[Academics].MajorSubjects, [Academics].DegreeInstituteName, [Academics].PassingYear, [Academics].CGPA, [Degree].[FullName] [DegreeName], 
    CONVERT(varchar(3),DATEDIFF(MONTH, DOJ, GETDATE())/12) +' Year(s) '+ 
    CONVERT(varchar(2),DATEDIFF(MONTH, DOJ, GETDATE()) % 12)+ ' Month(s)' 
    AS [EXPERIENCE] 
FROM [HRM].[tbl_EmployeeInfo] EI, [HRM].[tbl_Designation] [Desig], HRM.tbl_Department [Dpt], 
[HRM].[tbl_EmployeeStatus] [Status], [AppCNF].[tbl_OrganizationCompany] [Company], [HRM].[tbl_Degree] [Degree], 
[HRM].[tbl_EmployeeAcademic] [Academics] 
WHERE [Desig].[ID] = EI.DesignationCode 
AND 
Dpt.ID = EI.[DeptCode] 
AND 
[Status].ID = EI.EmpStatusCode 
AND 
[Company].ID = EI.EmpCompanyCode 
AND 
[EI].ID = [Academics].EmpCode 
AND 
[Degree].ID = [Academics].[DegreeCode] 
ORDER BY 1,4

回答：

试试这个：

DECLARE @Student TABLE(StudentId CHAR(1), SubCode VARCHAR(2),SubName VARCHAR(10)) 
INSERT INTO @Student VALUES('1','M1','Math') 
INSERT INTO @Student VALUES('1','S1','Science') 
INSERT INTO @Student VALUES('1','E1','English') 
INSERT INTO @Student VALUES('2','H1','History') 
INSERT INTO @Student VALUES('2','G2','Geography') 
INSERT INTO @Student VALUES('4','M1','Math') 
SELECT CASE WHEN D.RN=1 THEN D.StudentId ELSE '' END StudentId 
    ,D.SubCode,D.SubName 
FROM(
    SELECT * 
     ,ROW_NUMBER() OVER(PARTITION BY StudentId ORDER BY StudentId)RN 
    FROM @Student 
    )D 
ORDER BY 
    D.StudentID, D.RN