SQL Server 多表运算——联接查询实战

假设现在需要查询数据库中 Orders 表的所有订单， 要确保对于2006 年 1月1日到2008年12月31日之间的每个日期至少在输出结果中出现一行。对于在这个时间范围内有订单的日期， 不需要做其他特殊处理；但对于输出没有订单的日期， 则其订 单列用NULL作为占位符。

为了解决这个问题，可以先写一条查询语句返回要求的日期范围内的所有日期序列。再对这个日期集和Orders表执行一个左联接操作。为了生成指定范围的日期序列， 使用一个由数字组成的辅助表。 新建一个名为Nums 的表，其中包含一个名为 n 的列，并将这个列的值初始化为整数序列(1, 2, 3, …）。由数字组成的辅助表是一个功能非常强大的通用工具， 最终能帮助我们解决很多问题。 只需要在数据库中创建一次这样的表， 然后用足够数量的数字来填充这个表。 

SET NOCOUNT ON    
DECLARE @i AS INT =1;
BEGIN TRAN 
    WHILE @i <= 100000 
    BEGIN 
        INSERT INTO dbo.Nums VALUES(@i); 
        SET @i= @i + 1; 
    END
COMMIT TRAN
SET NOCOUNT OFF

作为解决方案的第一步，要生成指定范围内的所有日期序列。为此，可以先查询Nums表，筛选出与请求日期范围内的天数一样多个整数；再用DATED/FF函数计算与每个整 数对应的日期。通过在日期范围(2006年1月1号）的起始日期上加n-1天，就可以得到日期序列中实际的第n个日期。以下是解决方案的完整查询语句：

SELECT DATEADD(day, n-1,'20060101') AS orderdate 
FROM dbo.Nums 
WHERE n <= DATEDIFF(day,'20060101','20081231') + 1 
ORDER BY orderdate; 

接下来就是要扩展前面的查询语句，在Nums表和Orders表之间增加一个左外联接。联接条件是对用Nums表和表达式OATEADD(day,Nums.n-1,'20060101')生成的订单日期与Orders表中的orderdate列进行比较，如下所示：

SELECT DATEADD(day, Nums.n -1,'20060101') AS orderdate, 
O.orderid, O.custid, O.empid
FROM dbo.Nums 
LEFT OUTER JOIN Sales.Orders AS O
ON DATEADD(day, Nums.n -1,'20060101') = O.orderdate 
WHERE Nums.n <= DATEDIFF(day,'20060101','20081231') + 1 
ORDER BY orderdate; 

 

转载于:https://www.cnblogs.com/johnvwan/p/9463518.html