select * 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
结果:
1 1 1b
2 1 1b
3 1 1b
1 2 2b
2 2 2b
3 2 2b
需要结果:
1 1 1b
2 NULL NULL
3 NULL NULL
1 NULL NULL
2 2 2b
3 NULL NULL
(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
结果:
1 1 1b
2 1 1b
3 1 1b
1 2 2b
2 2 2b
3 2 2b
需要结果:
1 1 1b
2 NULL NULL
3 NULL NULL
1 NULL NULL
2 2 2b
3 NULL NULL
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
*/
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,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.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
主表,尺寸表.
尺寸
54
58
64
70
76
82
90
98
附表
尺寸: 产品1 产品1 产品1 产品1 合计 补正
58 1 0 0 0 1 0
64 4 10 0 0 14 0
70 12 24 0 0 36 0
76 8 14 0 0 22 0
82 12 16 0 0 28 0
90 3 0 0 0 3 0
98 3 0 0 0 3 0
58 1 0 0 0 1 1
64 4 10 0 0 14 1
70 12 24 0 0 36 1
76 8 14 0 0 22 1
82 12 16 0 0 28 1
90 3 0 0 0 3 1
98 3 0 0 0 3 1
64 0 0 0 0 0 3
82 0 0 0 0 0 3
90 0 0 0 0 0 3
70 0 0 0 0 0 4
76 0 0 0 0 0 5产品不是固定的,按照补正区分,去显示所有尺寸的产品数据.54 null null null null null 0
58 1 0 0 0 1 0
64 4 10 0 0 14 0
70 "
76 "
82 以此例推
90 "
98 "
54 null null null null null 1
58 1 0 0 0 1 1
64 4 10 0 0 14 1
70
76
82
90
98
(select '1' a
union
select '2' a
union
select '3' a ) a
cross join
(select '1' b,'1b' c,'0' flg
union
select '2' b ,'2b' c,'0' flg
union
select '1' b ,'1b' c,'1' flg
)b
希望结果是
1 1 1b 0
2 2 2b 0
3 NULL NULL 0
1 1 1b 1
2 NULL NULL 1
3 NULL NULL 1
能解决么?
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 行受影响)
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 * from
(select '1' a
union
select '2' a
union
select '3' a ) a
cross join
(select '1' b,'1b' c,'0' flg
union
select '2' b ,'2b' c,'0' flg
union
select '1' b ,'1b' c,'1' flg
)b a b c flg
---- ---- ---- -----------
1 1 1b 0
2 2 2b 0
3 NULL NULL 0
1 1 1b 1
2 NULL NULL 1
3 NULL NULL 1
就是按FLG去做条件,取笛卡尔乘积.