求段Sql

[zhanghua_1228]

我要取一个字段同时为多个值的记录。
如:
1 a
2 a
3 a
2 b
1 c
3 c

我要取第一列为1,2,3的记录 得到a

[水族杰纶]

select *
from tb t
where exists(select 1 from tb where col2=t.col2 and col2=1)
and   exists(select 1 from tb where col2=t.col2 and col2=2)
and   exists(select 1 from tb where col2=t.col2 and col2=3)
  

[水族杰纶]

--更正
if not object_id('tb') is null
	drop table tb
Go
Create table tb([col1] int,[col2] nvarchar(1))
Insert tb
select 1,N'a' union all
select 2,N'a' union all
select 3,N'a' union all
select 2,N'b' union all
select 1,N'c' union all
select 3,N'c'
Go
select *
from tb t
where exists(select 1 from tb where col2=t.col2 and col1=1)
and   exists(select 1 from tb where col2=t.col2 and col1=2)
and   exists(select 1 from tb where col2=t.col2 and col1=3)
/*
col1        col2
----------- ----
1           a
2           a
3           a

(3 個資料列受到影響)
*/

[bancxc]

阿,,,

[SQL77]

if not object_id('tb') is null
    drop table tb
Go
Create table tb([col1] int,[col2] nvarchar(1))
Insert tb
select 1,N'a' union all
select 2,N'a' union all
select 3,N'a' union all
select 2,N'b' union all
select 1,N'c' union all
select 3,N'c'
Go
select COL2
from tb t WHERE COL1 IN(1,2,3) GROUP BY COL2 HAVING COUNT(COL1)=3

（所影响的行数为 6 行）

COL2 
---- 
a

（所影响的行数为 1 行）

[htl258_Tony]

IF NOT OBJECT_ID('TB') IS NULL
    DROP TABLE TB
GO
CREATE TABLE TB([COL1] INT,[COL2] NVARCHAR(1))
INSERT TB
SELECT 1,N'A' UNION ALL
SELECT 2,N'A' UNION ALL
SELECT 3,N'A' UNION ALL
SELECT 2,N'B' UNION ALL
SELECT 1,N'C' UNION ALL
SELECT 3,N'C'
GO

SELECT COL2
FROM TB 
WHERE COL1 IN(1,2,3)
GROUP BY COL2
HAVING COUNT(DISTINCT COL1)=3
/*
COL2
----
A

(1 行受影响)
*/

[东那个升]

if not object_id('tb') is null
    drop table tb
Go
Create table tb([col1] int,[col2] nvarchar(1))
Insert tb
select 1,N'a' union all
select 2,N'a' union all
select 3,N'a' union all
select 2,N'b' union all
select 1,N'c' union all
select 3,N'c'


declare @var varchar(10)
set @var='1,2,3'
select [col2] from tb where ','+@var+',' like  '%,'+ltrim([col1])+',%'
group by [col2] 
having count(distinct [col1])=len(@var)-len(replace(@var,',',''))+1

col2
----
a

(1 行受影响)

[junlintianxia07]

select column as a from test where column = 1 or column = 2 or column = 3

或者

select case when column = 1 or column = 2 or column = 3 then column end as a

[htl258_Tony]

IF NOT OBJECT_ID('TB') IS NULL
    DROP TABLE TB
GO
CREATE TABLE TB([COL1] INT,[COL2] NVARCHAR(1))
INSERT TB
SELECT 1,N'A' UNION ALL
SELECT 2,N'A' UNION ALL
SELECT 3,N'A' UNION ALL
SELECT 2,N'B' UNION ALL
SELECT 1,N'C' UNION ALL
SELECT 3,N'C' UNION ALL
SELECT 2,N'A' UNION ALL --增加两行
SELECT 3,N'A' 
GO

SELECT COL2
FROM TB 
WHERE COL1 IN(1,2,3)
GROUP BY COL2
HAVING COUNT(DISTINCT COL1)=3
/*
COL2
----
A

(1 行受影响)
*/

[ws_hgo]

if not object_id('tb') is null
    drop table tb
Go
Create table tb([col1] int,[col2] nvarchar(1))
Insert tb
select 1,N'a' union all
select 2,N'a' union all
select 3,N'a' union all
select 2,N'b' union all
select 1,N'c' union all
select 3,N'c'
Go

select  distinct b1.[col2] from tb b1 join tb b2
on b1.[col2]=b2.[col2] 
and b1.[col1]=b2.[col1]-1
join tb b3 
on b2.[col1]=b3.[col1]-1

col2
----
a

(1 行受影响)

[ws_hgo]

水哥做麻烦啦！~

[ws_hgo]

if not object_id('tb') is null
    drop table tb
Go
Create table tb([col1] int,[col2] nvarchar(1))
Insert tb
select 1,N'a' union all
select 2,N'a' union all
select 3,N'a' union all
select 2,N'b' union all
select 1,N'c' union all
select 3,N'c'
Go
--1
select distinct b1.[col2] from tb b1 join tb b2
on b1.[col2]=b2.[col2] 
and b1.[col1]=b2.[col1]-1
join tb b3 
on b2.[col1]=b3.[col1]-1

--2
select [col2] from tb
where [col1] in(1,2,3)
group by [col2]
having (count([col2]))=3

col2
----
a

(1 行受影响)