SELECT * , if( a > b, 999999999 - a - b / a *100, c ) AS ord FROM `abc` ORDER BY ord DESC LIMIT 0 , 30 999999 可以替换为 MAX(C) + 1最终 SELECT * , if( a > b, (select max(c) from `abc`) + 1 - (a - b / a *100), c ) AS ord FROM `abc` ORDER BY ord DESC
SELECT * , if( a > b, (select max(c) from `abc`)+(a-b)/a*100,c) as ord FROM `abc` ORDER BY ord DESC 这个是你要的
不知道是什么数据库,姑且假设是mysqlselect id from table order by (a-b)/a*100 desc where a>b limit 5 union select id from table order by c desc where a<=b limit 5这样最多能够选出10个记录,如果一共想选出5个记录select * from ( select id from table order by (a-b)/a*100 desc where a>b limit 5 union select id from table order by c desc where a<=b limit 5 ) limit 5
多谢大家热心帮忙,参考了这么多意见以及网路查询,终于搞定了~~~~主要参考的是hhzh426的句子,感谢hhzh426的帮忙,虽然有些小错误。 按照hhzh426的句子,会出现“Incorrect usage of UNION and ORDER BY”的错误,mysql的union字句不支持order by,去掉order by会出现“Every derived table must have its own alias”的错误。 这是上例中改后的句子select * from ( select * from s1 order by (a-b)/a*100 desc where a>b)as t1 union select * from ( select * from s1 order by c desc where a<=b)as t2 limit 10;
可以参照如下数据库,数据库表名s1:
id a b c
1 53 34 39
2 27 11 29
3 0 17 95
4 5 283 58
5 37 37 93
. . . .
. . . .
. . . .
. . . .如果 a-b>0, 按照公式(a-b)/a*100由大到小排列a-b>0的所有数组
如果 a-b<=0 则按照c的大小由大到小排列出a-b<=0的所有数组
希望能够编写详细些的php代码,谢谢!PS:结贴率小是因为我只发过这两个相同的帖子,这个是加分的帖,另一个没加分,都没结贴。
上例中排列的结果是
2
1 //以上是a>b的情况再按公式计算后的排列,以下是a<=b的情况按c的大小排列。
3
5
4
FROM `abc`
ORDER BY ord DESC
LIMIT 0 , 30 999999 可以替换为 MAX(C) + 1最终
SELECT * , if( a > b, (select max(c) from `abc`) + 1 - (a - b / a *100), c ) AS ord
FROM `abc`
ORDER BY ord DESC
FROM `abc`
ORDER BY ord DESC 这个是你要的
union
select id from table order by c desc where a<=b limit 5这样最多能够选出10个记录,如果一共想选出5个记录select * from
(
select id from table order by (a-b)/a*100 desc where a>b limit 5
union
select id from table order by c desc where a<=b limit 5
) limit 5
按照hhzh426的句子,会出现“Incorrect usage of UNION and ORDER BY”的错误,mysql的union字句不支持order by,去掉order by会出现“Every derived table must have its own alias”的错误。
这是上例中改后的句子select * from
(
select * from s1 order by (a-b)/a*100 desc where a>b)as t1
union
select * from
(
select * from s1 order by c desc where a<=b)as t2 limit 10;