在完全外连接中按日期对两个单独的列进行排序

发布于 2024-12-11 01:21:36 字数 914 浏览 0 评论 0原文

我使用完全外连接排列了两列数据，但它包括两个单独的日期列，这使得排序变得困难。

表 1 包含产品的销售排名数据。表2为同一产品的实际销售数据。

每个表可能有其他表没有的日期条目。

因此，想象一下完全连接后，我们最终会得到类似这个简化示例的结果：

ProdID  L.Date         P.Date       Rank   Units
101     null           2011-10-01   null   740
101     2011-10-02     2011-10-02   23     652
101     2011-10-03     null         32     null

这是我用来提取此数据的查询：

select L.ListID, L.ASIN, L.date, L.ranking, P.ASIN, P.POSdate, P.units from ListItem L
full outer join POSdata P on 
    L.ASIN = P.ASIN and 
    L.date = P.POSdate and 
    (L.ListID = 1 OR L.ASIN is null)
where (L.ASIN = 'xxxxxxxxxx' and L.ListID = 1) or  
      (P.ASIN = 'xxxxxxxxxx' and L.BookID is null) 
order by POSdate, date

它有点复杂，因为产品可能会出现在多个列表中，所以我也必须考虑到这一点，但它返回我需要的数据。我愿意接受改进它的建议，当然如果有人有的话。

问题是，当两个日期列中可能至少有一些 NULL 时，如何正确排序。当两列都有一个 NULL 时，我现在的排序方式将不起作用。

谢谢。

原文

I have two columns of data I am lining up using a Full Outer Join but it includes two separate date columns which make it challenging to sort by.

Table 1 has sales rank data for a product.
Table 2 has actual sales data for the same product.

Each table may have entries for dates on which the other does not.

So envision after the full join, we end up with something like this simplified example:

ProdID  L.Date         P.Date       Rank   Units
101     null           2011-10-01   null   740
101     2011-10-02     2011-10-02   23     652
101     2011-10-03     null         32     null

Here is the query I am using to pull this data:

select L.ListID, L.ASIN, L.date, L.ranking, P.ASIN, P.POSdate, P.units from ListItem L
full outer join POSdata P on 
    L.ASIN = P.ASIN and 
    L.date = P.POSdate and 
    (L.ListID = 1 OR L.ASIN is null)
where (L.ASIN = 'xxxxxxxxxx' and L.ListID = 1) or  
      (P.ASIN = 'xxxxxxxxxx' and L.BookID is null) 
order by POSdate, date

It's a bit more complex because products may appear on multiple lists so I have to account for that as well, but it returns the data I need. I am open to suggestions on improving it of course should someone have one.

The problem is, how can I sort this properly when both date columns are likely to have at least some NULLs in them. The way I am Ordering By now will not work when both columns have at one NULL.

Thanks.

分享到QQ

分享到微博