仅当不存在具有非空值的同一行时才输出具有空值的行 [英] Output rows with null value only if there isn't the same row with a non-null value
问题描述
尽我所能地表述我的问题...
Trying to formulate my question as good as I can...
我有一个包含历史数据的定价表.因此，每件商品都有特定日期的价格.难点在于这些行还有一个类型(1 = 采购订单的价格，2 = 销售订单)和一个 VendorID.
I have a pricing table with historic data in it. So per item there is a price for certain dates. The difficulty is that the rows also have a type (1 = price for purchase order, 2 = sales order) and a VendorID.
该供应商 ID 可以填入一行:该行上的价格是针对该特定供应商的.如果该表中某个项目没有具有 VendorID 的行，但它确实有 VendorID 为 null 的行，则该行应该在结果中.
That VendorID can be filled on a row: the price on that row is then for that specific vendor. If there is no row for a certain item in this table that has a VendorID, but it does have a row where VendorID is null, that row should be in the result.
因此，如果结果中有两行，其中一行是 VendorID 值，另一行是 VendorID 为 null，则带有该值的行应该在结果集中，而带有 null 值的行可能不在结果集.
So, if there are two rows in the result, one with a VendorID value and one with the VendorID being null, the row with the value should be in the result set and the row with the null value may not be in the result set.
此外，结果集应仅包含最新的价格，因此我必须考虑FromDate".
Also, the result set should only contain the prices that are the newest, so i have to take in account the 'FromDate'.
'VendorID' 列的名称没有选择好，因为 type = 2 的行用于销售订单，但我们暂时忘记它;-)
The name of the column 'VendorID' is not well chosen because the rows with type = 2 are for sales orders, but let's forget about that for now ;-)
如果我想要 type = 1 的所有项目，希望有以下结果集:
If I want all items for type = 1, would like to have the following result set:
ID | ItemID | FromDate | Type | VendorID | price
------------------------------------------------
1 | 1. | 2020-01-01 | 1 | 97 | 2.45
9 | 2 | 2020-02-15 | 1 | 97 | 3.88
7 | 3 | 2020-01-01 | 1 | 97 | 2.55
假设 ID 3,4 和 9 不在表中(因此，特定 VendorID 97 的项目 2 没有定价)，结果应该是:
Suppose IDs 3,4 and 9 wheren't in the table (so, no pricing for item 2 for specific VendorID 97), the result should be:
ID | ItemID | FromDate | Type | VendorID | price
------------------------------------------------
1 | 1 | 2020-01-01 | 1 | 97 | 2.45
13 | 2 | 2020-01-01 | 1 | NULL | 999.45
7 | 3 | 2020-01-01 | 1 | 97 | 2.55
对于 ItemID 2，这意味着没有为 VendorID 97 设置特定价格，但有一个通用价格集(VendorID 为空)，现在应将此价格放入结果集中.
For ItemID 2 this would mean that there isn't a specific price set for VendorID 97 but there is a general price set (VendorID is null) and this price should now be placed in the result set.
我希望我现在解释得更清楚......
I hope I explained it more clearly now....
我现在已经写了很多查询，也用谷歌搜索了很多，但我找不到如何让它做我想要的.我尝试了不同的排序，但没有运气.一定很简单，但我找不到.
I've written loads of queries now and also googled a lot but I cannot find how to make it do what I want. I tried distinct, sorting, but no luck. Must be something simple but I can't find it.
到目前为止，我有以下 Mysql 查询，但是 a) 它输出 VendorID 为空的行和它有值的行 b) 我认为它非常复杂，但不知道如何使它更简单和更快.
Up until now I have the following Mysql query but a) it outputs both the rows where VendorID is null and where it has value and b) I think its very overcomplicated but can't figure out how to make it simpler and faster.
SELECT I.ItemID, I.Name, b.vendorID, b.max_date, IP.Price, T.Percentage
FROM Items I
JOIN ( SELECT ItemID, VendorID, MAX(FromDate) max_date, type
FROM ItemPrices
WHERE Type = 1 AND FromDate < '2020-02-30'
AND VendorID = (SELECT ID
FROM Vendors
WHERE VendorID = 'V001')
OR VendorID IS NULL
GROUP BY ItemID, VendorID
) b ON I.ID = b.ItemID
JOIN ItemPrices IP ON IP.ItemID = b.ItemID
AND IP.Type = b.type
AND IP.FromDate = b.max_date
AND (IP.VendorID = b.VendorID OR IP.VendorID IS NULL)
LEFT JOIN TaxCodes T ON T.ID =
( SELECT TC.TaxCodeID
FROM TaxCombinations TC
WHERE TC.Direction = 1
AND TC.TaxAreaID = (SELECT TaxArea
FROM Vendors
WHERE ID = (SELECT ID
FROM Vendors
WHERE VendorID = 'V001') )
AND TC.ItemTaxID = I.ItemTaxID )
ORDER BY I.ItemID ASC
也看了下面的网址，但还是不知道怎么办:具有非空值优先于空值的不同行如果存在则选择非空值，否则为空，但始终选择行
Also looked at the following urls but still don't know what to do: Distinct rows with non-null values taking precedence over nulls Select the non-null value if exists else null, but always select the row
有人可以帮我吗?
推荐答案
如果你想为每个 ItemID 对应的 Type 选择获得最新的，你可以使子查询首先返回最新价格，然后加入原始表以在最终输出中显示它.以下是示例查询:
If you want to get the newest for each ItemID corresponding to the Type selection, you can make a sub-query to return the newest price first then join the original table to show it in the end output. Below are the example query:
SELECT A.*
FROM ItemPrices A
JOIN ( SELECT itemid,
TYPE,
MAX(fromdate) AS mdt
FROM ItemPrices
GROUP BY itemid,TYPE ) B
ON A.itemid=B.itemid
AND A.type=B.type
AND A.fromdate=B.mdt
WHERE A.type=1
ORDER BY A.itemid;
您可以在此处查看演示:https://www.db-fiddle.com/f/7YCaiLYz9DE11wnijWEdi/3
You can view the demo here : https://www.db-fiddle.com/f/7YCaiLYz9DE11wnijWEdi/3
这篇关于仅当不存在具有非空值的同一行时才输出具有空值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
