column1:ID Name select * from tb t where exists(select 1 from tb where Name=t.Name and ID<>t.ID)
--> -->
if not object_id('Tempdb..#') is null drop table # Go Create table #([Col1] int,[Col2] nvarchar(1),[Col3] nvarchar(1)) Insert # select 1,N'a',N'b' union all select 2,N'a',N'c' union all select 3,N'D',N'E' union all select 4,N'F',N'G' union all select 5,N'h',N'j' union all select 6,N'h',N'i' Go Select * from # t where exists(select 1 from # where [Col2]=t.[Col2] and [Col1]<>t.[Col1])
select * from tb where col2 in (select col2 from tb group by col2 having count(*) > 1)
create table tb(col1 int,col2 varchar(10),col3 varchar(10)) insert into tb values(1, 'a','b') insert into tb values(2, 'a','c') insert into tb values(3, 'D','E') insert into tb values(4, 'F','G') insert into tb values(5, 'h','j') insert into tb values(6, 'h','i') select * from tb where col2 in (select col2 from tb group by col2 having count(*) > 1)drop table tb/* col1 col2 col3 ----------- ---------- ---------- 1 a b 2 a c 5 h j 6 h i(所影响的行数为 4 行)*/
declare @t table(id int identity,col1 varchar(2),col2 varchar(2)) insert @t select 'a','b' union all select 'a','c' union all select 'D','E' union all select 'F','G' union all select 'h','j' union all select 'h','i' select * from @t where col1 in (select col1 from @t t where (select count(*) from @t where col1=t.col1 and id<=t.id)>1) or col2 in (select col2 from @t t where (select count(*) from @t where col2=t.col2 and id<=t.id)>1) ---- /* id col1 col2 ----------- ---- ---- 1 a b 2 a c 5 h j 6 h i(所影响的行数为 4 行) */
还请高手帮忙: TB1 字段名:F1 F2 F3 值: A AA NULL B BB NULL C CC NULL TB2 字段名:F1 D E 值: A AA 1 B BB 2 C CC 3 如何根据两表的F1字段关联,将TB2中的E字段写入TB1中,更新后的TB1如下: 字段名:F1 F2 F3 值: A AA 1 B BB 2 C CC 3 谢谢!!
UPDATE TB1 SET F3= (SELECT E FROM TB2 WHERE TB1.F1=TB2.F1 AND TB1.F2=TB2.D)
select * from tb t where exists(select 1 from tb where Name=t.Name and ID<>t.ID)
if not object_id('Tempdb..#') is null
drop table #
Go
Create table #([Col1] int,[Col2] nvarchar(1),[Col3] nvarchar(1))
Insert #
select 1,N'a',N'b' union all
select 2,N'a',N'c' union all
select 3,N'D',N'E' union all
select 4,N'F',N'G' union all
select 5,N'h',N'j' union all
select 6,N'h',N'i'
Go
Select * from # t where exists(select 1 from # where [Col2]=t.[Col2] and [Col1]<>t.[Col1])
select * from tb where col2 in (select col2 from tb group by col2 having count(*) > 1)
insert into tb values(1, 'a','b')
insert into tb values(2, 'a','c')
insert into tb values(3, 'D','E')
insert into tb values(4, 'F','G')
insert into tb values(5, 'h','j')
insert into tb values(6, 'h','i') select * from tb where col2 in (select col2 from tb group by col2 having count(*) > 1)drop table tb/*
col1 col2 col3
----------- ---------- ----------
1 a b
2 a c
5 h j
6 h i(所影响的行数为 4 行)*/
insert @t select 'a','b'
union all select 'a','c'
union all select 'D','E'
union all select 'F','G'
union all select 'h','j'
union all select 'h','i'
select *
from @t
where col1 in
(select col1 from @t t where (select count(*) from @t where col1=t.col1 and id<=t.id)>1)
or col2 in
(select col2 from @t t where (select count(*) from @t where col2=t.col2 and id<=t.id)>1)
----
/*
id col1 col2
----------- ---- ----
1 a b
2 a c
5 h j
6 h i(所影响的行数为 4 行)
*/
TB1
字段名:F1 F2 F3
值: A AA NULL
B BB NULL
C CC NULL
TB2
字段名:F1 D E
值: A AA 1
B BB 2
C CC 3
如何根据两表的F1字段关联,将TB2中的E字段写入TB1中,更新后的TB1如下:
字段名:F1 F2 F3
值: A AA 1
B BB 2
C CC 3
谢谢!!
UPDATE TB1
SET F3=
(SELECT E FROM TB2
WHERE TB1.F1=TB2.F1 AND TB1.F2=TB2.D)