34,576
社区成员
发帖
与我相关
我的任务
分享
with ca(a) as --测试表创建
(
select 1 as a union all
select 2
),cb(b,c,flg) as --测试表创建
(
select '1' as b,'1b' as c,0 as flg union all
select '2','2b',0
)
select a.a,b.b,b.c,b.flg
from
ca a
cross join
(select distinct b from cb)c
left join
cb b
on c.b = b.b and a.a = c.b
/*
a b c flg
----------- ---- ---- -----------
1 1 1b 0
2 NULL NULL NULL
1 NULL NULL NULL
2 2 2b 0
(4 行受影响)
select a.a,b.b,b.c,b.flg
from
(select '1' a union select '2' a union select '3' a) a
cross join
(select distinct b from (select '1' b,'1b' c,0 flg union select '2' b ,'2b' c,0)e)c
left join
(select '1' b,'1b' c,0 flg union select '2' b ,'2b' c,0)b
on c.b = b.b and a.a = c.b
/*
a b c flg
---- ---- ---- -----------
1 1 1b 0
2 NULL NULL NULL
3 NULL NULL NULL
1 NULL NULL NULL
2 2 2b 0
3 NULL NULL NULL
(6 行受影响)
select a.a,b.b,b.c
from
(select '1' a union select '2' a union select '3' a) a
cross join
(select distinct b from (select '1' b,'1b' c union select '2' b ,'2b' c)e)c
left join
(select '1' b,'1b' c union select '2' b ,'2b' c)b
on c.b = b.b and a.a = c.b
/*
a b c
---- ---- ----
1 1 1b
2 NULL NULL
3 NULL NULL
1 NULL NULL
2 2 2b
3 NULL NULL
select a.a,b.b,b.c
from
(select '1' a union select '2' a union select '3' a) a
cross join
(select '1' d union select '2' d)c
left join
(select '1' b,'1b' c union select '2' b ,'2b' c)b
on c.d = b.b and a.a = c.d
/*
a b c
---- ---- ----
1 1 1b
2 NULL NULL
3 NULL NULL
1 NULL NULL
2 2 2b
3 NULL NULL
select a.*,b.* from
(select '1' a
union
select '2' a
union
select '3' a ) a
full join
(select '1' b,'1b' c
union
select '2' b ,'2b' c)b
select a.a,case when a.a=b.b then b.b else null end as b,
case when a.a=b.b then b.c else null end as c
from
(select '1' a union select '2' a union select '3' a) a
cross join
(select '1' b,'1b' c union select '2' b ,'2b' c)b
/*
a b c
---- ---- ----
1 1 1b
2 NULL NULL
3 NULL NULL
1 NULL NULL
2 2 2b
3 NULL NULL
*/