日期:2014-05-16 浏览次数:20685 次
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-02-13 09:01:03
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([ID] int)
insert [T1]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([ID] int,[num] int,[customer] varchar(1))
insert [T2]
select 1,100,'A' union all
select 2,200,'B' union all
select 4,300,'A'
--------------开始查询--------------------------
select a.id,ISNULL(num,0)num ,c.[customer]
from [T1] a LEFT JOIN [t2] b ON a.id=b.id
CROSS JOIN (SELECT DISTINCT [customer] FROM t2)c
ORDER BY c.[customer] ,id
----------------结果----------------------------
/*
id num customer
----------- ----------- --------
1 100 A
2 200 A
3 0 A