求一个SQL,我的写法不对。

Question

表A
------
ID
1266
1267
1268
1269
表B
------
rowid              ID       a         b         c        d
100001720100302 NULL NULL NULL NULL NULL
100001720100315 1266 8.0 0.0 0.0 0.0
100001720100315 1269 3.0 0.0 0.0 0.0
求表C
-----------------
1266 100001720100302 NULL NULL NULL NULL NULL
1267 100001720100302 NULL NULL NULL NULL NULL
1268 100001720100302 NULL NULL NULL NULL NULL
1269 100001720100302 NULL NULL NULL NULL NULL
1266 100001720100315 1266 8.0 0.0 0.0 0.0
1267 100001720100315 NULL NULL NULL NULL NULL
1268 100001720100315 NULL NULL NULL NULL NULL
1269 100001720100315 1269 3.0 0.0 0.0 0.0select * from 表A left join 表B on 表A.id=表B.ID
这么写不对？？

Answer 1

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-04-15 15:40:53
-- Version:
--      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
-- Oct 14 2005 00:33:37 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#a
if object_id('tempdb.dbo.#a') is not null drop table #a
go 
create table #a([ID] int)
insert #a
select 1266 union all
select 1267 union all
select 1268 union all
select 1269
--> 测试数据：#B
if object_id('tempdb.dbo.#B') is not null drop table #B
go 
create table #B([rowid] bigint,[ID] int,[a] numeric(2,1),[b] numeric(2,1),[c] numeric(2,1),[d] numeric(2,1))
insert #B
select 100001720100302,null,null,null,null,null union all
select 100001720100315,1266,8.0,0.0,0.0,0.0 union all
select 100001720100315,1269,3.0,0.0,0.0,0.0
--------------开始查询--------------------------
select * from 
(
select a.id,b.rowid,b.a,b.b,b.c,b.d ,1 flag from #a a left join #B b on a.id=b.id and b.id is not null
union all
select a.id,b.rowid,b.a,b.b,b.c,b.d ,0  from #a a cross join #B b where b.id is null
) t
order by flag----------------结果----------------------------
/*(4 行受影响)(3 行受影响)
id          rowid                a                                       b                                       c                                       d                                       flag
----------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
1266        100001720100302      NULL                                    NULL                                    NULL                                    NULL                                    0
1267        100001720100302      NULL                                    NULL                                    NULL                                    NULL                                    0
1268        100001720100302      NULL                                    NULL                                    NULL                                    NULL                                    0
1269        100001720100302      NULL                                    NULL                                    NULL                                    NULL                                    0
1266        100001720100315      8.0                                     0.0                                     0.0                                     0.0                                     1
1267        NULL                 NULL                                    NULL                                    NULL                                    NULL                                    1
1268        NULL                 NULL                                    NULL                                    NULL                                    NULL                                    1
1269        100001720100315      3.0                                     0.0                                     0.0                                     0.0                                     1(8 行受影响) 
*/

Answer 2

select * 
from 表A , 表B
where 表A.id=表B.ID or B.ID is null

Answer 3

select a.*,b.a,b.b,b.c,b.d
from
(select a.id,b.rowid from a,b) a
left b
on a.id=b.id

Answer 4

select a.*,b.a,b.b,b.c,b.d
from
(select disinct a.id,b.rowid from a,b) a
left b
on a.id=b.id
去掉重复的

Answer 5

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-15 15:17:54
-- Verstion:
--      Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 
-- May 26 2009 14:24:20 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int)
insert [A]
select 1266 union all
select 1267 union all
select 1268 union all
select 1269
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([rowid] bigint,[ID] int,[a] numeric(2,1),[b] numeric(2,1),[c] numeric(2,1),[d] numeric(2,1))
insert [B]
select 100001720100302,null,null,null,null,null union all
select 100001720100315,1266,8.0,0.0,0.0,0.0 union all
select 100001720100315,1269,3.0,0.0,0.0,0.0
--------------开始查询--------------------------
select
 a.*,b.a,b.b,b.c,b.d 
from 
(select distinct a.id,b.rowid from a cross join b)a 
outer apply
 (select * from b where id=a.id)b
----------------结果----------------------------
/* id          rowid                a                                       b                                       c                                       d
----------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1266        100001720100302      8.0                                     0.0                                     0.0                                     0.0
1266        100001720100315      8.0                                     0.0                                     0.0                                     0.0
1267        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1267        100001720100315      NULL                                    NULL                                    NULL                                    NULL
1268        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1268        100001720100315      NULL                                    NULL                                    NULL                                    NULL
1269        100001720100302      3.0                                     0.0                                     0.0                                     0.0
1269        100001720100315      3.0                                     0.0                                     0.0                                     0.0(8 行受影响)*/

Answer 6

if object_id('tempdb.dbo.#a') is not null drop table #a
go 
create table #a([ID] int)
insert #a
select 1266 union all
select 1267 union all
select 1268 union all
select 1269
--> 测试数据：#B
if object_id('tempdb.dbo.#B') is not null drop table #B
go 
create table #B([rowid] bigint,[ID] int,[a] numeric(2,1),[b] numeric(2,1),[c] numeric(2,1),[d] numeric(2,1))
insert #B
select 100001720100302,null,null,null,null,null union all
select 100001720100315,1266,8.0,0.0,0.0,0.0 union all
select 100001720100315,1269,3.0,0.0,0.0,0.0
select a.*,b.a,b.b,b.c,b.d
from
(
select distinct #A.id,#B.rowid from #A,#B
) A
left join
#B b
on a.id=b.idid          rowid                a                                       b                                       c                                       d
----------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1266        100001720100302      8.0                                     0.0                                     0.0                                     0.0
1266        100001720100315      8.0                                     0.0                                     0.0                                     0.0
1267        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1267        100001720100315      NULL                                    NULL                                    NULL                                    NULL
1268        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1268        100001720100315      NULL                                    NULL                                    NULL                                    NULL
1269        100001720100302      3.0                                     0.0                                     0.0                                     0.0
1269        100001720100315      3.0                                     0.0                                     0.0                                     0.0(8 行受影响)

Answer 7

to 2楼的也不对啊。1267        NULL                 NULL                                    NULL                                    NULL                                    NULL                                    1
1268        NULL                 NULL                                    NULL    这两条记录没有newid啊。

Answer 8

借一下2楼那位大侠的数据
if object_id('tempdb.dbo.#a') is not null drop table #a
go 
create table #a([ID] int)
insert #a
select 1266 union all
select 1267 union all
select 1268 union all
select 1269
--> 测试数据：#B
if object_id('tempdb.dbo.#B') is not null drop table #B
go 
create table #B([rowid] bigint,[ID] int,[a] numeric(2,1),[b] numeric(2,1),[c] numeric(2,1),[d] numeric(2,1))
insert #B
select 100001720100302,null,null,null,null,null union all
select 100001720100315,1266,8.0,0.0,0.0,0.0 union all
select 100001720100315,1269,3.0,0.0,0.0,0.0
select a.*,b.id,b.a,b.b,b.c,b.d
from
(select distinct a.id,b.rowid from #a a,#b b) a
left join #b b
on a.id=b.id and a.rowid=b.rowid
order by a.rowid/**
id          rowid                id          a    b    c    d    
----------- -------------------- ----------- ---- ---- ---- ---- 
1266        100001720100302      NULL        NULL NULL NULL NULL
1267        100001720100302      NULL        NULL NULL NULL NULL
1268        100001720100302      NULL        NULL NULL NULL NULL
1269        100001720100302      NULL        NULL NULL NULL NULL
1266        100001720100315      1266        8.0  .0   .0   .0
1267        100001720100315      NULL        NULL NULL NULL NULL
1268        100001720100315      NULL        NULL NULL NULL NULL
1269        100001720100315      1269        3.0  .0   .0   .0（所影响的行数为 8 行）
**/

Answer 9

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-04-15 15:40:53
-- Version:
--      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
-- Oct 14 2005 00:33:37 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#a
if object_id('tempdb.dbo.#a') is not null drop table #a
go 
create table #a([ID] int)
insert #a
select 1266 union all
select 1267 union all
select 1268 union all
select 1269
--> 测试数据：#B
if object_id('tempdb.dbo.#B') is not null drop table #B
go 
create table #B([rowid] bigint,[ID] int,[a] numeric(2,1),[b] numeric(2,1),[c] numeric(2,1),[d] numeric(2,1))
insert #B
select 100001720100302,null,null,null,null,null union all
select 100001720100315,1266,8.0,0.0,0.0,0.0 union all
select 100001720100315,1269,3.0,0.0,0.0,0.0
--------------开始查询--------------------------
--select * from 
--(
--select a.id,b.rowid,b.a,b.b,b.c,b.d ,1 flag from #a a left join #B b on a.id=b.id and b.id is not null
--union all
--select a.id,b.rowid,b.a,b.b,b.c,b.d ,0  from #a a cross join #B b where b.id is null
--) t
--order by flag
select  
distinct a.id,b.rowid,b.a,b.b,b.c,b.d 
 from 
(
select distinct a.id,b.rowid from #a a cross join #B b 
)a left join #b b on (a.rowid=b.rowid and a.id=b.id) 
or(a.rowid=b.rowid and b.id is null)
----------------结果----------------------------
/*
 
(4 行受影响)(3 行受影响)
id          rowid                a                                       b                                       c                                       d
----------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1266        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1266        100001720100315      8.0                                     0.0                                     0.0                                     0.0
1267        NULL                 NULL                                    NULL                                    NULL                                    NULL
1267        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1268        NULL                 NULL                                    NULL                                    NULL                                    NULL
1268        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1269        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1269        100001720100315      3.0                                     0.0                                     0.0                                     0.0(8 行受影响)
*/

Answer 10

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-04-15 15:40:53
-- Version:
--      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
-- Oct 14 2005 00:33:37 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#a
if object_id('tempdb.dbo.#a') is not null drop table #a
go 
create table #a([ID] int)
insert #a
select 1266 union all
select 1267 union all
select 1268 union all
select 1269
--> 测试数据：#B
if object_id('tempdb.dbo.#B') is not null drop table #B
go 
create table #B([rowid] bigint,[ID] int,[a] numeric(2,1),[b] numeric(2,1),[c] numeric(2,1),[d] numeric(2,1))
insert #B
select 100001720100302,null,null,null,null,null union all
select 100001720100315,1266,8.0,0.0,0.0,0.0 union all
select 100001720100315,1269,3.0,0.0,0.0,0.0
--------------开始查询--------------------------
--select * from 
--(
--select a.id,b.rowid,b.a,b.b,b.c,b.d ,1 flag from #a a left join #B b on a.id=b.id and b.id is not null
--union all
--select a.id,b.rowid,b.a,b.b,b.c,b.d ,0  from #a a cross join #B b where b.id is null
--) t
--order by flag
select  
distinct a.id,a.rowid,b.a,b.b,b.c,b.d 
 from 
(
select distinct a.id,b.rowid from #a a cross join #B b 
)a left join #b b on (a.rowid=b.rowid and a.id=b.id) 
or(a.rowid=b.rowid and b.id is null)
----------------结果----------------------------
/*
(4 行受影响)(3 行受影响)
id          rowid                a                                       b                                       c                                       d
----------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1266        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1266        100001720100315      8.0                                     0.0                                     0.0                                     0.0
1267        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1267        100001720100315      NULL                                    NULL                                    NULL                                    NULL
1268        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1268        100001720100315      NULL                                    NULL                                    NULL                                    NULL
1269        100001720100302      NULL                                    NULL                                    NULL                                    NULL
1269        100001720100315      3.0                                     0.0                                     0.0                                     0.0(8 行受影响)
*/我晕,弄错个列名,

Answer 11

--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
--       QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0    C Language 
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server  2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.A') is not null drop table A
-- 创建数据表
create table A
(
ID int
)
go
--插入测试数据
insert into A select 1266
union all select 1267
union all select 1268
union all select 1269
go
if object_id('test.dbo.B') is not null drop table B
-- 创建数据表
create table B
(
rowid char(25),
ID int,
a float,
b float,
c float,
d float
)
go
--插入测试数据
insert into B select 100001720100302,NULL,NULL,NULL,NULL,NULL
union all select 100001720100315,1266,8.0,0.0,0.0,0.0
union all select 100001720100315,1269,3.0,0.0,0.0,0.0
go
--代码实现
select distinct a.ID IDa,a.rowid,b.ID IDb,b.a,b.b,b.c,b.d 
from (select * from A cross join (select distinct rowid from B)t)a
left join B b on (a.ID=b.ID and a.rowid=b.rowid) or(a.rowid=b.rowid and b.ID is null)
order by a.rowid/*结果
IDa  rowid           IDb  a    b    c    d
----------------------------------------------------------
1266 100001720100302 NULL NULL NULL NULL NULL
1267 100001720100302 NULL NULL NULL NULL NULL
1268 100001720100302 NULL NULL NULL NULL NULL
1269 100001720100302 NULL NULL NULL NULL NULL
1266 100001720100315 1266 8.0 0.0 0.0 0.0
1267 100001720100315 NULL NULL NULL NULL NULL
1268 100001720100315 NULL NULL NULL NULL NULL
1269 100001720100315 1269 3.0 0.0 0.0 0.0(8 行受影响)
*/