select a.baseinfoid,b.homeid,c.price from a,b,c where a.baseinfoid=b.baseinfoid and b.homeid=c.homeid order by c.price
这样我试过了,不是我需要的结果。 select a.* from hotel_base_info a,hotelhometype b,hotelhomeprice c where a.baseinfoid=b.hotelbaseinfoid and a.citry_id='4028888f056685a201056685ba0d00e0' and b.hotelhome_id=c.hotelhome_id order by c.sales_price 而且这样查询由于多了很多条记录
我尝试通过子查询来实现: select hotel_base_info.baseinfoid from hotel_base_info where hotel_base_info.baseinfoid in ( select a.baseinfoid from hotel_base_info a,hotelhometype b,hotelhomeprice c where a.baseinfoid=b.hotelbaseinfoid and a.citry_id='4028888f056685a201056685ba0d00e0' and b.hotelhome_id=c.hotelhome_id order by c.sales_price ); 但是提示:缺少右括号,应该可是使用order by的。
select a.* from hotel_base_info a left join hotelhometype b on a.baseinfoid=b.hotelbaseinfoid left join hotelhomeprice c on b.hotelhome_id=c.hotelhome_id order by c.sales_price
晕了,不明白,既然用了in,那里面还用order by 干嘛啊,难道in(1,2,3)和in(2,1,3)不是一样的吗还是不明白你究竟要得到什么样的结果
你要怎么取数据还没说哪?如果没其它条件的话,这么写就因该是对的 select a.*,b.* from hotel_base_info a,hotelhometype b,hotelhomeprice c where a.baseinfoid=b.hotelbaseinfoid and b.hotelhome_id=c.hotelhome_id and a.citry_id='4028888f056685a201056685ba0d00e0' order by c.sales_price
目前排序如下: select * from( select a.baseinfoid,b.hotelhome_id,c.sales_price,c.cash_price,c.priority from hotel_base_info a,hotelhometype b,hotelhomeprice c where a.baseinfoid=b.hotelbaseinfoid and b.hotelhome_id=c.hotelhome_id and a.citry_id='4028888f056685a201056685ba0d00e0' and ( c.cash_price<>'0' and c.cash_price is not null) order by c.cash_price asc ) t where (t.sales_price is not null or t.cash_price is not null) order by t.cash_price asc ,这样排序已经没问题了,但是结果里面有很多重复的纪录!我假定上面的纪录结果集合是u, 我用 select distinct baseinfoid from u来去掉重复的纪录,可是这样,排序又乱了,请教各位该怎么解决啊?
select a.*
from hotel_base_info a,hotelhometype b,hotelhomeprice c
where a.baseinfoid=b.hotelbaseinfoid
and a.citry_id='4028888f056685a201056685ba0d00e0'
and b.hotelhome_id=c.hotelhome_id
order by c.sales_price
而且这样查询由于多了很多条记录
select hotel_base_info.baseinfoid from hotel_base_info where hotel_base_info.baseinfoid in (
select a.baseinfoid
from hotel_base_info a,hotelhometype b,hotelhomeprice c
where a.baseinfoid=b.hotelbaseinfoid
and a.citry_id='4028888f056685a201056685ba0d00e0'
and b.hotelhome_id=c.hotelhome_id order by c.sales_price );
但是提示:缺少右括号,应该可是使用order by的。
from hotel_base_info a
left join hotelhometype b on a.baseinfoid=b.hotelbaseinfoid
left join hotelhomeprice c on b.hotelhome_id=c.hotelhome_id
order by c.sales_price
主表:hotel_base_info 字段
BASEINFOID VARCHAR2(32),
CITRY_ID VARCHAR2(32),--城市ID
NAME_CN VARCHAR2(50),--中文名称
其他省略
从表 hotelhometype
HOTELHOME_ID VARCHAR2(32) --该表主键
HOTELBASEINFOID VARCHAR2(32) --外键
HOME_NAME_CN VARCHAR2(500) --中文名称
从表 hotelhomeprice
HOMEPRICEID VARCHAR2(32)
HOTELHOME_ID VARCHAR2(32)
PIRCE FLOAT
SALES_PRICE FLOAT
CASH_PRICE FLOAT
select a.*,b.*
from hotel_base_info a,hotelhometype b,hotelhomeprice c
where a.baseinfoid=b.hotelbaseinfoid
and b.hotelhome_id=c.hotelhome_id
and a.citry_id='4028888f056685a201056685ba0d00e0'
order by c.sales_price
目前排序如下:
select * from(
select a.baseinfoid,b.hotelhome_id,c.sales_price,c.cash_price,c.priority
from hotel_base_info a,hotelhometype b,hotelhomeprice c
where a.baseinfoid=b.hotelbaseinfoid
and b.hotelhome_id=c.hotelhome_id
and a.citry_id='4028888f056685a201056685ba0d00e0'
and ( c.cash_price<>'0' and c.cash_price is not null)
order by c.cash_price asc
) t
where (t.sales_price is not null or t.cash_price is not null)
order by t.cash_price asc
,这样排序已经没问题了,但是结果里面有很多重复的纪录!我假定上面的纪录结果集合是u,
我用 select distinct baseinfoid from u来去掉重复的纪录,可是这样,排序又乱了,请教各位该怎么解决啊?