gpt4 book ai didi

sql-server - T-SQL Cross Join 获取缺失值

转载 作者:行者123 更新时间:2023-12-03 02:32:51 25 4
gpt4 key购买 nike

这是我的问题的一个简单示例。我有一个创建报告的存储过程。

DECLARE @Report TABLE
(Product VARCHAR(10),
Purchases MONEY default (0)
)
DECLARE @Range TABLE
(minP MONEY,
maxP MONEY,
Descrip VARCHAR(50)
)

INSERT @Range
VALUES(0,1,'0-1'),
(2,5,'2-5'),
(6,10,'6-10')

INSERT @Report(Product, Purchases)
VALUES('A',1),
('A',5),
('B',10)


SELECT r.Product, r.Purchases, x.Descrip
FROM @Report r CROSS JOIN @Range x
WHERE r.purchases BETWEEN x.minp AND x.maxp

结果如下所示:

Product Purchases   Descrip
A 1.00 0-1
A 5.00 2-5
B 10.00 6-10

如何才能得到如下所示的结果:

Product Purchases   Descrip
A 1.00 0-1
A 5.00 2-5
A 0 6-10
B 0 0-1
B 0 2-5
B 10.00 6-10

最佳答案

这应该可以做到:

SELECT B.Product, ISNULL(C.Purchases,0) Purchases, A.Descrip
FROM @Range A
CROSS JOIN (SELECT DISTINCT Product
FROM @Report) B
LEFT JOIN @Report C
ON B.Product = C.Product
AND C.Purchases BETWEEN A.minP AND A.maxP
ORDER BY B.Product, Purchases

Here is a demo供您尝试。

关于sql-server - T-SQL Cross Join 获取缺失值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14163312/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com