22,210
社区成员
发帖
与我相关
我的任务
分享
select a.id,(b.id),a.cid
from (select *,cnt=row_number() over(partition by cid order by id) from a )a
left join c on a.cid=c.id1
left join (select *,cnt=row_number() over(partition by cid order by id) from b )b on b.cid=c.id2
and a.cnt = b.cnt
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-02-04 12:08:09
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(2),[cid] int)
insert [A]
select 1,'aa',1 union all
select 2,'bb',1 union all
select 3,'cc',2 union all
select 4,'dd',3
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[name] varchar(3),[cid] int)
insert [B]
select 100,'abc',11 union all
select 101,'bcd',11 union all
select 102,'cde',12 union all
select 103,'def',13
--> 测试数据:[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([id1] int,[id2] int)
insert [C]
select 1,11 union all
select 2,12 union all
select 3,13
--------------开始查询--------------------------
select
a.id,(b.id),a.cid
from
(select *,cnt=row_number()over(partition by cid order by getdate()) from a )a
left join
c on a.cid=c.id1
left join
(select *,cnt=row_number()over(partition by cid order by getdate()) from b )b on b.cid=c.id2
and
a.cnt = b.cnt
----------------结果----------------------------
/* id id cid
----------- ----------- -----------
1 100 1
2 100 1
3 102 2
4 103 3
(4 行受影响)
*/
------------------------------------
-- Author: flystone
-- Version:V1.001
-- Date:2010-02-04 12:08:18
------------------------------------
-- Test Data: A
If object_id('A') is not null
Drop table A
Go
Create table A(id int,name nvarchar(2),cid int)
Go
Insert into A
select 1,'aa',1 union all
select 2,'bb',1 union all
select 3,'cc',2 union all
select 4,'dd',3
Go
-- Test Data: B
If object_id('B') is not null
Drop table B
Go
Create table B(id int,name nvarchar(3),cid int)
Go
Insert into B
select 100,'abc',11 union all
select 101,'bcd',11 union all
select 102,'cde',12 union all
select 103,'def',13
Go
-- Test Data: C
If object_id('C') is not null
Drop table C
Go
Create table C(id1 int,id2 int)
Go
Insert into C
select 1,11 union all
select 2,12 union all
select 3,13
Go
--Start
select a.id,(b.id),a.cid
from (select *,cnt=(select count(1) from a where cid = aa.cid and id <=aa.id ) from a as aa)a
left join c on a.cid=c.id1
left join (select *,cnt=(select count(1) from b where cid = bb.cid and id <=bb.id ) from b as bb)b on b.cid=c.id2
and a.cnt = b.cnt
--group by a.id,a.cid
--Result:
/*
id id cid
----------- ----------- -----------
1 100 1
2 101 1
3 102 2
4 103 3
(所影响的行数为 4 行)
*/
--End