比较sqlserver中的两列并恢复其余数据

Question

我有两张桌子。第一个是学生表，他可以在其中选择两门选修课，其他表是本学期的选修课列表。

每当学生选择一门课程时，都会插入包含基本详细信息的行，如学号、插入时间、所选课程和状态为"1“。无论何时取消选择所选课程，该行的状态都设置为"0“。

假设学生选择了id为1和2的课程。

现在使用这个查询

select SselectedCourse AS [text()] FROM  Sample.dbo.Tbl_student_details where var_rollnumber = '020803009' and status = 1 order by var_courseselectedtime desc FOR XML PATH('')

这将给我的结果是"12“，其中1是物理，2是社会。

第二个表保存从1到9的值，例如课程id

1 = physics
2 = social
3 = chemistry
4 = geography
5 = computer
6 = Spoken Hindi
7 = Spoken English
8 = B.EEE
9 = B.ECE

现在，当前的学生已经选择了1和2，所以在第一列，我得到了"12“，第二列，我需要得到”3456789“(剩余的课程)。如何为此编写查询？

Answer 1

这不是在单个查询中实现的，但很简单。

DECLARE @STUDENT AS TABLE(ID INT, COURSEID INT)
DECLARE @SEM AS TABLE (COURSEID INT, COURSE VARCHAR(100))

INSERT INTO @STUDENT VALUES(1, 1)

INSERT INTO @STUDENT VALUES(1, 2)

INSERT INTO @SEM VALUES(1, 'physics')

INSERT INTO @SEM VALUES(2, 'social')

INSERT INTO @SEM VALUES(3, 'chemistry')

INSERT INTO @SEM VALUES(4, 'geography')

INSERT INTO @SEM VALUES(5, 'computer')

INSERT INTO @SEM VALUES(6, 'Spoken Hindi')

INSERT INTO @SEM VALUES(7, 'Spoken English')

INSERT INTO @SEM VALUES(8, 'B.EEE')

INSERT INTO @SEM VALUES(9, 'B.ECE')

DECLARE @COURSEIDS_STUDENT VARCHAR(100), @COURSEIDS_SEM  VARCHAR(100)

SELECT @COURSEIDS_STUDENT = COALESCE(@COURSEIDS_STUDENT, '') + CONVERT(VARCHAR(10), COURSEID) + ' ' FROM @STUDENT

SELECT @COURSEIDS_SEM = COALESCE(@COURSEIDS_SEM , '') + CONVERT(VARCHAR(10), COURSEID) + ' ' FROM @SEM WHERE COURSEID NOT IN (SELECT COURSEID FROM @STUDENT)


SELECT @COURSEIDS_STUDENT COURSEIDS_STUDENT, @COURSEIDS_SEM COURSEIDS_SEM

Answer 2

试试这个：

;WITH CTE as (select ROW_NUMBER() over (order by (select 0)) as rn,* from Sample.dbo.Tbl_student_details)

,CTE1 As(
select rn,SselectedCourse ,replace(stuff((select ''+courseid  from course_details for xml path('')),1,1,''),SselectedCourse,'') as rem from CTE a 
where rn = 1
union all
select c2.rn,c2.SselectedCourse,replace(rem,c2.SselectedCourse,'') as rem 
from CTE1 c1 inner join CTE c2
on c2.rn=c1.rn+1

)

select STUFF((select ''+SselectedCourse from CTE1 for xml path('')),1,0,''),(select top 1 rem from CTE1 order by rn desc)