PKID CPPKID Money Price Area _Date Bool
1 3 200 20 10 2006-1-5 true
2 3 200 20 10 2007-1-5 false
3 3 200 20 10 2008-1-5 false
4 3 200 20 10 2009-1-5 false
5 4 300 20 15 2006-6-6 true
6 4 300 20 15 2007-6-6 false
7 4 300 20 15 2008-6-6 false
8 5 300 30 10 2006-1-5 true
9 5 300 30 10 2007-1-5 false
查询出
PKID CPPKID Money Price Area _Date Bool
2 3 200 20 10 2007-1-5 false
6 4 300 20 15 2007-6-6 false
9 5 300 30 10 2007-1-5 false
--按CPPKID分组,BOOL=‘FALSE’,日期最小那行
1 3 200 20 10 2006-1-5 true
2 3 200 20 10 2007-1-5 false
3 3 200 20 10 2008-1-5 false
4 3 200 20 10 2009-1-5 false
5 4 300 20 15 2006-6-6 true
6 4 300 20 15 2007-6-6 false
7 4 300 20 15 2008-6-6 false
8 5 300 30 10 2006-1-5 true
9 5 300 30 10 2007-1-5 false
查询出
PKID CPPKID Money Price Area _Date Bool
2 3 200 20 10 2007-1-5 false
6 4 300 20 15 2007-6-6 false
9 5 300 30 10 2007-1-5 false
--按CPPKID分组,BOOL=‘FALSE’,日期最小那行
where a.PKID=(select PKID from table bool=a.bool and date<a.date group by CPPKID)
where a.PKID=(select PKID from table where bool=a.bool and date<a.date group by CPPKID)
列 'Rent.PKID' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
insert into tt select 3,200,20,10,'2006-1-5','true'
union all select 3,200,20,10,'2007-1-5','false'
union all select 3,200,20,10,'2008-1-5','false'
union all select 3,200,20,10,'2008-1-5','false'union all select 4,300,20,15,'2006-6-6','true'
union all select 4,300,20,15,'2007-6-6','false'
union all select 4,300,20,15,'2008-6-6','false'union all select 5,300,30,1,'2006-1-5','true'
union all select 5,300,30,1,'2007-1-5','false'--
select * from tt a where pkid in(select top 1 pkid from tt where cppkid=a.cppkid and bool='false' order by _date)
inner join (select cppkid,min(date) date from 表 group by cppkid) t
on t.cppkid = a.cppkid and t.date = a.date
where a.bool = 'false'