同时满足数组中数据的SQL
有一张表 ，第一列class:班级，第二列 是卫生检查分数： score，一个班级有多次卫生检查，比如下面的数据
1班，5, 2012-1-2
2班，4，2012-1-3
3班，9，2012-1-6
1班，6，2012-1-9
2班, 6, 2012-1-10
3班, 5, 2012-1-11

给一个数列
declare @ss varchar(100)
select @ss = '5,6'

求同一班级，同时有5,6两个分数的记录



[解决办法]

SQL code

select *from tb twhere exists (    select 1 from tb e where e.class = t.class    where exists (select 1 from tb where class = e.class                       and charindex(','+ltrim(score)+',',','+@ss+',')>0)    group by class    having count(distinct score) = len(replace(@ss,',','')))--try !!!
[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([class] varchar(3),[score] int,[checkdate] datetime)insert [tb]select '1班','5','2012-1-2' union allselect '2班','4','2012-1-3' union allselect '3班','9','2012-1-6' union allselect '1班','6','2012-1-9' union allselect '2班','6','2012-1-10' union allselect '3班','5','2012-1-11'godeclare @ss varchar(100)select @ss = '5,6'select *from tb ajoin   (select class    from tb    where charindex(','+ltrim(score)+',',','+@ss+',')>0    group by class    having count(distinct score)=2) bon a.class=b.class/**class score       checkdate               class----- ----------- ----------------------- -----1班    5           2012-01-02 00:00:00.000 1班1班    6           2012-01-09 00:00:00.000 1班(2 行受影响)**/
[解决办法]
SQL code
use [tempdb]goif object_id('[tb]') is not null drop table [tb]gocreate table [tb]([class] varchar(3),[score] int,[checkdate] datetime)insert [tb]select '1班','5','2012-1-2' union allselect '2班','4','2012-1-3' union allselect '3班','9','2012-1-6' union allselect '1班','6','2012-1-9' union allselect '2班','6','2012-1-10' union allselect '3班','5','2012-1-11'godeclare @ss varchar(100)select @ss = '5,6';WITH c1 AS(SELECT  CASE WHEN CHARINDEX(CAST(score as varchar(10)), @ss)<>0 THEN 1 ELSE 0 END 'Statistic',  class,  score,  checkdate FROM tb), c2 AS(SELECT  SUM(Statistic) as 'Combination',classFROM c1GROUP BY class )SELECT tb.*FROM c2join tb ON c2.class=tb.classWHERE c2.Combination=2--- 试试--借用下树哥 数据, 嘿嘿……