有哪位高手可以实现这个查询功能2
有谁可以实现这个查询功能2
table1
a b
15,000 1
4,000 1
1,762 -1
7,000 1
2,207 -1
1,644 -1
table2
c d
15,000
4,000
1,762
7,000
2,207
1,644
怎么通过查询表1，根据表1 b列的值得到表2的格式，用sql语句直接查询出来
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
select case when b=1 then a else 0 end as c,
case when b=-1 then a else 0 end as d
from table1
table1
a b
15,000 1
4,000 1
1,762 -1
7,000 1
2,207 -1
1,644 -1
table2
c d
15,000
4,000
1,762
7,000
2,207
1,644
怎么通过查询表1，根据表1 b列的值得到表2的格式，用sql语句直接查询出来
------解决方案--------------------
with a(a,b)as(
select 15.000,1 union all
select 4.000, 1 union all
select 1.762,-1 union all
select 7.000, 1 union all
select 2.207,-1 union all
select 1.644,-1)
, tc as(
select number=ROW_NUMBER()over(order by getdate()),* from a
)
select c,d from (
select number,a c,null d from tc where b=1
union all
select number,null,a from tc where b=-1)a
order by number
------解决方案--------------------
select
case when b=1 then a else null end c,
case when b=-1 then a else null end d
from table1
------解决方案--------------------
select case when b=1 then a else 0 end as c,
case when b=-1 then a else 0 end as d
from table1