两表相链问题。。技术有限，实在想不到~

[ck_邬]

现在有两个表，表1,表2,结构如下

表1 | 表2
--------------------------------------------------
col1 col2 col3 | col11 col12
1 a | a 111
2 a | b 222
3 a | b 333
4 b | c 444
5 b | c 555
6 c |
7 c |
8 d |
9 d |
10 d |

如上，我要把table2中的col12更新到table1中的col3
而col2与col11有对应关系，表1与对表2有如下对应可能：
1、表1在表2中找到1个对应的记录；
2、表1在表2中找到与表1一样行数的记录；
3、表1在表2中找不到对应的记录

结果是，表1在表2中要么找到一个，要么找到一样多个，不会出现左边有2行，右边有3行的情况..
我想要的结果如下:

col1 col2 col3
1 a 111
2 a 111
3 a 111
4 b 222
5 b 333
6 c 444
7 c 555
8 d
9 d
10 d

各位大大们请帮忙啊~~我想不出来了~

[ck_邬]

表1 | 表2
--------------------------------------------------
col1 col2 col3 | col11 col12
1 a | a 111
2 a | b 222
3 a | b 333
4 b | c 444
5 b | c 555
6 c |
7 c |
8 d |
9 d |
10 d |

结果
col1 col2 col3
1 a 111
2 a 111
3 a 111
4 b 222
5 b 333
6 c 444
7 c 555
8 d
9 d
10 d

[SQL77]

[Quote=引用 1 楼 chadwick 的回复:]
表1 | 表2
--------------------------------------------------
col1 col2 col3 | col11 col12
1 a | a 111
2 a | b 222
3 a | b 333
4 b | c 444
5 b | c 555
6 c |
7 c |
8 d |
9 d |
10 d |

结果
col1 col2 col3
1 a 111
2 a 111
…
[/Quote]

UPDATE A SET COL3=B.COL2 FROM A JOIN ON B ON A.COL2=B.COL1

??试试

[SQL77]

[Quote=引用 2 楼 SQL77 的回复:]
引用 1 楼 chadwick 的回复:
表1 | 表2
--------------------------------------------------
col1 col2 col3 | col11 col12
1 a | a 111
2 a | b 222
3 a | b 333
4 b | c 444
5 b | c 555
6 c |
7 c |
8 d |
9 d |
10 d |

结果
col1 col2 col3
…
[/Quote]
错了,改下!

UPDATE A SET COL3=B.COL2 FROM A JOIN  B ON A.COL2=B.COL1

[SQL77]

[Quote=引用 3 楼 SQL77 的回复:]
引用 2 楼 SQL77 的回复:
引用 1 楼 chadwick 的回复:
表1 | 表2
--------------------------------------------------
col1 col2 col3 | col11 col12
1 a | a 111
2 a | b 222
3 a | b 333
4 b | c 444
5 b | c 555
6 c |
7 c |
8 d |
9 d |
10 d |
…
[/Quote]
还得LEFT ,回答太快了!!

UPDATE A SET COL3=B.COL2 FROM A LEFT JOIN  B ON A.COL2=B.COL1

[ks_reny]

楼上的都不对吧，按照你们的结果应是
col1 col2 col3
1 a 111
2 a 111
3 a 111
4 b 222
4 b 333
5 b 222
5 b 333
6 c 444
7 c 555
8 d
9 d
10 d

[lg3605119]

update a set a.col3 = b.col2 from 表一 a,表二 b where a.col2 = b.col2 

[lg3605119]

if object_id('表一') is not null
  drop table 表一
go
if object_id('表二') is not null
  drop table 表二
go

create table 表一(col1 int,col2 varchar(10),col3 varchar(10))
insert into 表一
select 1,    'a','' union all          
select 2,    'a','' union all         
select 3,    'a','' union all       
select 4,    'b','' union all         
select 5,    'b','' union all        
select 6,    'c'    ,'' union all       
select 7,    'c'    ,'' union all     
select 8,    'd'    ,'' union all      
select 9,    'd'    ,'' union all      
select 10,    'd'    ,'' 

create table 表二(col11 varchar(10), col12 varchar(10))
insert into 表二
select 'a', '111' union all 
select 'b', '222' union all 
select 'b', '333' union all 
select 'c', '444' union all 
select 'c', '555' 


;with cte as
(
  select *,px=row_number() over(partition by col11 order by col12) from 表二
)
,cte1 as
(
  select a.px,a.col1,a.col2,col3 =b.col12
  from (select *,px = row_number() over(partition by col2 order by col1) from 表一) a left join cte b 
  on a.col2 = b.col11 and a.px=b.px
)
,cte2 as
(
  select a.px,a.col1,a.col2,
         col3 = case when a.col3 is null then (select col3 from cte1 
         where px = (select max(px) from cte1 where col3 is not null and col2 = a.col2) and col2=a.col2) else a.col3 end
  from cte1 a
)



update a set a.col3 = b.col3
from 表一 a,cte2 b 
where a.col2 = b.col2 and a.col1 = b.col1

select * from 表一

drop table 表一,表二

[ck_邬]

[Quote=引用 7 楼 lg3605119 的回复:]
SQL code
if object_id('表一') is not null
drop table 表一
go
if object_id('表二') is not null
drop table 表二
go

create table 表一(col1 int,col2 varchar(10),col3 varchar(10))
insert into 表一
select 1, 'a','' union all
select 2, 'a','' union all
select 3, 'a','' union all
select 4, 'b','' union all
select 5, 'b','' union all …
[/Quote]
方法要不要不那么复杂啊..我想一条SQL语句达成..
方法我倒是想了几个,就是不想太复杂..
用临时表或者用游标都可以做成.但如果可以的话,我尽量不想用这两个方法~

[ck_邬]

[Quote=引用 12 楼 szx1999 的回复:]
SQL codeif object_id('[ta]') is not null drop table [ta]
go
create table [ta]([col1] int,[col2] varchar(1),[col3] int)
insert [ta]
select 1,'a',null union all
select 2,'a',null union all
select 3,'a',null union all
select 4,'b',null union all
select 5,'b',null union all
select 6,'c',null union all
select 7,'c',null union all
select 8,'d',null union all
select 9,'d',null union all
se…
[/Quote]
这位姐姐很厉害..我就是想要这样的结果~~但看不是很明白~~
语句的确够简洁~

[lg3605119]

if object_id('表一') is not null
  drop table 表一
go
if object_id('表二') is not null
  drop table 表二
go

create table 表一(col1 int,col2 varchar(10),col3 varchar(10))
insert into 表一
select 1,    'a','' union all          
select 2,    'a','' union all         
select 3,    'a','' union all       
select 4,    'b','' union all         
select 5,    'b','' union all        
select 6,    'c'    ,'' union all       
select 7,    'c'    ,'' union all     
select 8,    'd'    ,'' union all      
select 9,    'd'    ,'' union all      
select 10,    'd'    ,'' 

create table 表二(col11 varchar(10), col12 varchar(10))
insert into 表二
select 'a', '111' union all 
select 'b', '222' union all 
select 'b', '333' union all 
select 'c', '444' union all 
select 'c', '555' 


update a set a.col3 = b.col3
from 表一 a,
(select a.px,a.col1,a.col2,
         col3 = case when a.col3 is null then (select col3 from 
           (select a.px,a.col1,a.col2,col3 =b.col12
  from (select *,px = row_number() over(partition by col2 order by col1) from 表一) a left join
  (select *,px=row_number() over(partition by col11 order by col12) from 表二) b 
  on a.col2 = b.col11 and a.px=b.px)t 
         where px = (select max(px) from 
(
   select a.px,a.col1,a.col2,col3 =b.col12
  from (select *,px = row_number() over(partition by col2 order by col1) from 表一) a left join
  (select *,px=row_number() over(partition by col11 order by col12) from 表二) b 
  on a.col2 = b.col11 and a.px=b.px
)t 
where col3 is not null and col2 = a.col2) and col2=a.col2) else a.col3 end
  from 
( select a.px,a.col1,a.col2,col3 =b.col12
  from (select *,px = row_number() over(partition by col2 order by col1) from 表一) a left join
  (select *,px=row_number() over(partition by col11 order by col12) from 表二) b 
  on a.col2 = b.col11 and a.px=b.px) a) b 
where a.col2 = b.col2 and a.col1 = b.col1

select * from 表一

drop table 表一,表二

[youzhj]

好复杂的代码

[SQL77]

CREATE TABLE TBTEST1(COL1 INT,COL2 VARCHAR(10),COL3 VARCHAR(10))
INSERT TBTEST1
SELECT 1, 'a','|' UNION ALL   
SELECT 2, 'a','|' UNION ALL
SELECT 3, 'a','|' UNION ALL 
SELECT 4, 'b','|' UNION ALL
SELECT 5, 'b','|' UNION ALL
SELECT 6, 'c','|' UNION ALL
SELECT 7, 'c','|' UNION ALL
SELECT 8, 'd','|' UNION ALL
SELECT 9, 'd','|' UNION ALL
SELECT 10,'d','|'



create table TBTEST2(col11 varchar(10), col12 varchar(10))
insert into TBTEST2
select 'a', '111' union all 
select 'b', '222' union all 
select 'b', '333' union all 
select 'c', '444' union all 
select 'c', '555' 

--SELECT * FROM TBTEST1
--SELECT * FROM TBTEST2


IF EXISTS(SELECT COL11 FROM TBTEST2 GROUP BY COL11 HAVING COUNT(COL11)>=2)

BEGIN
     SELECT ID=IDENTITY(INT,1,1),* INTO #TB1 FROM TBTEST1 
     WHERE COL2 IN(SELECT COL11 FROM TBTEST2 GROUP BY COL11 HAVING COUNT(COL11)>=2)

     SELECT ID=IDENTITY(INT,1,1),* INTO #TB2 FROM TBTEST2 
     WHERE COL11 IN(SELECT COL11 FROM TBTEST2 GROUP BY COL11 HAVING COUNT(COL11)>=2)

     --SELECT COL1,COL2,COL12 FROM #TB1,#TB2 WHERE #TB1.ID=#TB2.ID


     --SELECT * FROM #TB1
     --SELECT * FROM #TB2
     UPDATE TBTEST1 SET COL3=TA.COL12 
            FROM TBTEST1 LEFT JOIN (SELECT COL1,COL2,COL12 FROM #TB1,#TB2 WHERE #TB1.ID=#TB2.ID)AS TA
            ON TBTEST1.COL2=TA.COL2 AND TBTEST1.COL1=TA.COL1
END


IF EXISTS(SELECT COL11 FROM TBTEST2 GROUP BY COL11 HAVING COUNT(COL11)<=1)
  BEGIN
       UPDATE TBTEST1 SET COL3=TBTEST2.COL12 
              FROM TBTEST1 LEFT JOIN TBTEST2  
                 ON TBTEST1.COL2=TBTEST2.COL11
                   WHERE COL11 IN(SELECT COL11 FROM TBTEST2 GROUP BY COL11 HAVING COUNT(COL11)<=1)
  END


SELECT * FROM TBTEST1

COL1 COL2 COL3
----------- ---------- ----------
1 a 111
2 a 111
3 a 111
4 b 222
5 b 333
6 c 444
7 c 555
8 d NULL
9 d NULL
10 d NULL

（所影响的行数为 10 行）

很死的写法!!呵呵

[等不到来世]

if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([col1] int,[col2] varchar(1),[col3] int)
insert [ta]
select 1,'a',null union all
select 2,'a',null union all
select 3,'a',null union all
select 4,'b',null union all
select 5,'b',null union all
select 6,'c',null union all
select 7,'c',null union all
select 8,'d',null union all
select 9,'d',null union all
select 10,'d',null
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col11] varchar(1),[col12] int)
insert [tb]
select 'a',111 union all
select 'b',222 union all
select 'b',333 union all
select 'c',444 union all
select 'c',555
go
--select * from [ta]
--select * from [tb]

with a as (select col1,col2,col3,ra=row_number() over(partition by col2 order by col1) from ta)
,b as (select col11,col12,rb=row_number() over(partition by col11 order by getdate()) from tb)
update a
set col3=(select top 1 col12 from b where col11=a.col2 order by abs(rb-a.ra))

select * from [ta]
/*
col1        col2 col3
----------- ---- -----------
1           a    111
2           a    111
3           a    111
4           b    222
5           b    333
6           c    444
7           c    555
8           d    NULL
9           d    NULL
10          d    NULL

(10 行受影响)

*/