通过表A和表B的数据，得出最后一个表的数据。
那2行0的数据也要显示
------解决方案--------------------
select a.*,isnull(b.amount,0) as amount from (select * from a cross join (select disintct date from b) as b) as a left join b on a.date=b.date
------解决方案--------------------
排下序
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-20 15:05:24
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[OrderNum] varchar(4),[Target] int,[Product] varchar(1))
insert [A]
select 1,'N001',100,'A' union all
select 2,'N001',80,'B'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[PID] int,[Date] VARCHAR(10),[Amount] int)
insert [B]
select 1,1,'2014.5.1',60 union all
select 2,1,'2014.5.2',40 union all
select 3,2,'2014.5.2',40 union all
select 4,2,'2014.5.3',40
--------------开始查询--------------------------
SELECT DISTINCT ISNULL(b.date,a.date)date ,
a.ordernum ,
a.TARGET ,
ISNULL(b.amount,0) AS amount ,
a.product
FROM ( SELECT *
FROM a
CROSS JOIN ( SELECT DISTINCT
date
FROM b
) AS c
) AS a
left JOIN b ON a.id=b.pid AND a.date=b.date
ORDER BY date,product,amount
----------------结果----------------------------
/*
date ordernum TARGET amount product
---------- -------- ----------- ----------- -------
2014.5.1 N001 100 60 A
2014.5.1 N001 80 0 B
2014.5.2 N001 100 40 A
2014.5.2 N001 80 40 B
2014.5.3 N001 100 0 A
2014.5.3 N001 80 40 B
*/