34,575
社区成员
发帖
与我相关
我的任务
分享
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
--- 试试
--借用下树哥 数据, 嘿嘿……
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 行受影响)
**/
select *
from tb t
where exists (
select 1 from tb e where e.class = t.class
and 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 !!!
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 !!!