27,579
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID('t2') IS NOT NULL DROP TABLE t2
GO
CREATE TABLE t1(
d VARCHAR(10)
)
SET NOCOUNT ON;
INSERT INTO t1 VALUES ('2018-1')
INSERT INTO t1 VALUES ('2018-2')
INSERT INTO t1 VALUES ('2018-3')
GO
CREATE TABLE t2(
pName NVARCHAR(10),
saleDate VARCHAR(10),
saleNum INT,
returnNum INT
)
SET NOCOUNT ON;
INSERT INTO t2 VALUES ('产品1','2018-1','100',1)
INSERT INTO t2 VALUES ('产品2','2018-2','200',2)
INSERT INTO t2 VALUES ('产品2','2018-1','20',1)
GO
SELECT
tt.pName [产品]
,t1.d AS [销售日期]
,isnull(t2.saleNum,0) AS [销售数量]
,isnull(t2.returnNum,0) AS [返修数量]
FROM t1 CROSS JOIN
(select pName from t2 GROUP BY t2.pName) AS tt
LEFT JOIN t2 ON tt.pName=t2.pName AND t1.d=t2.saleDate
/*
产品 销售日期 销售数量 返修数量
---------- ---------- ----------- -----------
产品1 2018-1 100 1
产品1 2018-2 0 0
产品1 2018-3 0 0
产品2 2018-1 20 1
产品2 2018-2 200 2
产品2 2018-3 0 0
*/