我正在尝试查询以查找不在客户授权产品列表中的产品,这让我伤透了脑筋。
每个客户都有一个授权的产品组列表,在一个产品组中,每个条目可能包含一个产品 ID、一个产品类别 ID、供应商编号、价格表 ID 或品牌 ID(产品记录中的所有不同列)。
许多授权产品组列表混合了 2 种或更多种条目,并且通常在用作选择标准的不同列之间存在一些重叠。当然,产品组列表项上有一个代码表示该条目基于哪一列,因此很容易解析。
在产品方面,我会寻找没有“非活动”标志的产品,这些标志不在每个客户的授权列表中。
这些表大致如下所示:
顾客
客户ID | 授权列表ID |
---|---|
1个 | 345 |
2个 | 345 |
3个 | 117 |
授权清单
列表编号 | 列表名称 |
---|---|
117 | 冷冻品 |
345 | 比萨 |
授权清单项目
列表键 | 项目编号 | 类型 |
---|---|---|
117 | 68 | BRND |
117 | 202 | 物品 |
117 | 1415 | 物品 |
117 | 3012 | 物品 |
117 | 61152 | CATG |
117 | 31667 | 公共投资委员会 |
345 | 9615 | 物品 |
345 | 2100 | 物品 |
345 | 13229 | 越南国防军 |
345 | 87211 | CATG |
产品
产品编号 | 分类键 | 价格键 | 供应商密钥 | 品牌钥匙 | 无效 |
---|---|---|---|---|---|
202 | 61152 | 113 | 45529 | 16 | 0 |
1415 | 4990 | 5601 | 882 | 41009 | 0 |
2100 | 61152 | 37772 | 9101 | 20900 | 0 |
3557 | 87211 | 4101 | 17743 | 23777 | 0 |
4027 | 5193 | 644 | 882 | 68 | 0 |
4028 | 5193 | 580 | 882 | 68 | 0 |
9615 | 87211 | 1512 | 17743 | 8738 | 0 |
13017 | 87211 | 4040 | 13229 | 23795 | 0 |
42500 | 87211 | 20155 | 17743 | 4355 | 0 |
53529 | 1011 | 31667 | 13229 | 602 | 1个 |
虽然我有相当多的 SQL 经验,但在这一点上,它的复杂程度超出了我的范围……所以让我感到困惑的部分原因是我什至不知道如何简洁准确地描述我在做什么尝试这样做我可以搜索它。我什至不知道这个问题的合适标签。另外,前面忘了提:T-SQL for SQL Server 2016R2。
预期结果将是一组包含 CustomerID 和 ProductID 列的记录,其中产品不在客户的授权列表中并且 Inactive = 0。理想情况下,这将是一个视图。
我已经得到了这个查询:
SELECT C.*,
A.ItemRefNo,
A.Type,
CASE
WHEN CA.ProductId IS NOT NULL THEN CA.ProductId
WHEN B.ProductId IS NOT NULL THEN B.ProductId
WHEN V.ProductId IS NOT NULL THEN V.ProductId
WHEN P.ProductId IS NOT NULL THEN P.ProductId
WHEN A.Type = 'ITEM' THEN A.ItemRefNo
ELSE NULL
END
AS ProductId
FROM Customer C
INNER JOIN authorizedListItems A
ON C.AuthorizedListId = A.ListKey
LEFT JOIN products CA
ON A.ItemRefNo = CA.CategoryKey
AND A.Type = 'CATG'
LEFT JOIN products B
ON A.ItemRefNo = B.BrandKey
AND A.Type = 'BRND'
LEFT JOIN products V
ON A.ItemRefNo = V.VendorKey
AND A.Type = 'VNDR'
LEFT JOIN products P
ON A.ItemRefNo = P.PriceKey
AND A.Type = 'PRIC'
查询结果:
客户ID | 授权清单编号 | 项目编号 | 类型 | 产品编号 |
---|---|---|---|---|
3个 | 117 | 68 | BRND | 4027 |
3个 | 117 | 68 | BRND | 4028 |
3个 | 117 | 202 | 物品 | 202 |
3个 | 117 | 1415 | 物品 | 1415 |
3个 | 117 | 3012 | 物品 | 3012 |
3个 | 117 | 61152 | CATG | 202 |
3个 | 117 | 61152 | CATG | 2100 |
1个 | 345 | 9615 | 物品 | 9615 |
2个 | 345 | 9615 | 物品 | 9615 |
1个 | 345 | 2100 | 物品 | 2100 |
2个 | 345 | 2100 | 物品 | 2100 |
1个 | 345 | 13229 | 越南国防军 | 13017 |
1个 | 345 | 13229 | 越南国防军 | 53529 |
我想看到的结果是:
客户ID | 产品编号 |
---|---|
1个 | 202 |
1个 | 1415 |
1个 | 4027 |
1个 | 4028 |
2个 | 202 |
2个 | 1415 |
2个 | 4027 |
2个 | 4028 |
3个 | 3557 |
3个 | 9615 |
3个 | 13017 |
3个 | 42500 |
让我提前说一下,我知道我没有过滤查询中的非活动项目,结果包括多余的列。