--这样? select a.id , tb.b , tc.c from a, ( select id , b1 b from b union all select id , b2 b from b ) tb, ( select id , b1 b from b union all select id , b2 b from b ) tc where a.id = tb.id and a.id = tc.id
--四楼错了点,这样.select a.id , tb.b , tc.c from a, ( select id , b1 b from b union all select id , b2 b from b ) tb, ( select id , c1 c from c union all select id , c2 c from c ) tc where a.id = tb.id and a.id = tc.id
表名 A B C字段 id id b1 b2 id c1 c2 值 1 1 'a' 'b' 1 'A' 'B'需要排列组合结果为1 'a' 'A' 1 'a' 'B' 1 'b' 'A' 1 'b' 'B'
create table A(id int) create table B(id int , B1 varchar(10) , B2 varchar(10)) create table C(id int , C1 varchar(10) , C2 varchar(10)) insert into A values(1) insert into B values(1 , 'a' , 'b') insert into C values(1 , 'A' , 'B') goselect a.id , tb.b , tc.c from a, ( select id , b1 b from b union all select id , b2 b from b ) tb, ( select id , c1 c from c union all select id , c2 c from c ) tc where a.id = tb.id and a.id = tc.id order by a.id , b , cdrop table a , b , c/* id b c ----------- ---------- ---------- 1 a A 1 a B 1 b A 1 b B(所影响的行数为 4 行)*/
select a.id , tb.b , tc.c from a,
(
select id , b1 b from b
union all
select id , b2 b from b
) tb,
(
select id , b1 b from b
union all
select id , b2 b from b
) tc
where a.id = tb.id and a.id = tc.id
(
select id , b1 b from b
union all
select id , b2 b from b
) tb,
(
select id , c1 c from c
union all
select id , c2 c from c
) tc
where a.id = tb.id and a.id = tc.id
值 1 1 'a' 'b' 1 'A' 'B'需要排列组合结果为1 'a' 'A'
1 'a' 'B'
1 'b' 'A'
1 'b' 'B'
create table A(id int)
create table B(id int , B1 varchar(10) , B2 varchar(10))
create table C(id int , C1 varchar(10) , C2 varchar(10))
insert into A values(1)
insert into B values(1 , 'a' , 'b')
insert into C values(1 , 'A' , 'B')
goselect a.id , tb.b , tc.c from a,
(
select id , b1 b from b
union all
select id , b2 b from b
) tb,
(
select id , c1 c from c
union all
select id , c2 c from c
) tc
where a.id = tb.id and a.id = tc.id
order by a.id , b , cdrop table a , b , c/*
id b c
----------- ---------- ----------
1 a A
1 a B
1 b A
1 b B(所影响的行数为 4 行)*/