34,590
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表:tb
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([CODE] int)
Insert [tb]
Select 11 union all
Select 11 union all
Select 11 union all
Select 12 union all
Select 13 union all
Select 12 union all
Select 15
Go
--Select * from [tb]
-->SQL查询如下:
select *
from tb t
where exists(
select 1
from tb
where [CODE]=t.[CODE]
group by [CODE]
having count(1)>1)
/*
CODE
11
11
11
12
12
*/
select a.id,a.va,b.id,b.va from @ta a,@ta b
select a.id,a.va,b.id,b.va from @ta a,@ta b where a.id=b.id+1
a.id=b.id+1
join 是 inner join 简写
SQL 几种JOIN用法实例
declare @ta table (id int,va varchar(10))
declare @tb table (id int,vb varchar(10))
insert into @ta select 1,'aa'
insert into @ta select 2,'bc'
insert into @ta select 3,'ccc'
insert into @tb select 1,'2'
insert into @tb select 3,'58'
insert into @tb select 4,'67'
--内连接简单写法
select a.id,a.va,b.id,b.vb from @ta a,@tb b
where a.id=b.id
--内连接
select a.id,a.va,b.id,b.vb from @ta a inner join @tb b
on a.id=b.id
select a.id,a.va,b.id,b.vb from @ta a join @tb b
on a.id=b.id
--左连接(左外连接)
--返回left join 子句中指定的左表的所有行,以及右表所匹配的行。
select a.id,a.va,b.id,b.vb from @ta a left join @tb b
on a.id=b.id
select a.id,a.va,b.id,b.vb from @ta a left outer join @tb b
on a.id=b.id
--右连接(右外连接)
--返回right join 子句中指定的右表的所有行,以及左表所匹配的行。
select a.id,a.va,b.id,b.vb from @ta a right join @tb b
on a.id=b.id
select a.id,a.va,b.id,b.vb from @ta a right outer join @tb b
on a.id=b.id
--完整外连接
--等同左连接+右连接
select a.id,a.va,b.id,b.vb from @ta a full join @tb b
on a.id=b.id
select a.id,a.va,b.id,b.vb from @ta a full outer join @tb b
on a.id=b.id
--交叉连接
--没有两个表之间关系的交叉连接,将产生连接所涉及的表的笛卡尔积。
select a.id,a.va,b.id,b.vb from @ta a cross join @tb b
select a.id,a.va,b.id,b.vb from @ta a,@tb b
--自连接
--一个表和其本身连接。
select a.id,a.va,b.id,b.va from @ta a,@ta b where a.id=b.id+1