dongque5529 2012-04-17 00:29
浏览 33
已采纳

MYSQL - 具有最低值和其他条件的SELECT行,或具有最高值的回退

there seem to be a lot of threads here asking similar questions, but I'm yet to find the one that helps me solve my problem.

I'm trying to select a row with the lowest value where three other conditions must be true OR fall back to another set of conditions, given a variable (uc) and select the highest price.

Table: level (sample data)
id  |price  |copies |used   |uc
--------------------------------------
1   |7      |10     |10     |charlie01
2   |17     |10     |8      |charlie01
3   |37     |5      |5      |charlie01
4   |47     |10     |9      |charlie01
7   |5      |15     |9      |charlie02
8   |15     |15     |0      |charlie02
9   |35     |15     |0      |charlie02
10  |45     |15     |0      |charlie02
11  |3      |5      |5      |charlie03
12  |13     |5      |5      |charlie03

Results: EDITED RESULTS - charlie01 should be 2

VARIABLE    |RESULTING ROW
--------------------------
charlie01   |2
charlie02   |7
charlie03   |12

### START Pseudocode: ###

Select the row with the lowest price where used is less than copies and used is greater than 0 and uc equals VARIABLE OR IF NO RESULTS (which means all the used are equal to copies) Select the row with the highest price where used equals copies and uc equals VARIABLE

SELECT * 
FROM level
WHERE

    (
        used < copies
        AND used > 0
        AND uc = *VARIABLE*
    )
 ### OR IF NO RESULTS ###
    (
        used = copies
        AND uc = *VARIABLE*
    )

ORDER BY price ASC
LIMIT 1

### END Pseudocode ###

This is the query I wrote (using uc charlie01 as an example):

SELECT *
FROM (
    SELECT *
    FROM `level` 
    WHERE 
        ((
        `used` < `copies`
        AND `used` > '0'
        )
        OR
        (
        `used` = `copies`
        ))
        AND `uc` = 'charlie01'
        ORDER BY `price` ASC

        ) AS T
ORDER BY `used` ASC
LIMIT 1

It doesn't work on the sample data because of record 3, even though used = copies. This is because used on record 3 < used on record 2 (the row we want, because it has the lowest price)

I know MAX() exists and have tried for the last hour to use it, but with no success, I really don't think I understand how it works, It seems to just be selecting the maximum value of the whole column no matter what...?

Now, I've gone round in circles adding conditions and such, but I think I need to do a JOIN of some type but I'm not really sure where to start with it. How do you think I should go about this query?

EDIT: I'm really sorry, I just realised I was saying highest price when I was actually trying to select the lowest price where used < copies BUT the highest price if all used = copies

Cheers
Charlie

  • 写回答

1条回答 默认 最新

  • douzi7219 2012-04-19 05:51
    关注

    if you're working on production code with this, i'd wait for a better answer, but a couple interesting options that might work for you...

    SELECT * FROM (
      SELECT *, (used < copies and used > 0) AS copies_avail FROM level
      ORDER BY copies_avail desc, IF(copies_avail, price, -price) ASC
    ) AS x GROUP BY uc;
    

    (you can just use the inner section if you want to specify a uc in a where clause)

    similar option that's even more interesting (trying not to say hackish =p) would be

    SELECT * FROM (
      SELECT * FROM level 
      ORDER BY IF(used < copies AND used > 0, price, -price + 10000) ASC
    ) AS x GROUP BY uc;
    

    (where 10000 is much higher than any expected price in the table)

    anyway... believe the output of both of these match your edited results, but are probably horrible performance wise. if you do get stuck doing something like this, you could probably store the copies_avail query result in the table to avoid the filesort?

    also, curious about the case where used = 0 and copies > 0 - couldn't tell from your description if it should be sorted with the price asc or price desc group. in these examples, it's with price desc.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择