是否有一种方法可以为SQL中没有出现的订单创建行？

Question

此查询：

SELECT nettprice
  ,prodgroup
  ,numofitems
  ,duedate
  ,traderid
  ,duestring
FROM db.dbo.v_activesalesorder
WHERE prodgroup IN ( '123', '456', '789')
AND traderid = 'CUST01'

返回这些结果

​

​

无论订单是否到期，我需要在SQL查询中添加哪些内容才能为每个duedate的每个产品组生成一行？

例如，我希望W7 Y20看起来像这样：

​

​

Answer 1

交叉连接生成所有结果行。然后外部加入您现有的数据。

WITH orders AS
(
  SELECT *
  FROM db.dbo.v_activesalesorder
  WHERE prodgroup IN (123, 456, 789)
  AND traderid = 'CUST01'
)
SELECT
  o.nettprice,
  p.prodgroup,
  COALESCE(o.numofitems, 0) as num_of_items,
  d.duedate, 
  o.traderid
  o.duestring
FROM (SELECT DISTINCT prodgroup FROM orders) p
CROSS JOIN (SELECT DISTINCT duedate FROM orders) d
LEFT JOIN orders o ON o.prodgroup = p.prodgroup AND o.duedate = d.duedate
ORDER BY d.duedate, p.prodgroup;

Answer 2

您需要将活动顺序表与prodgroup表和某些日期表交叉连接。

Answer 3

您可以使用VALUE() & do LEFT JOIN：

SELECT COALESCE(v.nettprice, 0) AS nettprice, vv.prodgroup, COALESCE(V.numofitems, 0) AS numofitems, 
       vv.prodgroup, v.traderid, v.duestring
FROM ( VALUES (123, '2020-02-10'), 
              (456, '2020-02-10'), 
              (789, '2020-02-10') 
     ) vv(prodgroup, DueDate) LEFT JOIN
     [db].[dbo].[v_activesalesorder] v
     ON v.prodgroup = vv.prodgroup AND v.DueDate = vv.DueDate;