22,209
社区成员
发帖
与我相关
我的任务
分享下面的需求，有什么比較好的語句達到
Create table #t (aa varchar(10),bb varchar(8))
insert into #t select 'A','01'
insert into #t select 'A','03'
insert into #t select 'A','04'
insert into #t select 'A','06'
insert into #t select 'A','02'
insert into #t select 'A','05'
insert into #t select 'B','02'
insert into #t select 'B','03'
insert into #t select 'B','04'
insert into #t select 'B','01'
insert into #t select 'C','01'
insert into #t select 'C','03'
insert into #t select 'C','02'
要求輸出如下：
0|00
A|01
A|02
A|03
A|04
A|05
A|06
0|00
B|01
B|02
B|03
B|04
0|00
C|01
C|02
C|03
insert into #t select 'A','01'
insert into #t select 'A','03'
insert into #t select 'A','04'
insert into #t select 'A','06'
insert into #t select 'A','02'
insert into #t select 'A','05'
insert into #t select 'B','02'
insert into #t select 'B','03'
insert into #t select 'B','04'
insert into #t select 'B','01'
insert into #t select 'C','01'
insert into #t select 'C','03'
insert into #t select 'C','02'
要求輸出如下：
0|00
A|01
A|02
A|03
A|04
A|05
A|06
0|00
B|01
B|02
B|03
B|04
0|00
C|01
C|02
C|03
...全文
请发表友善的回复…
发表回复
lovezx1028 2009-06-28
- 打赏
- 举报
xue xi
gxg353 2009-06-25
- 打赏
- 举报
select '0'+'|'+'00' union all
select aa+'|'+ bb from #t where aa='A' union all
select '0'+'|'+'00' union all
select aa+'|'+ bb from #t where aa='B' union all
select '0'+'|'+'00' union all
select aa+'|'+ bb from #t where aa='C'
------------------
0|00
A|01
A|03
A|04
A|06
A|02
A|05
0|00
B|02
B|03
B|04
B|01
0|00
C|01
C|03
C|02
gxg353 2009-06-25
- 打赏
- 举报
select * from #t where aa='A' union all
select aa='0',bb='00' union all
select * from #t where aa='B' union all
select aa='0',bb='00' union all
select * from #t where aa='C'
A 01
A 03
A 04
A 06
A 02
A 05
0 00
B 02
B 03
B 04
B 01
0 00
C 01
C 03
C 02
feixianxxx 2009-06-25
- 打赏
- 举报
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
Create table #t (aa varchar(10),bb varchar(8))
insert into #t select 'A','01'
insert into #t select 'A','03'
insert into #t select 'A','04'
insert into #t select 'A','06'
insert into #t select 'A','02'
insert into #t select 'A','05'
insert into #t select 'B','02'
insert into #t select 'B','03'
insert into #t select 'B','04'
insert into #t select 'B','01'
insert into #t select 'C','01'
insert into #t select 'C','03'
insert into #t select 'C','02'
go
select 输出=t.aa+'|'+bb
from(select * from #t
union all
select '0','00') t
order by aa,bb
/*------------
0|00
A|01
A|02
A|03
A|04
A|05
A|06
B|01
B|02
B|03
B|04
C|01
C|02
C|03
-------*/
drysea 2009-06-25
- 打赏
- 举报
SQL code
select aa+'|'+bb from
(
select aa as tmp , aa,bb
from #t
union all
select distinct aa ,'0','00'
from #t
) A
order by tmp, bb
drysea 2009-06-25
- 打赏
- 举报
[Quote=引用 1 楼 playwarcraft 的回复:]
SQL code
select aa,bb from
(
select aa as tmp , aa,bb
from #t
union all
select distinct aa ,'0','00'
from #t
) A
order by tmp, bb
/*
aa bb
-----------------------
0 00
A 01
A 02
A 03
A 04
A 05
A 06
0 00
B 01
B 02
B 03
B 04
0 00
C 01
C 02
C 03
*/
[/Quote]
顶
SQL code
select aa,bb from
(
select aa as tmp , aa,bb
from #t
union all
select distinct aa ,'0','00'
from #t
) A
order by tmp, bb
/*
aa bb
-----------------------
0 00
A 01
A 02
A 03
A 04
A 05
A 06
0 00
B 01
B 02
B 03
B 04
0 00
C 01
C 02
C 03
*/
[/Quote]
顶
pt1314917 2009-06-25
- 打赏
- 举报
1楼的不错。学习了。
xyworkroom 2009-06-25
- 打赏
- 举报
[Quote=引用 1 楼 playwarcraft 的回复:]
SQL code
select aa,bb from
(
select aa as tmp , aa,bb
from #t
union all
select distinct aa ,'0','00'
from #t
) A
order by tmp, bb
/*
aa bb
-----------------------
0 00
A 01
A 02
A 03
A 04
A 05
A 06
0 00
B 01
B 02
B 03
B 04
0 00
C 01
C 02
C 03
*/
[/Quote]
select aa+'|'+bb from
(
select aa as tmp , aa,bb
from #t
union all
select distinct aa ,'0','00'
from #t
) A
order by tmp, bb
SQL code
select aa,bb from
(
select aa as tmp , aa,bb
from #t
union all
select distinct aa ,'0','00'
from #t
) A
order by tmp, bb
/*
aa bb
-----------------------
0 00
A 01
A 02
A 03
A 04
A 05
A 06
0 00
B 01
B 02
B 03
B 04
0 00
C 01
C 02
C 03
*/
[/Quote]
select aa+'|'+bb from
(
select aa as tmp , aa,bb
from #t
union all
select distinct aa ,'0','00'
from #t
) A
order by tmp, bb
csdyyr 2009-06-25
- 打赏
- 举报
select aa+'|'+bb
from
(select distinct aa,'00' as bb from #t
union all
select aa,bb from #t
) t
order by aa,bb
/*
A|00
A|01
A|02
A|03
A|04
A|05
A|06
B|00
B|01
B|02
B|03
B|04
C|00
C|01
C|02
C|03
*/pt1314917 2009-06-25
- 打赏
- 举报
if object_id('tempdb..#t') is not null drop table #t
Create table #t (aa varchar(10),bb varchar(8))
insert into #t select 'A','01'
insert into #t select 'A','03'
insert into #t select 'A','04'
insert into #t select 'A','06'
insert into #t select 'A','02'
insert into #t select 'A','05'
insert into #t select 'B','02'
insert into #t select 'B','03'
insert into #t select 'B','04'
insert into #t select 'B','01'
insert into #t select 'C','01'
insert into #t select 'C','03'
insert into #t select 'C','02'
select * from #t a
union all
select a.* from (select a='0',b='00')a full join
(select distinct aa from #t)b on 1=1
--结果：
aa bb
---------- --------
A 01
A 03
A 04
A 06
A 02
A 05
B 02
B 03
B 04
B 01
C 01
C 03
C 02
0 00
0 00
0 00
csdyyr 2009-06-25
- 打赏
- 举报
aa bb
---------- --------
A 00
A 01
A 02
A 03
A 04
A 05
A 06
B 00
B 01
B 02
B 03
B 04
C 00
C 01
C 02
C 03csdyyr 2009-06-25
- 打赏
- 举报
select *
from
(select distinct aa,'00' as bb from #t
union all
select aa,bb from #t
) t
order by aa,bb
playwarcraft 2009-06-25
- 打赏
- 举报
select aa,bb from
(
select aa as tmp , aa,bb
from #t
union all
select distinct aa ,'0','00'
from #t
) A
order by tmp, bb
/*
aa bb
-----------------------
0 00
A 01
A 02
A 03
A 04
A 05
A 06
0 00
B 01
B 02
B 03
B 04
0 00
C 01
C 02
C 03
*/
