create table T(perID int,paintID int)
insert into T select 1,1
insert into T select 1,2
insert into T select 1,5
insert into T select 1,7
insert into T select 1,11
insert into T select 2,12
insert into T select 2,1
insert into T select 2,4
insert into T select 2,6
insert into T select 2,9
insert into T select 2,11
insert into T select 3,8
insert into T select 3,11
insert into T select 4,1
insert into T select 4,2
insert into T select 5,5
insert into T select 5,8create table #(paint int)
declare @s1 varchar(30),@s2 varchar(30)
set @s1='1,11' --會1和11的人
set @s2='1,11' --會1或者11的人declare @sql varchar(500)
declare @cnt int
select @cnt=len(@s1)-len(replace(@s1,',',''))+1
set @sql='select '+replace(@s1,',',' union all select ')
insert into # exec(@sql)--@s1
select perID
from(
select T.* from T
inner join # on paintID=paint) A
group by perID
having count(*)=@cnt/*
PerID
---------
1
2
*/
--@s2
exec('select distinct perID from T where paintID in ('+@s2+')')
/*
PerID
------------
1
2
3
4
*/
drop table #,T
解决方案 »
- 为什么新建的作业 规定 是每天6点执行,agent也启动了,但就是没执行,查看作业记录是空的,还是列表 中的
- 各位高手能高抬贵手教我如何在SQL2005 中创建作业和调度吗?在下感激在心呐!
- 存储过程 输出参数??
- 两查询结果相加后没有数据了。
- 国外空间出现 500 Internal Server Error
- 请问在SQL2000中如何按条件查询记录,谢谢各位
- 一个表新增和删除的时候触发,应该写在一个触发器呢还是分开2个触发器好?
- VFP打印问题
- ADO和VFP的DBF表操作(DELELTE)问题!一定给分,有经验者来看看!
- 请教大伙一个问题,在VB中实现将SQL SERVER的查询结果插入ACCESS中!用LINKED SERVER
- 一个SQL的排序问题
- 如何把多个表里的text字段内容和并到一个表的text字段中,咨询了好久的数据库问题
create table T(perID int,PaintID int,PaintKind varchar(50))
insert into T select 1,1,'国画'
insert into T select 1,2,'油画'
insert into T select 1,5,'水彩粉画'
insert into T select 1,7,'年画'
insert into T select 1,11,'漆画'
insert into T select 1,12,'插图'
insert into T select 2,1,'国画'
insert into T select 2,4,'雕塑'
insert into T select 2,6,'壁画'
insert into T select 2,8,'连环画'
insert into T select 2,9,'宣传画'
insert into T select 2,11,'漆画'
insert into T select 3,8,'连环画'
insert into T select 3,11,'漆画'
insert into T select 4,1,'国画'
insert into T select 4,2,'油画'
insert into T select 5,5,'水彩粉画'
insert into T select 5,8,'连环画'(1)这些作家既擅长"国画" 还擅长"漆画"
select perid from t where PaintKind in ('国画','漆画') group by perid having count(1)>=2(2)这些作家会"国画"或者会"漆画"
select perid from t where PaintKind in ('国画','漆画') group by perid having count(1)>=1