[SQL] Cross连接

USE tempdb
go
CREATE TABLE Product(ID int,
                      ProductName varchar(100),
                      Cost money);
CREATE TABLE SalesItem(ID int,
                        SalesDate datetime,
                        ProductID int,
                        Qty int,
                        TotalSalesAmt money);
INSERT INTO Product
    VALUES (1,'Widget',21.99),
          (2,'Thingamajig',5.38),
          (3,'Watchamacallit',1.96);
INSERT INTO SalesItem
    VALUES (1,'2014-10-1',1,1,21.99),
          (2,'2014-10-2',3,1,1.96),
          (3,'2014-10-3',3,10,19.60),
          (4,'2014-10-3',1,2,43.98),
           (5,'2014-10-3',1,2,43.98);

应用:  表1每一行 X 表2每一行

with step1 as
(
--产品+每个销售日期①
select P.ID,P.ProductName,P.Cost
      ,S.SalesDate
  from Product P
       CROSS JOIN
       (SELECT SalesDate FROM SalesItem group by SalesDate) S  --!!!!!  distinct后可以减小规模. cross join 使用时要注意笛卡尔积的数据量!
)
SELECT S1.SalesDate, S1.ProductName
     , ISNULL(Sum(S2.Qty),0) AS TotalQty
     , ISNULL(SUM(S2.TotalSalesAmt),0) AS TotalSales
  from step1 S1
       LEFT JOIN 
       SalesItem S2 --关联销售结果②
       ON S1.ID = S2.ProductID AND S1.SalesDate = S2.SalesDate
GROUP BY S1.SalesDate, S1.ProductName
ORDER BY S1.SalesDate;

select * from Product

select * from SalesItem

 

① ★ 

②

 

↓   [result] 本来汇总起来没有值(为0)不显示的数据也显示出来