表T:
字段:XH ZD1 ZD2
1 a1 a2
2 s2 s1
3 d7 d5
……
XH为序号通过sql查询得到的结果为:
1 a1
2 a2
3 s2
4 s1
5 d7
6 d5
……
这个sql怎么写?谢谢!
字段:XH ZD1 ZD2
1 a1 a2
2 s2 s1
3 d7 d5
……
XH为序号通过sql查询得到的结果为:
1 a1
2 a2
3 s2
4 s1
5 d7
6 d5
……
这个sql怎么写?谢谢!
WITH T AS (
SELECT 1 a, 'a1' b, 'a2' c FROM dual
UNION ALL SELECT 2, 's2', 's1' FROM dual
UNION ALL SELECT 3, 'd7', 'd5' FROM dual
)
SELECT ROW_NUMBER() OVER (ORDER BY a,c) ID,b FROM(
SELECT T.a,1 c,T.B FROM T
UNION ALL
SELECT T.a,2 c, T.c FROM T ORDER BY 1
) x;
WITH T AS (
SELECT 1 a, 'a1' b, 'a2' c FROM dual
UNION ALL SELECT 2, 's2', 's1' FROM dual
UNION ALL SELECT 3, 'd7', 'd5' FROM dual
)
SELECT ROW_NUMBER() OVER (ORDER BY a,d) ID,b FROM(
SELECT T.a,1 d,T.B FROM T
UNION ALL
SELECT T.a,2 d, T.c FROM T ORDER BY 1
) x
with t1 as
(
select 1 xh,'a1' zd1,'a2' zd2 from dual
union all
select 2 xh,'s2' zd1,'s1' zd2 from dual
union all
select 3 xh,'d7' zd1,'d5' zd2 from dual
)select zd1
from
(
select xh,zd1
from t1
union all
select xh+0.1,zd2
from t1
)
order by xh
zd1
----------------
1 a1
2 a2
3 s2
4 s1
5 d7
6 d5
SELECT xh * 2 - 1 AS xh1, xh AS xh2, zd1 FROM t
UNION
SELECT xh * 2 AS xh1, xh AS xh2, zd2 FROM t
ORDER BY xh1