sql语句问题，怎样才能当B等于1、2、3都成立时才输出一条记录，列A重复 请高手帮忙

[wtpgood]

请教一个问题啊，我这儿现在有一个表，大致如下：

表1

A B
1 1
1 2
1 3
1 4


我想得到只有当列B等于1、2、3都成立时才输出一条记录，这个表我是通过join on这样的语句做的一个试图，列A有重复

[wtpgood]

感谢Beirut的回答，不过你的答案是当满足a、b、c其中任意一条件就可以成立吧，而我想要的是同时满足abc三个条件是才成立

[wtpgood]

[Quote=引用 3 楼 lihan6415151528 的回复:]
SQL codecreatetable test (aint , bint)insertinto testselect1,1unionallselect1,2unionallselect1,3unionallselect1,4goselect*from testselectcasewhen
(selectcount(*)from testwhere b=1)>0and (selectcount(¡­
[/Quote]

lihan6415151528 的回答应该能满足我的需求，不过请问能不能合并到一个查询中啊，我的条件比较负责，查询的条件可能是几十个，该怎么优化啊？

select PID,count(fid) from VS_BuyerRequestFilter where
cateid='003001' and (
(fid=14 and value1=1) or (fid=15 and value1=10)
or (fid=17 and value1=29 )
)
group by PID having count(fid)=3

这是我自己写的语句，不过因为FID和Value1也有可能存在重复，所以还是得不到正确的答案

这是另外一个朋友提供的语句
SELECT distinct t1.pid
FROM (SELECT PID,cateid FROM VS_BuyerRequestFilter WHERE fid=14 and value1=1) AS t1
JOIN (SELECT PID FROM VS_BuyerRequestFilter WHERE fid=15 and value1=10) AS t2 ON (t1.PID = t2.PID)
JOIN (SELECT PID FROM VS_BuyerRequestFilter WHERE fid=17 and value1=29) AS t3 ON (t1.PID = t3.PID)
.......
where t1.cateid='003001'

这个语句也能达到我的要求，不过我的条件比较多，而且记录会达到几十万条，会不会效率比较低啊？有没有其他的解决方法或优化方案

[华夏小卒]

贴出主要表结构，主要测试数据 ，结果

[zc_0101]

帮顶

[武哥博文]

楼主没有说的很清楚，比如，只能是1,2,3不能有4还是1,2,3,4也可以
另外，1,2,3能重复吗？

[lihan6415151528]

本来就是一条语句啊！

[lihan6415151528]

create table test (a int , b int)
insert into test 
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4
go

select * from test


select case when 
(select count(*) from test where b =1)>0 
and (select count(*) from test where b =2)>0 
and (select count(*) from test where b =3)>0 
then '1' else '0' end

     
---- 
1

（所影响的行数为 1 行）

[无锡阔微信息科技]

请楼主贴出测试数据

[kerisyml]

不明白，select * from test where b in ('1','2','3')

[黄_瓜]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([A] int,[B] int)
insert [tb]
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,1 union all
select 2,2 union all
select 3,4


CREATE FUNCTION dbo.f_str(@id int) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @r varchar(8000) 
    SET @r = '' 
    SELECT @r = @r + ',' + cast(b as varchar(10)) FROM tb WHERE a=@id 
    RETURN STUFF(@r, 1, 1, '') 
END 
GO 
-- 调用函数 
select * from  tb
where a in
(SELECt a FROM tb where dbo.f_str(a) like'%1,2,3%'  GROUP BY a )
/*A           B
----------- -----------
1           1
1           2
1           3
1           4

(4 行受影响)

*/

[黄_瓜]

if exists(
select * from tb
where a in
(SELECt a FROM tb where dbo.f_str(a) like'%1,2,3%' GROUP BY a )
)
print '有列值等于1，2，3'
else
print'无'

[黄_瓜]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[code] varchar(1))
insert [tb]
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'d' union all
select 3,'a' union all
select 3,'c' union all
select 3,'d'
---------------------------
--创建函数
CREATE FUNCTION dbo.f_str(@id int) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @r varchar(8000) 
    SET @r = '' 
    SELECT @r = @r + ',' + cast([code] as varchar(10)) FROM tb WHERE id=@id 
    RETURN STUFF(@r, 1, 1, '') 
END 
GO 
-- 调用函数 
select * from  tb
where id in
(SELECt id FROM tb where dbo.f_str(id) like'%a,b,c%'  GROUP BY id )
/*
id          code
----------- ----
1           a
1           b
1           c

(3 行受影响)


*/

[jiangshun]

输出什么记录？

[weichaozu131083]

把20分给我，我就告诉你