22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([学号] nvarchar(22),[姓名] nvarchar(22))
Insert #T1
select N'01',N'张三' union all
select N'02',N'李四' union all
select N'03',N'王五' union all
select N'04',N'张三' union all
select N'05',N'王五'
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([学号] nvarchar(22),[科目] nvarchar(22))
Insert #T2
select N'01',N'语文' union all
select N'01',N'数学' union all
select N'02',N'英语' union all
select N'03',N'物理' union all
select N'03',N'数学' union all
select N'02',N'化学' union all
select N'02',N'语文' union all
select N'02',N'历史' union all
select N'01',N'地理' union all
select N'03',N'语文' union all
select N'05',N'数学' union all
select N'05',N'历史' union all
select N'04',N'物理'
Go
--测试数据结束
SELECT CASE
WHEN rn = 1 THEN
学号
ELSE
NULL
END AS 学号,
CASE
WHEN rn = 1 THEN
姓名
ELSE
NULL
END AS 姓名,
科目
FROM
(
SELECT #T1.*,
科目,
ROW_NUMBER() OVER (PARTITION BY #T1.学号 ORDER BY #T1.学号) rn
FROM #T2
JOIN #T1
ON #T1.学号 = #T2.学号
) t
with tb as (select b.id, a.name, b.subject from tb1 a, tb2 b where a.id = b.id)
select id = case when t1.subject = t2.subject then t1.id else null end
name = case when t1.subject = t2.subject then t1.name else null end
t1.subject
from tb t1, tb t2 where t1.id = t2.id