我可以在 SQL 的 IF 函数中嵌套 select 语句吗？

发布于 2025-01-18 06:21:21 字数 650 浏览 3 评论 0原文

使用 Teradata.. 我想编写一个查询，将项目代码上的表 1 和表 2 连接到表 2 中的位置。每个项目代码有多个位置，并且每个位置可能有多个项目代码条目，具体取决于日期。我只对每个位置的最新项目感兴趣。为了实现此目的，我使用嵌套查询来选择每个位置和项目编号的最大日期。我仍然返回比预期更多的数据行，并怀疑这是由于一些重复的位置漏掉了，可能有两个不同的项目编号。

我想知道是否可以使用 IF 运算符来表示“如果有重复的位置，请选择日期较新的位置” 这可能吗？

这是我到目前为止所写的内容：

SELECT t1.item_no, t1.date, t2.location, t2.date
FROM table 1 t1
JOIN table 2 t2 ON t1.item_no = t2.item_no
WHERE (t1.item_no, t1.date) IN
    ( 
     SELECT item_no, MAX(date)
     FROM table 1
     GROUP BY item_no
    )
AND (t2.location, t2.date) IN
    (
     SELECT location, MAX(date)
     FROM table 2
     GROUP BY location
    )

原文

Using Teradata..
I want to write a query that joins table 1 and table 2 on item code to the location in table 2.
There are multiple locations per item code and potentially multiple item code entries per location depending on date. I'm only interested in the most recent item per location. To achieve this I've used a nested query to select the max date per both location and item number. I'm still returning more rows of data than anticipated and suspect it is due to some duplicate locations slipping through, potentially with two different item numbers.

I'm wondering if its possible to use the IF operator to say "If there are duplicate locations, choose the location with the more recent date"
Is this possible?

Here is what I have written so far:

SELECT t1.item_no, t1.date, t2.location, t2.date
FROM table 1 t1
JOIN table 2 t2 ON t1.item_no = t2.item_no
WHERE (t1.item_no, t1.date) IN
    ( 
     SELECT item_no, MAX(date)
     FROM table 1
     GROUP BY item_no
    )
AND (t2.location, t2.date) IN
    (
     SELECT location, MAX(date)
     FROM table 2
     GROUP BY location
    )

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

美羊羊 2025-01-25 06:21:21

更改您的查询并使用子查询

SELECT t1.item_no, t1.date, t2.location, t2.date FROM 
    ( 
     SELECT item_no, MAX(date) date
     FROM table 1
     GROUP BY item_no
    ) T1
    JOIN
      (
     SELECT location, MAX(date) date
     FROM table 2
     GROUP BY location
    ) T2
    ON t1.item_no = t2.location

Change your query and use Subquery

SELECT t1.item_no, t1.date, t2.location, t2.date FROM 
    ( 
     SELECT item_no, MAX(date) date
     FROM table 1
     GROUP BY item_no
    ) T1
    JOIN
      (
     SELECT location, MAX(date) date
     FROM table 2
     GROUP BY location
    ) T2
    ON t1.item_no = t2.location

回复收藏 0 原文

贪了杯 2025-01-25 06:21:21

在不知道DBM的情况下，解决方案可以是使用row_number（）。我不确定是否偏爱嵌套查询，而不是CTE，但是可以使用CTE的解决方案是：

WITH items AS (
    SELECT
        item_no
        ,date AS item_date
        ,row_number() OVER (PARTITION BY item_no ORDER BY date desc) as rn  
    FROM table1
),

locations AS (
    SELECT
        location
        ,item_no
        ,date AS location_date
        ,ROW_NUMBER() OVER(PARTITION BY item_no, location ORDER BY date desc) as rn
    from table2
)

SELECT
    t1.item_no
    ,t1.item_date
    ,t2.location
    ,t2.location_date
FROM items AS t1
JOIN locations AS t2 on t1.item_no = t2.item_no
    AND t2.rn = 1
WHERE t1.rn = 1

Without knowing DBMS, a solution could be to use ROW_NUMBER(). I'm not sure if there's a preference for nested queries over say CTE but a solution w/ CTE could be:

WITH items AS (
    SELECT
        item_no
        ,date AS item_date
        ,row_number() OVER (PARTITION BY item_no ORDER BY date desc) as rn  
    FROM table1
),

locations AS (
    SELECT
        location
        ,item_no
        ,date AS location_date
        ,ROW_NUMBER() OVER(PARTITION BY item_no, location ORDER BY date desc) as rn
    from table2
)

SELECT
    t1.item_no
    ,t1.item_date
    ,t2.location
    ,t2.location_date
FROM items AS t1
JOIN locations AS t2 on t1.item_no = t2.item_no
    AND t2.rn = 1
WHERE t1.rn = 1

回复收藏 0 原文

~没有更多了~