mysql - 按升序返回行

Question

我有一个包含 392871961 行的表。

     p_id        r_p_id      weight
     1           2           0.223923923
     1           3           0.12923923
     1           4           0.423926924
     1           5           0.143921921
     1           6           0.323923922
     1           7           0.223923728
     .
     .
     .
     2           1           0.123923921
     2           3           0.023923922
     2           4           0.223923926
     2           5           0.323923928
     2           6           0.223923921
     2           7           0.423923921
     .
     .
     .
     3           1           0.023923925
     3           2           0.223923922
     3           4           0.123923926
     3           5           0.223923929
     3           6           0.123923921
     3           7           0.523923922
     .
     .
     .

p_id 上升到略高于 6000 位。 weight 表示 p_id 和 r_p_id 之间的某种优先级。

我有两个这样的查询。用户将提供 p_id 列表。它将少于 10 个 p_id。

第一个查询 = select r_p_id, weight from table where p_id in (....) order by weight

第二个查询 = select r_p_id, (weight*0.8) as m_weight from table where p_id in (....) order by (weight * 0.8)

我想组合这些查询以按(第一个查询的权重)+(第二个查询的权重)的总和按升序返回 r_p_id。

谁能告诉我怎么做？

我很感激。

Answer 1

试试这个

select t1.r_p_id, (t1.weight+t2.m_weight) as total_weight
from 
    (select r_p_id, weight from table where p_id in (....) ) as t1,
    (select r_p_id, (weight*0.8) as m_weight from table where p_id in (....)) as t2
where t1.r_p_id=t2.r_p_id
order by (t1.weight+t2.m_weight)

OR 使用 JOIN 关键字

select t1.r_p_id, (t1.weight+t2.m_weight) as total_weight
from 
    (select r_p_id, weight from table where p_id in (....) ) as t1 INNER JOIN
    (select r_p_id, (weight*0.8) as m_weight from table where p_id in (....)) as t2 ON t1.r_p_id=t2.r_p_id
order by (t1.weight+t2.m_weight)

您需要在两个查询的结果之间使用 JOIN。

编辑:

因为 MySQL 支持 order by [alias-name] 作为 dg99建议，可以直接使用order by total_weight

select t1.r_p_id, (t1.weight+t2.m_weight) as total_weight
from 
    (select r_p_id, weight from table where p_id in (....) ) as t1,
    (select r_p_id, (weight*0.8) as m_weight from table where p_id in (....)) as t2
where t1.r_p_id=t2.r_p_id
order by total_weight