英文:
Change Subquery to a type of JOIN
问题
过去几天，我一直在尝试将子查询转换为内连接，因为我需要将SQL代码迁移到BigQuery，而后者不支持子查询。
这是原始查询：
SELECT order_id_int, order_number_vc,(SELECT TOP 1 create_dt FROM lifecycle_tbl life WHERE life.recipient_package_id_fk_int = pak.recipient_package_id_int AND status_vc = 'Deleted' ORDER BY lifecycle_id_int DESC) AS package_archive_dt,FROM recipient_package_tbl pak
我已将其更改如下：
SELECT order_id_int, order_number_vcFROM recipient_package_tbl pakINNER JOIN lifecycle_tbl lifeON life.recipient_package_id_fk_int = pak.recipient_package_id_intWHERE life.status_vc = 'Deleted'
显然，使用 TOP 1 和 ORDER BY DESC，我只需要获取第一个值并将其添加到查询中（通过子查询）。然而，如果我直接在INNER JOIN中这样做，它会过滤整个查询。
如果有人知道如何正确进行更改，我将非常感激。
英文:
For the past few days, I have been trying to convert a subquery to an Inner Join since I need to migrate an SQL code to BigQuery, and the latter does not support subqueries.
This is the original query:
SELECT order_id_int, order_number_vc,(SELECT TOP 1 create_dt FROM lifecycle_tbl life WHERE life.recipient_package_id_fk_int = pak.recipient_package_id_int AND status_vc = 'Deleted' ORDER BY lifecycle_id_int DESC) AS package_archive_dt,FROM recipient_package_tbl pak
I have changed it like this:
SELECT order_id_int, order_number_vcFROM recipient_package_tbl pakINNER JOIN lifecycle_tbl lifeON life.recipient_package_id_fk_int = pak.recipient_package_id_intWHERE life.status_vc = 'Deleted'
Clearly, with a TOP 1 and an ORDER BY DESC, I need to only take the first value to add it to the query (through a subquery). However, if I do that directly in the INNER JOIN, it filters the entire query.
If somebody knows how to make the change correctly I will thank you so much.
答案1
得分: 0
以下是翻译好的部分：
"Could something like this work for you? I used ROW_NUMBER, and broke it out into two steps for clarity.
with subquery as (
SELECT life.recipient_package_id_fk_int, create_dt, row_number() over(partition by life.recipient_package_id_fk_int ORDER BY lifecycle_id_int DESC) as rn
FROM lifecycle_tbl life
WHERE status_vc = 'Deleted'
), subquery_fitlered as (
SELECT recipient_package_id_fk_int, create_dt as package_archive_dt
FROM subquery
WHERE rn = 1
)
SELECT order_id_int, order_number_vc,
package_archive_dt
FROM recipient_package_tbl pak
LEFT JOIN subquery_fitlered
ON subquery_fitlered.recipient_package_id_fk_int = pak.recipient_package_id_int
英文:
Could something like this work for you? I used ROW_NUMBER, and broke it out into two steps for clarity.
with subquery as (SELECT life.recipient_package_id_fk_int, create_dt, row_number() over(partition by life.recipient_package_id_fk_int ORDER BY lifecycle_id_int DESC) as rnFROM lifecycle_tbl lifeWHERE status_vc = 'Deleted'), subquery_fitlered as (SELECT recipient_package_id_fk_int, create_dt as package_archive_dtFROM subqueryWHERE rn = 1)SELECT order_id_int, order_number_vc,package_archive_dtFROM recipient_package_tbl pakLEFT JOIN subquery_fitleredON subquery_fitlered.recipient_package_id_fk_int = pak.recipient_package_id_int
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论