例表tb:
id price netprice
1 8.00 20.00
2 10.00 5.00
3 0.00 3.00
我想要查出price与netprice之间大的列
比如查出如下数据:
id newprice
1 20.00
2 10.00
3 3.00
sql语句要怎么写?
id price netprice
1 8.00 20.00
2 10.00 5.00
3 0.00 3.00
我想要查出price与netprice之间大的列
比如查出如下数据:
id newprice
1 20.00
2 10.00
3 3.00
sql语句要怎么写?
Select
id,
(Case When price > netprice Then price Else netprice End) As newprice
From
tb
from 表tb
(
select id , price from tb
union all
select id , netprice price from tb
) t
group by id
t.id,max(price) as price
from
(select id,price from 表 union all select id,newprice from 表) t
group by
t.id
order by
t.id
Create Table tb
(id Int,
price Numeric(10, 2),
netprice Numeric(10, 2))Insert tb Select 1, 8.00, 20.00
Union All Select 2, 10.00, 5.00
Union All Select 3, 0.00, 3.00
GO
--測試
Select
id,
(Case When price > netprice Then price Else netprice End) As newprice
From
tb
--刪除測試環境
Drop Table tb
--結果
/*
id newprice
1 20.00
2 10.00
3 3.00
*/
insert tb
select 1 , 8.00 , 20.00
union select 2 , 10.00 , 5.00
union select 3 , 0.00 , 3.00 select id , nweprice=case when price>netprice then price else netprice end
from tbdrop table tb
/*
id nweprice
----------- -----------
1 20
2 10
3 3(3 row(s) affected)*/
insert into tb values(2, 10.00, 5.00)
insert into tb values(3, 0.00 , 3.00 )select id , max(price) price from
(
select id , price from tb
union all
select id , netprice price from tb
) t
group by iddrop table tb/*
id price
----------- --------------------
1 20.00
2 10.00
3 3.00(所影响的行数为 3 行)
*/
1 8.00 20.00
2 10.00 5.00
3 0.00 3.00
我想要查出price与netprice之间大的列
比如查出如下数据:
id newprice
1 20.00
2 10.00
3 3.00
sql语句要怎么写?
----------select id ,newprice=case when price>netprice then price else netprice end
from t
insert into #tmp values(2,10.00, 5.00)
insert into #tmp values(3,0.00,3.00)---------------------------------------
select id ,case when price>netprice then price else netprice end as newprice
from #tmp
from tb