考慮表 x
id,val
1,100
3,300
和表 y
id
1
2
3
對于每一行y我想val從x其中id從y等于或之前最接近id從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
uj5u.com熱心網友回復：
您可以嘗試根據 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
輸出：
| 編號 | 身份證號 | 值 |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 1 | 100 |
| 3 | 3 | 300 |
| 4 | 4 | 400 |
uj5u.com熱心網友回復：
你這樣使用我認為它的作業......
SELECT *, (
SELECT top 1 x.val
FROM x
WHERE x.id <= y.id
ORDER BY x.id DESC
) as closest_id
FROM y
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/346022.html
上一篇：Dynamo資料庫憑據管理
下一篇：使用hcl2創建ami時出現Packer錯誤：“查詢AMI時出錯：InvalidAMIID.Malformed：ID無效：”