select name from 表 group by name having count(*)=(select count(distinct id) from 表)
select name from table where id=1 and name='b' ?
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-27 14:04:16 -- 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) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(1)) insert [tb] select 1,'A' union all select 1,'B' union all select 2,'B' union all select 2,'C' --------------开始查询-------------------------- select name from tb where id=1 and name in(select name from tb where id=2)----------------结果---------------------------- /* name ---- B(1 行受影响)*/
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-27 14:04:16 -- 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) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(1)) insert [tb] select 1,'A' union all select 1,'B' union all select 2,'B' union all select 2,'C' --------------开始查询-------------------------- select name from tb t where id=1 and exists(select name from tb where id=2 and name=t.name) ----------------结果---------------------------- /* name ---- B(1 行受影响)*/
select distinct name from tablea k where exists(select * from tablea where k.id<>id)
if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(1)) insert [tb] select 1,'A' union all select 1,'B' union all select 2,'B' union all select 2,'C' select distinct name from tb k where exists(select * from tb where k.id<>id and name=k.name)name ---- B
SELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE NAME=T.NAME AND ID<>T.ID)
if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(1)) insert [tb] select 1,'A' union all select 1,'B' union all select 2,'B' union all select 2,'A' union all select 3,'A' union all select 4,'D'select name from tb t where id=4 and name in ( select name from tb t where id=2 and name=t.name and exists ( select name from tb where id=3 and name=t.name ) ); 看的的 大牛 门 一句话 胜读十年书啊。 name=t.name 唉。没想到呢。菜。。继续努力。
数据表名:test select distinct a.name from test a,test b where a.id<>b.id and a.name=b.name
都说的好复杂呀, select name from table where id in (1,2) 不就可以求出交集了吗?1,和2也可以作为请求参数。
?
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-27 14:04:16
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1))
insert [tb]
select 1,'A' union all
select 1,'B' union all
select 2,'B' union all
select 2,'C'
--------------开始查询--------------------------
select name from tb where id=1 and name in(select name from tb where id=2)----------------结果----------------------------
/* name
----
B(1 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-27 14:04:16
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1))
insert [tb]
select 1,'A' union all
select 1,'B' union all
select 2,'B' union all
select 2,'C'
--------------开始查询--------------------------
select name from tb t where id=1 and exists(select name from tb where id=2 and name=t.name)
----------------结果----------------------------
/* name
----
B(1 行受影响)*/
from tablea k
where exists(select * from tablea where k.id<>id)
drop table [tb]
go
create table [tb]([id] int,[name] varchar(1))
insert [tb]
select 1,'A' union all
select 1,'B' union all
select 2,'B' union all
select 2,'C'
select distinct name
from tb k
where exists(select * from tb where k.id<>id and name=k.name)name
----
B
go
create table [tb]([id] int,[name] varchar(1))
insert [tb]
select 1,'A' union all
select 1,'B' union all
select 2,'B' union all
select 2,'A' union all
select 3,'A' union all
select 4,'D'select name from tb t where id=4 and name in
(
select name from tb t where id=2 and name=t.name and exists
(
select name from tb where id=3 and name=t.name
)
);
看的的 大牛 门 一句话 胜读十年书啊。 name=t.name 唉。没想到呢。菜。。继续努力。
select distinct a.name
from test a,test b where a.id<>b.id and a.name=b.name
select name from table where id in (1,2)
不就可以求出交集了吗?1,和2也可以作为请求参数。