Sql 查询 笛卡尔积

收藏 SQL查询cross join 的用法(笛卡尔积)

 

select a.Dealer, a.ProductCategoryI, a.CalendarYear, ('Q' + cast(a.CalendarQuarter as nvarchar(4))) as CalendarQuarter, sum(b.AmountUSD)as AmountUSD from
(select 
    e.Dealer,
    b.ProductCategoryI,
    b.CalendarYear,
    d.CalendarQuarter
from DimDate a full join
    (select * from (select distinct ProductCategoryI  from FactBooking)a, (select distinct CalendarYear from DimDate)b) b
on
    a.CalendarYear=b.CalendarYear
    full join
    (select * from (select distinct dealer from FactBooking)a, (select distinct ProductCategoryI from FactBooking)b) e
on
    b.ProductCategoryI=e.ProductCategoryI
    FULL join
    (select * from (select distinct CalendarQuarter from DimDate) a, (select distinct CalendarYear from dimdate)b) d
on
    d.CalendarYear=b.CalendarYear
    where Dealer is not null
group by e.Dealer,b.ProductCategoryI,b.CalendarYear, d.CalendarQuarter)a
    left join 
    (select a.*, b.CalendarQuarter, b.CalendarYear 
    from FactBooking a left join DimDate b on a.DateKey=b.DateKey)as b
    on a.CalendarQuarter=b.CalendarQuarter and a.CalendarYear=b.CalendarYear and a.Dealer=b.Dealer and a.ProductCategoryI=b.ProductCategoryI
where a.CalendarYear in (2013,2014) and a.CalendarQuarter <= 2 
group by a.Dealer,a.CalendarQuarter,a.CalendarYear,a.ProductCategoryI
order by Dealer,ProductCategoryI,calendaryear,CalendarQuarter