一个table表,假设有4列A B C Da1 b1 c1 d1
a2 b2 c2 d2
a3 c3 d4
c4 d4
d5
要求,把所有列的组合排列出来,并别可以按照指定的列排序生成结果
a1b1c1d1
a1b1c1d2
a1b1c1d3
.....
a1b1c2d1
a1b1c2d2
a1b1c2d3
...
a1b2c1d1
a1b2c1d1
....
a2b1c1d1
....
按照指定的列排序,比如按照C列排序
则顺序为
a..b..c1..d..
a..b..c1..d..
a..b..c1..d..
a..b..c2..d..
a..b..c2..d..
a..b..c2..d..
a..b..c3..d..
a..b..c3..d..
a..b..c3..d..
....
a2 b2 c2 d2
a3 c3 d4
c4 d4
d5
要求,把所有列的组合排列出来,并别可以按照指定的列排序生成结果
a1b1c1d1
a1b1c1d2
a1b1c1d3
.....
a1b1c2d1
a1b1c2d2
a1b1c2d3
...
a1b2c1d1
a1b2c1d1
....
a2b1c1d1
....
按照指定的列排序,比如按照C列排序
则顺序为
a..b..c1..d..
a..b..c1..d..
a..b..c1..d..
a..b..c2..d..
a..b..c2..d..
a..b..c2..d..
a..b..c3..d..
a..b..c3..d..
a..b..c3..d..
....
select A,B,C,D from
(select distinct A from table) AA
left join
(select distinct B from table) BB
on 1=1
left join
(select distinct C from table) CC
on 1=1
left join
(select distinct D from table) DD
on 1=1
order by '某一列'
先对C列排序,可以使用DataView
然后循环嵌套,4个,
每一列都循环一次。
select * from (select 'a1' as a union select 'a2' union select 'a3') a,
(select 'b1' as b union select 'b2') b,
(select 'c1' as c union select 'c2' union select 'c3' union select 'c4') c,
(select 'd1' as d union select 'd2' union select 'd3' union select 'd4' union select 'd5') d
order by a,b,c,d
(
SELECT A FROM tb
) a,
(
SELECT B FROM tb
) b,
(
SELECT C FROM tb
) c,
(
SELECT D FROM tb
)d
ORDER BY A,B,C,D
SELECT a.A||b.B||c.C||d.D FROM tab a,tab b,tab c,tab d
WHERE a.A IS NOT NULL
AND b.B IS NOT NULL
AND c.C IS NOT NULL
AND d.D IS NOT NULL
ORDER BY a.A,b.B,c.C,d.D
关键词 DISTINCT 用于返回唯一不同的值。
所以........cross join.......就足够了
(
a nvarchar(20),
b nvarchar(20),
c nvarchar(20),
d nvarchar(20)
)
insert into temp values('a1','b1','c1','d1')
insert into temp values('a2','b2','c2','d2')
insert into temp values('a3','b3','c3','d3')select * from
(select a from temp) as aa
join
(select b from temp) as bb
on 1=1
join
(select c from temp) as cc
on 1=1
join
(select d from temp) as dd
on 1=1
(
a nvarchar(20),
b nvarchar(20),
c nvarchar(20),
d nvarchar(20)
)
insert into temp values('a1','b1','c1','d1')
insert into temp values('a2','b2','c2','d2')
insert into temp values('a3','b3','c3','d3')select * from
(select a from temp) as aa
join
(select b from temp) as bb
on 1=1
join
(select c from temp) as cc
on 1=1
join
(select d from temp) as dd
on 1=1