select distinct ptid from table1 where ptcode in('a','b')
解决方案 »
- 疑难语句。谁来帮帮忙
- 求一个sql语句解决方法
- 好难的问题.想的我头都暴了.各位大虾救救我吧
- 要把 “一 三 四 五 二” 排序为 “一 二 三 四 五” 需要用什么排序规则
- 小弟要开始学习SQL Server了,先献分100,望各位以后多多帮忙呀!
- vf中问是出现这样一个问题?(在线及等)
- 怪,怪,怪,怪,怪,为什么?????
- Pb+sql server,俺有问题哦~~~各位指教
- 急!小弟想做个数据厍管理系统不知怎么下手,希望大家能进来聊聊,介绍几本好书源码也好而且还想加上网络功能.
- 求助:一条关于统计的sql语句
- 在我写存储过程的时候,用sql 语句怎样insert单引号'等特殊符号??
- 请教SQLServer2005有没有能够同时接收请求的镜像的架构方案
下面是我的想法,估计条件多了,再加。
SELECT ptid INTO #a FROM table1 where ptcode='a'
SELECT ptid INTO #b FROM table1 where ptcode='b'
SELECT ptid INTO #c FROM table1 where ptcode='c'SELECT a.ptid FROM #a a,#b b,#c c
WHERE a.ptid=b.ptid AND a.ptid=c.ptid AND b.ptid=c.ptid
declare @table1 table (ptid int,ptcode varchar(1))
insert into @table1
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 1,'d' union all
select 1,'e' union all
select 2,'a' union all
select 2,'b' union all
select 3,'a' union all
select 3,'e' union all
select 4,'b' union all
select 4,'e'select ptid from @table1
where ptcode in('a','b') group by ptid having count(1)>=2
insert into @tb select 1,'a'
insert into @tb select 1,'b'
insert into @tb select 1,'c'
insert into @tb select 1,'d'
insert into @tb select 1,'e'
insert into @tb select 2,'a'
insert into @tb select 2,'b'
insert into @tb select 3,'a'
insert into @tb select 3,'e'
insert into @tb select 4,'b'
insert into @tb select 4,'e'declare @s varchar(50)
set @s='a,b'select * from @tb t
where charindex(','+ptcode+',',','+@s+',')>0 and exists(
select * from @tb
where ptid=t.ptid and charindex(','+ptcode+',',','+@s+',')>0
group by ptid
having count(*)=len(@s)-len(replace(@s,',',''))+1
)ptid ptcode
1 a
1 b
2 a
2 b
declare @table1 table (ptid int,ptcode varchar(1))
insert into @table1
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 1,'d' union all
select 1,'e' union all
select 2,'a' union all
select 2,'b' union all
select 3,'a' union all
select 3,'e' union all
select 4,'b' union all
select 4,'e'
SELECT A.ptid
FROM
(
SELECT * FROM @table1
WHERE ptcode in('b')
) AINNER JOIN
(
SELECT * FROM @table1
WHERE ptcode in('a')
) BON A.ptid = B.ptid
insert into @tb select 1,'a'
insert into @tb select 1,'b'
insert into @tb select 1,'c'
insert into @tb select 1,'d'
insert into @tb select 1,'e'
insert into @tb select 2,'a'
insert into @tb select 2,'b'
insert into @tb select 3,'a'
insert into @tb select 3,'e'
insert into @tb select 4,'b'
insert into @tb select 4,'e'
DROP TABLE #t
CREATE TABLE #t
(
ptid int
)DECLARE o CURSOR FOR
SELECT DISTINCT ptid FROM @tb OPEN o
DECLARE @BIGID int, @flag int ,@NO intSET @NO=2
FETCH o into @BIGID
PRINT @BIGID
WHILE ( @@fetch_status=0 )
BEGIN
SELECT @flag=count(*) FROM @tb WHERE ptid=@BIGID AND ptcode IN ('a','b')
IF(@flag=@NO)
BEGIN
INSERT #t SELECT @BIGID
END
FETCH next FROM o INTO @BIGID
ENDDEALLOCATE o
SELECT * FROM #T我这里有个方法 就是必须给出a,b,等条件的个数
不知道能不能帮得上忙
go
insert table1 select 1,'a'
insert table1 select 1,'b'
insert table1 select 1, 'c'
insert table1 select 1, 'd'
insert table1 select 1, 'e'
insert table1 select 2, 'a'
insert table1 select 2, 'b'
insert table1 select 3, 'a'
insert table1 select 3, 'e'
insert table1 select 4, 'b'
insert table1 select 4, 'e'
go
select distinct ptid from table1 where ptid in (select ptid from table1 where ptcode='a') and ptid in (select ptid from table1 where ptcode='b')
go
drop table table1
/*
ptid
-----------
1
2
*/
insert into @table1
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 1,'d' union all
select 1,'e' union all
select 2,'a' union all
select 2,'b' union all
select 3,'a' union all
select 3,'e' union all
select 4,'b' union all
select 4,'e'select ptid from @table1 where ptcode in ('a','b') group by ptid having count(ptid)>=2ptid
-----------
1
2(2 行受影响)