mysql SQL使用join选择最近的值

nkhmeac6 于 5个月前发布在 Mysql

关注(0)|答案(2)|浏览(49)

有两张table：
参考：

id  | score  | value | type_id
1   | 0      | 10    | 1
2   | 1      | 20    | 1
3   | 2      | 30    | 1
..  | ..     | ..    | ..

字符串
结果：

id  | score  | type_id
1   | 2      | 1
2   | 7      | 2
3   | 0      | 3

型
我想根据result表中的score，从refernce表中为每个type_id获取value。
查询：

SELECT ref.score, ref.value, ref.type_id
FROM `refernce` ref
JOIN `result` res
    ON ref.type_id = res.type_id
WHERE res.score >= ref.score
GROUP BY ref.type_id
ORDER BY ref.id DESC;

型
在这种情况下，输出应该是：

score | value   | type_id
0     | 8       | 3
3     | 25      | 2
2     | 30      | 1

型
但这是实际输出：

score | value   | type_id
0     | 8       | 3
0     | 5       | 2
0     | 10      | 1

型
因此，如果result表中的score存在于refernce中，则此分数的value，并且如果result表中的score大于refernce表中的value，则应返回大于score的value，即3。
这是一把小提琴：http：//sqlfiddle.com/#！9/ecf 1 e3/1

mysql

来源：https://stackoverflow.com/questions/77679150/sql-select-the-nearest-values-with-join

2条答案

按热度按时间

dbf7pr2w1#

使用MAX：）获取最大值...然后基于这两个值再次加入...

SELECT ref.score 
, subsel.max_val 
, subsel.type_id
FROM (
  SELECT res.score, max(ref.value) as max_val, ref.type_id
  FROM `refernce` ref
  JOIN `result` res
      ON ref.type_id = res.type_id
  WHERE res.score >= ref.score
  GROUP BY res.score, ref.type_id
) subsel
JOIN `refernce` ref
ON ref.type_id = subsel.type_id
AND ref.value = subsel.max_val 
WHERE 1=1
ORDER BY 3 desc;

字符串
如果你使用的是一些更复杂的数据库（Postgre，Oracle，...）
您可以使用窗口函数来分区和排序行..然后只在随后的筛选中选择您需要的行
示例（未测试）

SELECT score_ref
, value
, type_id
FROM (
  SELECT res.score as score_res
  , ref.score as score_ref
  , ref.value
  , ref.type_id
  , row_number() over (partition by ref.type_id order by ref.value desc) as row_order
  FROM `refernce` ref
  JOIN `result` res
      ON ref.type_id = res.type_id
  WHERE res.score >= ref.score
  GROUP BY ref.type_id
)
WHERE row_number = 1

型

赞(0）回复(0）举报 5个月前

2o7dmzc52#

只使用一个子查询的解决方案，来自链接reference和result数据表，其中score和value数据行会转换成固定长度的十六进制字串表示，然后加以组合，并套用max汇总函数，再将十六进制字串转换回整数。

select
  conv(substr(binstr, 1, 3), 16, 10) as score,
  conv(substr(binstr, -5), 16, 10) as `value`,
  type_id
from (
  select
    max(concat(
          lpad(conv(ref.score, 10, 16), 3, '0'),
          lpad(conv(ref.`value`, 10, 16), 5, '0')
    )) as binstr,
    ref.type_id
  from reference as ref
  join result as res
  on ref.type_id = res.type_id and
     ref.score <= res.score
  group by ref.type_id
) as t;

字符串
测试结果：

+-------+-------+---------+
| score | value | type_id |
+-------+-------+---------+
|     2 |    30 |       1 |
|     3 |    25 |       2 |
|     0 |     8 |       3 |
+-------+-------+---------+

型
SQL小操作。

赞(0）回复(0）举报 5个月前

我来回答

mysql SQL使用join选择最近的值

2条答案

相关问题

热门标签

最新问答