与此同时满足数组中数据的SQL
同时满足数组中数据的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两个分数的记录
------解决方案--------------------
有一张表 ,第一列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 t where 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] go create table [tb]([class] varchar(3),[score] int,[checkdate] datetime) insert [tb] select '1班','5','2012-1-2' union all select '2班','4','2012-1-3' union all select '3班','9','2012-1-6' union all select '1班','6','2012-1-9' union all select '2班','6','2012-1-10' union all select '3班','5','2012-1-11' go declare @ss varchar(100) select @ss = '5,6' select * from tb a join (select class from tb where charindex(','+ltrim(score)+',',','+@ss+',')>0 group by class having count(distinct score)=2 ) b on 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] go if object_id('[tb]') is not null drop table [tb] go create table [tb]([class] varchar(3),[score] int,[checkdate] datetime) insert [tb] select '1班','5','2012-1-2' union all select '2班','4','2012-1-3' union all select '3班','9','2012-1-6' union all select '1班','6','2012-1-9' union all select '2班','6','2012-1-10' union all select '3班','5','2012-1-11' go declare @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',class FROM c1 GROUP BY class ) SELECT tb.* FROM c2 join tb ON c2.class=tb.class WHERE c2.Combination=2 --- 试试 --借用下树哥 数据, 嘿嘿……