34,838
社区成员




;WITH tab(列1,列2,列3,列4)AS(
select 'A',NULL,NULL,'A1' union all
select 'B','B1','B2','B3' union all
select 'B','B4','B5','B6' union all
select 'C','C1','C2','C3' union all
select 'C','C4','C5','C6'
)
SELECT a.列1,
ISNULL(t.列2,a.列2) 列2,
isnull( t.列3,a.列3) 列3,
a.列4
FROM tab a
LEFT JOIN tab t
ON a.列1= CASE WHEN a.列1='A' THEN CASE WHEN t.列1='A' THEN '' ELSE 'A'END ELSE t.列1 END
AND t.列2=CASE WHEN a.列1='A' THEN t.列2 ELSE NULL END
列1 列2 列3 列4
---- ---- ---- ----
A B1 B2 A1
A B4 B5 A1
A C1 C2 A1
A C4 C5 A1
B B1 B2 B3
B B4 B5 B6
C C1 C2 C3
C C4 C5 C6
create table #T(姓名 varchar(10))
insert into #T values('张三')
insert into #T values('李四')
insert into #T values(NULL )
create table #T2(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into #T2 values('张三' , '语文' , 74)
insert into #T2 values('张三' , '数学' , 83)
insert into #T2 values('张三' , '物理' , 93)
insert into #T2 values(NULL , '数学' , 50)
--drop table #t,#T2
go
select
*
from
#T a
cross apply
(select 课程,分数 from #t2 where 姓名=a.姓名) b
/*
姓名 课程 分数
---------- ---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
top n 那个希望赐教。
WITH T1(列1,列2,列3,列4)AS(
select 'A',NULL,NULL,'A1' union all
select 'B','B1','B2','B3' union all
select 'B','B4','B5','B6' union all
select 'C','C1','C2','C3' union all
select 'C','C4','C5','C6'
)
Select a.列1,b.列2,b.列3,a.列4 from T1 a ,T1 b where a.列1='A' and a.列1<>b.列1
SELECT a.列1,
t.列2,
t.列3,
a.列4
FROM tab a
LEFT JOIN tab t
ON a.列1 <> t.列1
WHERE a.列1 = 'A'
--测试数据
;WITH tab(列1,列2,列3,列4)AS(
select 'A',NULL,NULL,'A1' union all
select 'B','B1','B2','B3' union all
select 'B','B4','B5','B6' union all
select 'C','C1','C2','C3' union all
select 'C','C4','C5','C6'
)
--测试数据结束
SELECT a.列1 ,
t.列2 ,
t.列3 ,
a.列4
FROM tab a
CROSS APPLY ( SELECT *
FROM tab b
WHERE a.列1 <> b.列1
) t
WHERE a.列1 = 'A'