tbl
id type isCheck date
1 type1 1 2006-9-1
2 type2 0 2006-9-2
3 type1 0 2006-9-3
4 type1 0 2006-9-4数据有很多条
求同样类型(type1)的数据,如果isCheck=1到 最新的纪录数量
id type isCheck date
1 type1 1 2006-9-1
2 type2 0 2006-9-2
3 type1 0 2006-9-3
4 type1 0 2006-9-4数据有很多条
求同样类型(type1)的数据,如果isCheck=1到 最新的纪录数量
???
from tbl
where isCheck = 1
group by type
直接放到where语句
select type, count(*) as num from tb1
where isCheck=1
group by type
(SELECT TYPE,date=MAX(date)
group by type
)tb
where tb1.type='type1' and tb1.type=tb.type and tb1.date=tb.date
tbl
id type isCheck date
1 type1 1 2006-9-1
2 type2 0 2006-9-2
3 type1 0 2006-9-3
4 type1 0 2006-9-4你想要的结果是什么?给出来
FROM TB1,
(SELECT TYPE,date=MAX(date)
group by type
) as tb
where tb1.type=tb.type and tb1.isCheck=1
SELECT tb1.*,datediff(day,tb1.date,tb.date) as 到
FROM TB1,
(SELECT TYPE,date=MAX(date)
group by type
) as tb
where tb1.type=tb.type and tb1.isCheck=1
type num
type1 2
id type isCheck date
1 type1 1 2006-9-1
2 type2 0 2006-9-2
3 type1 0 2006-9-3
4 type1 0 2006-9-4type num
type1 2
tbl
id type isCheck date
1 type1 1 2006-8-1
2 type1 0 2006-8-2
3 type1 0 2006-8-3
4 type1 1 2006-9-1
5 type2 0 2006-9-2
6 type1 0 2006-9-3
7 type1 0 2006-9-4type num
type1 2
统计的都是最新未check的纪录数
create table tbl
(
id int ,
type varchar(10),
ischeck int ,
date varchar(10)
)
insert into tbl select 1 , 'type1', 1 , '2006-8-1'
union all select 2 ,'type1', 0 , '2006-8-2'
union all select 3 ,'type1', 0 ,'2006-8-3'
union all select 4 ,'type1', 1 ,'2006-9-1'
union all select 5 ,'type2', 0 ,'2006-9-2'
union all select 6 ,'type1', 0 ,'2006-9-3'
union all select 7 ,'type1', 0 ,'2006-9-4'select type ,count(*) as num
from tbl a
where exists (select 1 from
(
select type ,max(date) as [date]
from tbl
where ischeck =1
group by type ) b
where b.type =a.type and b.date< a.date
)
group by type type num
---------- -----------
type1 2(所影响的行数为 1 行)
where t.ischeck=1 and not exist(select 1 from tb1
where t.type=type and ischeck=1 and date>t.date)