问SQL -棘手的合并
EN

Stack Overflow用户

提问于 2019-10-22 08:44:48

回答 3查看 56关注 0票数 0

我面临着一个棘手的问题要做。我希望你的专业知识能帮助我解决这个问题。

有两个表：

Table1 :订单

Index                 ProductName             OrderDate
0                      a                      03/03/1903
1                      a                      10/03/2014
2                      b                      01/01/2017
3                      c                      01/01/2019

Table2 :产品规格-->此表显示了我们产品的所有颜色变化

Index         ProductName                    Color   ColorUpdatedOn
0                      a                     Blue     01/01/1900
1                      a                     Red      01/01/2014
2                      a                     Yellow   01/01/2017
3                      b                     Pink     01/01/2017
4                      c                     Black    01/01/2018
5                      c                     Black    31/12/2018

我希望能够使用Color et UpdatedOn列检索Table1中的所有数据

Index   ProductName    OrderDate          Color   ColorUpdatedOn
0        a           03/03/1903           Blue     01/01/1900
1        a           10/03/2014           Red      01/01/2014
2        a           01/01/2019           Yellow   01/01/2017    
3        c           01/01/2019           Black    31/12/2018

你知道我是怎么做到的吗？

预先感谢您的帮助

拉戈

sql

merge

CDN&音视频通信出海专场

提供游戏出海、社交娱乐等方案，助力企业抢占出海市场

回答 3

Stack Overflow用户

发布于 2019-10-22 08:57:38

根据颜色获取Product Specs表的max()日期，然后使用year()函数进行join，适用于mysql和mssql，其他数据库不确定。

select o.Index, o.ProdcutName, o.Date, t1.color, t1.ColorUpdatedOn
from Orders o
inner join
    (select color, max(colorupdatedon) as ColorUpdatedOn 
    from productspecs
    group by color) t1 on year(o.OrderDate) = year(t1.createdon)

但我更喜欢使用right()函数，因为您的年份日期在末尾。

select o.Index, o.ProdcutName, o.Date, t1.color, t1.ColorUpdatedOn
    from Orders o
    inner join
        (select color, max(colorupdatedon) as ColorUpdatedOn 
        from productspecs
        group by color) t1 on right(o.OrderDate, 4) = right(t1.createdon, 4)

票数 0

Stack Overflow用户

发布于 2019-10-22 11:17:06

假设OrderDate和ColorUpdatedOn的数据类型都是date，我们可以找到订购时的颜色。为此，我使用了分析/窗口函数。Hive查询将如下所示：

SELECT 
    y.ProductName, y.OrderDate, y.Color, y.ColorUpdatedOn
FROM (
    SELECT 
        x.*,
        DENSE_RANK() OVER(PARTITION BY x.ProductName, x.OrderDate ORDER BY x.recency ASC) AS relevance
    FROM (
        SELECT 
            a.*, b.color, b.ColorUpdatedOn, DATEDIFF(a.OrderDate, b.ColorUpdatedOn) AS recency
        FROM 
            Order a 
        INNER JOIN 
            Product b 
        ON (
            a.ProductName = b.ProductName
            AND a.OrderDate >= b.ColorUpdatedOn
        )
    ) x 
) y
WHERE 
    y.relevance = 1;

如果您让我知道您正在使用的数据库，查询可以是特定的。如果有帮助，请告诉我。

票数 0

Stack Overflow用户

发布于 2019-10-22 11:35:10

在支持横向联接的数据库中(现在已经有相当多的横向联接)，这非常简单：

select o.*, s.*  -- select the columns you want
from orders o left join lateral
     (select s.*
      from specs s
      where s.ProductName = o.ProductName and
            s.ColorUpdatedOn <= o.OrderDate 
      order by s.ColorUpdatedOn desc
      fetch first 1 row only
     ) s
     on 1=1;

在SQL Server中，这将使用outer apply而不是left join lateral。

在其他数据库中，我将使用lead()

select o.*, s.*  -- select the columns you want
from orders o left join
     (select s.*,
             lead(ColorUpdatedOn) over (partition by ProductName order by ColorUpdatedOn) as next_ColorUpdatedOn
      from specs s
     ) s
     on s.ProductName = o.ProductName and
        o.OrderDate >= s.ColorUpdatedOn and
        (o.OrderDate < s.next_ColorUpdatedOn or s.next_ColorUpdatedOn is null)