字段拆分，在线等，给分

[sid1001]

数据库里有一字段balance，根据他的正负值把它插入到不同的变量，或者select出来的时候根据正负值给它AS不同的别名，
balance
100
-199
188
-234
变成
dibit crd
100 -199
189 -234

[PB菜鸟]

row_number() OVER?

[lxf2000104]

3楼正解

[bushy]

Select case when balance>=0 then balance end as dibit,
case when balance<0 then balance end as crd
from balance

这种思路试试！

[lxf2000104]

6楼不行，
100 NULL
NULL -199
188 NULL
NULL -234
NULL -234
返回是这样的结果

[htl258_Tony]

SQL2000要用到临时表,一条语句不好实现.

[-狙击手-]

;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;

[sid1001]

能写清楚点吗，如果我想用一个select语句完成呢

[luckyleaf521]

DECLARE @ta TABLE (balance INT) 
INSERT INTO @ta
SELECT 100 UNION ALL
SELECT -199 UNION ALL
SELECT 188 UNION ALL
SELECT -234

select 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
*/