多字段取最小排序问题

[北京不不]

刚好做这么个东西
房屋买卖的
房地产司众多
现在做搜索功能

房屋类型4种,价格分别不同
selname room1,room2,room3,room4
郑州房地产公司 130 30 20 50
鱼鳞房地产公司 40 10 60 90
百新房地产公司 60 70 90 120

现在客户要按价格排序,就是取四种房型最低的价格来排序
如上:郑州房地产公司 最低是room3 20万
鱼鳞房地产公司 最低是room2 10万
百新房地产公司 最低是room1 60万

那么想达到的排序方式就是
selname room1 room2 room3 room4
鱼鳞房地产公司 40 10 60 90
郑州房地产公司 130 30 20 50
百新房地产公司 60 70 90 120

语句如何写呢
select * from room order by ....?什么呢

[zhangjiewoshiwo]

LZ的意思是先找出room1最低价格的房地产公司，然后再找出room2、room3、room4的。。。。还是只要输出
selname room1 room2 room3 room4
鱼鳞房地产公司 40 10 60 90
郑州房地产公司 130 30 20 50
百新房地产公司 60 70 90 120
这张表就OK
如果只是要上面这样的效果的话那就直接：select * from room order by room1；

[kitty09_lei]

按room1排序达不到他要的效果！！！
他那样得按（room1+room2+room3+room4）得和来排序吧？

[zhangjiewoshiwo]

不好意思我看错了。。。。。我还是没弄明白你到底是怎么排的！能说详细点吗？

现在客户要按价格排序,就是取四种房型最低的价格来排序
如上:郑州房地产公司 最低是room3 20万
鱼鳞房地产公司 最低是room2 10万
百新房地产公司 最低是room1 60万

这断不是很明白

[kitty09_lei]

是不是得把每个地产公司里的最低的价格找出来，放到一个列里，再对这个列排序？

[shenzhenNBA]

房屋类型4种,价格分别不同
selname room1,room2,room3,room4
郑州房地产公司 130 30 20 50
鱼鳞房地产公司 40 10 60 90
百新房地产公司 60 70 90 120

现在客户要按价格排序,就是取四种房型最低的价格来排序
如上:郑州房地产公司 最低是room3 20万
鱼鳞房地产公司 最低是room2 10万
百新房地产公司 最低是room1 60万

LZ这里是涉及到表格一行内跨列的最大值求法，这比较难，
如果改成是各个列之和的大小排列如下

SELECT SELNAME, ROOM1, ROOM2, ROOM3, ROOM4 FROM [表] ORDER BY (room1+room2+room3+room4) ASC

[mailbao]

直接用sql應該實現不了樓主的idea,
但如果先通過room1,room2,room3,room4四列各取最小值再進行比較排序倒是能夠達到目的,
但這樣做似乎很繁瑣,
不知是否有高人能夠一條sql實現......

[hookee]

SQL Server的话，可自定义函数

CREATE FUNCTION minvalue(@col1 int, @col2 int, @col3 int, @col4 int )  
returns int
as
begin
    declare @tablemin Table ( minval  int )
    insert into @tablemin (minval) values (@col1)
    insert into @tablemin(minval) values (@col2)
    insert into @tablemin (minval) values (@col3)
    insert into @tablemin (minval) values (@col3)
    return (select min(minval) from @tablemin where minval is not null)
End


SELECT selname, room1,room2,room3,room4,
	dbo.minvalue(room1,room2,room3,room4) AS minvalue
FROM XXX
ORDER BY minvalue 

[hookee]

改一下:

CREATE FUNCTION minvalue(@col1 int, @col2 int, @col3 int, @col4 int )  
returns int
as
begin
    declare @tablemin Table ( minval  int )
    insert into @tablemin (minval) values (@col1)
    insert into @tablemin(minval) values (@col2)
    insert into @tablemin (minval) values (@col3)
    insert into @tablemin (minval) values (@col4)
    return (select min(minval) from @tablemin where minval is not null)
End


SELECT selname, room1,room2,room3,room4,
	dbo.minvalue(room1,room2,room3,room4) AS minvalue
FROM XXX
ORDER BY minvalue 

[kitty09_lei]

“hookee”真是我偶像！！！

[pdq315]

ding

[cuixiping]

SELECT selname, ( case
   when room1<=room2 and room1<=room3 and room1<=room4 then room1
   when room2<=room1 and room2<=room3 and room2<=room4 then room2
   when room3<=room1 and room3<=room2 and room3<=room4 then room3
   when room4<=room1 and room4<=room2 and room4<=room3 then room4
) AS minvalue
FROM XXX ORDER BY minvalue

这样更直接，1个sql语句搞定。

[zzhqiao]

SELECT selname,room1,room2,room3,room4 FROM room ORDER BY case
when room1<=room2 and room1<=room3 and room1<=room4 then room1
when room2<=room1 and room2<=room3 and room2<=room4 then room2
when room3<=room1 and room3<=room2 and room3<=room4 then room3
when room4<=room1 and room4<=room2 and room4<=room3 then room4

[zzhqiao]

SELECT selname,room1,room2,room3,room4 FROM room ORDER BY case
when room1 <=room2 and room1 <=room3 and room1 <=room4 then room1
when room2 <=room1 and room2 <=room3 and room2 <=room4 then room2
when room3 <=room1 and room3 <=room2 and room3 <=room4 then room3
when room4 <=room1 and room4 <=room2 and room4 <=room3 then room4
end

[一把编程的菜刀]

这个是可以解决的

你的什么数据库？

MSSQL数据库用 case when
access数据库用 iif
MSSQL 数据库：
SELECT selname, ( case
when room1<=room2 and room1<=room3 and room1<=room4 then room1
when room2<=room1 and room2<=room3 and room2<=room4 then room2
when room3<=room1 and room3<=room2 and room3<=room4 then room3
when room4<=room1 and room4<=room2 and room4<=room3 then room4
) AS minvalue
FROM XXX ORDER BY minvalue

有时候这样会提示错误，最好是

SELECT * FROM XXX ORDER BY ( case
   when room1<=room2 and room1<=room3 and room1<=room4 then room1
   when room2<=room1 and room2<=room3 and room2<=room4 then room2
   when room3<=room1 and room3<=room2 and room3<=room4 then room3
   when room4<=room1 and room4<=room2 and room4<=room3 then room4
)

access的同理：

SELECT * FROM XXX ORDER BY IIf(room1<=room2 and room1<=room3 and room1<=room4, room1, IIf(room2<=room1 and room2<=room3 and room2<=room4,room2,iif(room3<=room1 and room3<=room2 and room3<=room4,room3,room4)))