如下两表所示:
表一:
主件 子件
000A 001
000A 002
000B 001
000B 003表二:
料号 料名 识别号
000A 123 XX1
000B 321 XX2
001 456
002 567
003 678PS:表一的主件与子件都是属于表二里的料号,只有主件号才有对应的识别号。现在需要得到的结果为:主件号与对应的子件号和识别号,并且子件号与对应的料名。结果如下表。
要得到结果表:
主件 子件 料名 识别号
000A 001 456 XX1
000A 002 567 XX1
000B 001 456 XX2
000B 003 678 XX2000A或000B为特指,实际操作不以这两种主件来查询。
表一:
主件 子件
000A 001
000A 002
000B 001
000B 003表二:
料号 料名 识别号
000A 123 XX1
000B 321 XX2
001 456
002 567
003 678PS:表一的主件与子件都是属于表二里的料号,只有主件号才有对应的识别号。现在需要得到的结果为:主件号与对应的子件号和识别号,并且子件号与对应的料名。结果如下表。
要得到结果表:
主件 子件 料名 识别号
000A 001 456 XX1
000A 002 567 XX1
000B 001 456 XX2
000B 003 678 XX2000A或000B为特指,实际操作不以这两种主件来查询。
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (主件 varchar(4),子件 varchar(3))
insert into #T1
select '000A','001' union all
select '000A','002' union all
select '000B','001' union all
select '000B','003'
--> 测试数据: #T2
if object_id('tempdb.dbo.#T2') is not null drop table #T2
create table #T2 (料号 varchar(4),料名 int,识别号 varchar(3))
insert into #T2
select '000A',123,'XX1' union all
select '000B',321,'XX2' union all
select '001',456,null union all
select '002',567,null union all
select '003',678,nullselect * from #T1
join #T2 on #T1.主件=#T2.料号主件 子件 料号 料名 识别号
---- ---- ---- ----------- ----
000A 001 000A 123 XX1
000A 002 000A 123 XX1
000B 001 000B 321 XX2
000B 003 000B 321 XX2(4 行受影响)
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (主件 varchar(4),子件 varchar(3))
insert into #T1
select '000A','001' union all
select '000A','002' union all
select '000B','001' union all
select '000B','003'
--> 测试数据: #T2
if object_id('tempdb.dbo.#T2') is not null drop table #T2
create table #T2 (料号 varchar(4),料名 int,识别号 varchar(3))
insert into #T2
select '000A',123,'XX1' union all
select '000B',321,'XX2' union all
select '001',456,null union all
select '002',567,null union all
select '003',678,nullselect 主件,子件,料名,识别号 from #T1
join #T2 on #T1.主件=#T2.料号主件 子件 料名 识别号
---- ---- ----------- ----
000A 001 123 XX1
000A 002 123 XX1
000B 001 321 XX2
000B 003 321 XX2(4 行受影响)
insert into 表一 values('000A', '001')
insert into 表一 values('000A', '002')
insert into 表一 values('000B', '001')
insert into 表一 values('000B', '003')
create table 表二(料号 varchar(10),料名 varchar(10),识别号 varchar(10))
insert into 表二 values('000A', '123', 'XX1')
insert into 表二 values('000B', '321', 'XX2')
insert into 表二 values('001' , '456', null)
insert into 表二 values('002' , '567', null)
insert into 表二 values('003' , '678', null)
goselect m.* , n.识别号 from 表一 m, 表二 n where m.主件 = n.料号drop table 表一 , 表二/*
主件 子件 识别号
---------- ---------- ----------
000A 001 XX1
000A 002 XX1
000B 001 XX2
000B 003 XX2(所影响的行数为 4 行)
*/
from 表一 a
cross apply(select 料名,识别号 from 表二 where 主件 = a.主件) b
(select * from 表一 a left join 表二 b on a.主件=b.主件 )t1 left join
(select * from 表一 a left join 表二 b on a.子件=b.子件 )t2
on t1.主件=t2.主件 and t1.子件=t2.子件
-- Author : htl258(Tony)
-- Date : 2010-05-18 11:37:10
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [表一]
IF OBJECT_ID('[表一]') IS NOT NULL
DROP TABLE [表一]
GO
CREATE TABLE [表一] ([主件] [nvarchar](10),[子件] [nvarchar](10))
INSERT INTO [表一]
SELECT '000A','001' UNION ALL
SELECT '000A','002' UNION ALL
SELECT '000B','001' UNION ALL
SELECT '000B','003'--> 生成测试数据表: [表二]
IF OBJECT_ID('[表二]') IS NOT NULL
DROP TABLE [表二]
GO
CREATE TABLE [表二] ([料号] [nvarchar](10),[料名] [int],[识别号] [nvarchar](10))
INSERT INTO [表二]
SELECT '000A','123','XX1' UNION ALL
SELECT '000B','321','XX2' UNION ALL
SELECT '001','456',NULL UNION ALL
SELECT '002','567',NULL UNION ALL
SELECT '003','678',NULL--SELECT * FROM [表一]
--SELECT * FROM [表二]-->SQL查询如下:
SELECT *
FROM 表一 A
CROSS APPLY(SELECT 料名,识别号 FROM 表二 WHERE [料号] = A.主件) B
/*
主件 子件 料名 识别号
---------- ---------- ----------- ----------
000A 001 123 XX1
000A 002 123 XX1
000B 001 321 XX2
000B 003 321 XX2(4 行受影响)
*/
主件 varchar(4),子件 varchar(3))
insert 表一
select '000A','001' union all
select '000A','002' union all
select '000B','001' union all
select '000B','003' create table 表二(
料号 varchar(4),料名 varchar(3),识别号 varchar(3))
insert 表二
select '000A','123','XX1' union all
select '000B','321','XX2' union all
select '001','456',null union all
select '002','567',null union all
select '003','678',null select t1.主件,t1.子件,t2.料名,t1.识别号 from
(select * from 表一 a left join 表二 b on a.主件=b.料号 )t1 left join
(select * from 表一 a left join 表二 b on a.子件=b.料号 )t2
on t1.主件=t2.主件 and t1.子件=t2.子件/*
主件 子件 料名 识别号
---- ---- ---- ----
000A 001 456 XX1
000A 002 567 XX1
000B 001 456 XX2
000B 003 678 XX2(所影响的行数为 4 行)*/