mysql - 在mysql中对2列进行排序

Question

我有以下 Mysql 查询

SELECT DISTINCT
  main_Table.id,
  t1.percentage,
  t2.percentage
FROM image_new main_Table
  INNER JOIN (SELECT DISTINCT
    image_colors_new.image_id,
    image_colors_new.percentage
  FROM image_colors_new
  WHERE image_colors_new.s BETWEEN 80 AND 120 AND image_colors_new.h BETWEEN - 20 AND 20 AND image_colors_new.v BETWEEN 73 AND 103
  GROUP BY image_colors_new.percentage
  ORDER BY image_colors_new.percentage DESC) t1
    ON main_Table.id = t1.image_id
  INNER JOIN (SELECT DISTINCT
    image_colors_new.image_id,
    image_colors_new.percentage
  FROM image_colors_new
  WHERE image_colors_new.s BETWEEN - 20 AND 20 AND image_colors_new.h BETWEEN - 20 AND 20 AND image_colors_new.v BETWEEN 85 AND 115
  GROUP BY image_colors_new.percentage
  ORDER BY image_colors_new.percentage DESC) t2
    ON main_Table.id = t2.image_id

这让我跟进了

   id   percentage      percentage1
36888   32.975669099756 0.559610705596
54899   9.9722991689751 0.55401662049861
43584   9.9195710455764 9.3833780160858
61517   9.7938144329897 40.20618556701
78076   9.7267759562842 12.786885245902
52123   9.6916299559471 3.3039647577093
36378   9.5798319327731 18.655462184874
43820   9.5238095238095 10.31746031746
53579   9.4736842105263 5.6140350877193

但我想要以下格式的行，即具有最高值的百分比的行必须从任一列弹出到顶部

id      percentage      percentage1
61517   9.7938144329897 40.20618556701
36888   32.975669099756 0.559610705596
36378   9.5798319327731 18.655462184874
78076   9.7267759562842 12.786885245902
43820   9.5238095238095 10.31746031746
43584   9.9195710455764 9.3833780160858
54899   9.9722991689751 0.55401662049861
52123   9.6916299559471 3.3039647577093
53579   9.4736842105263 5.6140350877193

Answer 1

听起来您想按百分比值 中的较大者排序。添加:

order by greatest(t1.percentage, t2.percentage) desc;

这实际上应该放在外部查询中。不要养成在子查询中使用 order by 的习惯。它通常可以工作，但不能保证(在多线程实现的其他数据库中尤其如此)。