用case when 得到的列，提示列名无效
SELECT HS_ID, HS_provinceid, HS_cityid, HS_districtid, HS_liveCriclesid, HS_subzoneid, HS_address, HS_issueType, HS_houseType,HS_bedroom,HS_livingroom,HS_toilet,HS_areas,HS_price, HS_priceUnit,HS_addTime,HS_commend,HS_shareType,HS_money=
(
case HS_priceUnit
when 1 then HS_Price
when 2 then HS_Price*HS_areas/10000
when 3 then HS_price
when 4 then HS_Price*HS_areas
end)
FROM syinfo_HouseSource where HS_money between 1 and 10000
[解决办法]
--试试
select *
from(
SELECT HS_ID, HS_provinceid, HS_cityid, HS_districtid, HS_liveCriclesid, HS_subzoneid, HS_address, HS_issueType, HS_houseType,HS_bedroom,HS_livingroom,HS_toilet,HS_areas,HS_price, HS_priceUnit,HS_addTime,HS_commend,HS_shareType,HS_money=
(
case HS_priceUnit
when 1 then HS_Price
when 2 then HS_Price*HS_areas/10000
when 3 then HS_price
when 4 then HS_Price*HS_areas
end)
FROM syinfo_HouseSource) a
where HS_money between 1 and 10000
或者
SELECT HS_ID, HS_provinceid, HS_cityid, HS_districtid, HS_liveCriclesid, HS_subzoneid, HS_address, HS_issueType, HS_houseType,HS_bedroom,HS_livingroom,HS_toilet,HS_areas,HS_price, HS_priceUnit,HS_addTime,HS_commend,HS_shareType,HS_money=
(
case HS_priceUnit
when 1 then HS_Price
when 2 then HS_Price*HS_areas/10000
when 3 then HS_price
when 4 then HS_Price*HS_areas
end)
FROM syinfo_HouseSource where
(
case HS_priceUnit
when 1 then HS_Price
when 2 then HS_Price*HS_areas/10000
when 3 then HS_price
when 4 then HS_Price*HS_areas
end)
between 1 and 10000
