;with t as (select balance as dibit,row_number() over (order by getdate()) from ta where balance > 0 union all select balance as crd,row_number() over (order by getdate()) from ta where balance <= 0 ) select * from t;
能写清楚点吗,如果我想用一个select语句完成呢
DECLARE @ta TABLE (balance INT) INSERT INTO @ta SELECT 100 UNION ALL SELECT -199 UNION ALL SELECT 188 UNION ALL SELECT -234select t1.dibit,t2.crd from (select row_number() over (order by getdate()) as r1 ,balance as dibit from @ta where balance >=0) t1 full join (select row_number() over (order by getdate()) as r2 ,balance as crd from @ta where balance <0) t2 on t1.r1 = t2.r2/* dibit crd ----------- ----------- 100 -199 188 -234 */
Select case when balance>=0 then balance end as dibit, case when balance<0 then balance end as crd from balance这种思路试试!
as
(select balance as dibit,row_number() over (order by getdate()) from ta where balance > 0
union all
select balance as crd,row_number() over (order by getdate()) from ta where balance <= 0 )
select * from t;
INSERT INTO @ta
SELECT 100 UNION ALL
SELECT -199 UNION ALL
SELECT 188 UNION ALL
SELECT -234select t1.dibit,t2.crd from
(select row_number() over (order by getdate()) as r1 ,balance as dibit from @ta where balance >=0) t1
full join
(select row_number() over (order by getdate()) as r2 ,balance as crd from @ta where balance <0) t2
on t1.r1 = t2.r2/*
dibit crd
----------- -----------
100 -199
188 -234
*/
case when balance<0 then balance end as crd
from balance这种思路试试!
100 NULL
NULL -199
188 NULL
NULL -234
NULL -234
返回是这样的结果