考虑表x
id,val
1,100
3,300
和表y
id
1
2
3
对于每一行y我想要val from x哪里的id从 y 等于或最接近之前id from x像那样：
id,val
1,100
2,100
3,300
我试图找到与相关子查询最接近的 id：
WITH
x AS (SELECT * FROM (VALUES (1, 100),(3, 300)) AS t(id, val)),
y AS (SELECT * FROM (VALUES 1,2,3) AS t(id))
SELECT *, (
SELECT x.id
FROM x
WHERE x.id <= y.id
ORDER BY x.id DESC
LIMIT 1
) as closest_id
FROM y
但我得到
SYNTAX_ERROR: line 5:5: Given correlated subquery is not supported
我也尝试过左连接：
SELECT *
FROM y
LEFT JOIN x ON x.id <= (
SELECT MAX(xbis.id) FROM x AS xbis WHERE xbis.id <= y.id
)
但我得到了错误
SYNTAX_ERROR: line 7:5: Correlated subquery in given context is not supported
您可以尝试基于 less then 条件加入，然后对结果进行分组并从分组中找到所需的数据：
WITH
x AS (SELECT * FROM (VALUES (1, 100),(3, 300),(4, 400)) AS t(id, val)),
y AS (SELECT * FROM (VALUES 1,2,3,4) AS t(id))
SELECT y.id as yId,
max(x.id) as xId,
max_by(x.val, x.id) as val
FROM y
JOIN x on x.id <= y.id
GROUP BY y.id
ORDER BY y.id
Output:
| yId |
xId |
val |
| 1 |
1 |
100 |
| 2 |
1 |
100 |
| 3 |
3 |
300 |
| 4 |
4 |
400 |
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
