坐标表 table1 有以下字段: uID(人员ID) thedate(时间) pointID(位于地点)
记录1: 1 2008-8-7 13:00:00 办公室
记录2: 1 2008-8-7 13:17:00 厂房
记录3: 2 2008-8-7 13:00:00 办公室
记录4: 2 2008-8-7 13:25:00 厂房
记录5: 3 2008-8-7 13:25:10 办公室
记录6: 4 2008-8-7 13:25:11 厂房我需要查询的结果是:
在2008-8-7 13:26:00 在"厂房"的人数有 多少个人
记录1: 1 2008-8-7 13:00:00 办公室
记录2: 1 2008-8-7 13:17:00 厂房
记录3: 2 2008-8-7 13:00:00 办公室
记录4: 2 2008-8-7 13:25:00 厂房
记录5: 3 2008-8-7 13:25:10 办公室
记录6: 4 2008-8-7 13:25:11 厂房我需要查询的结果是:
在2008-8-7 13:26:00 在"厂房"的人数有 多少个人
解决方案 »
- 超简单的问题
- 续select top 1 studentid from
- 求SQL语句
- SQL SERVER 2000数据库还原
- Sql Server中如何解决:“一个表只能有一个时间戳列”这样的错误
- sql 2000 导出access的一个小问题,加急快递,有人收没?
- 如何用存储过程调用WebService? 先100分请高手指教。
- 请问各位大虾SQL Server2000 DTS导数据的原理是什么?
- 新手:access数据库,请帮帮我!!!
- 简单的问题(关于BDE Administrator)
- 我碰到个很奇怪的奇怪的事情,等人解决
- Sql 账号密码x,db_pub 权限,但能看到 sa 建的所有数据库存储过程实体(代码),请问怎样让这个帐号看不到呢?
select count(1) from table1 where pointid = '厂房'
from table1
where pointid = '厂房'
and thedate = '2008-8-7 13:26:00'
and thedate =‘2008-8-7 13:26:00 ’
比如记录6: 4 2008-8-7 13:25:11 厂房
这个人25分就来到了厂房,26分也应该在厂房,只要没有下一条记录说明他在其他地方
from table1
where pointid = '厂房'
and thedate = '2008-8-7 13:26:00') a 试试,接分
from table1
where pointid = '厂房'
and thedate in (select max(thedate) from table1 )) a 最大时间,OK
and ((select max(thedate) from table1 where pointid = '厂房')>'2008-8-7 13:26:00 '
or (select min(thedate) from table1 where pointid = '厂房')<='2008-8-7 13:26:00 ')
select count(*) from table1 where pointid = '厂房' and thedate>='2008-8-7 00:00:00' and thedate<='2008-8-7 23:59:59'
from table1 group by uIDdeclare @max_qty integerselect @max_qty = count(*) from #table1 as a,table1 as b
where a.uid = b.uid and a.thedate = b.thedate and b.pointid = '厂房'
select @max_qty --rollback tran
and select min(thedate) from table1 where pointid = '厂房')=< '2008-8-7 13:26:00'
from table1
where pointid = '厂房'
and thedate in (select max(thedate) from table1 )) a 但是MAX(thedate)好象是全部记录的MAX,而不是distinct uID那个人的MAX
from table1
where pointid = '厂房'
and thedate in (select max(thedate) from table1 )) a 但是MAX(thedate)好象是全部记录的MAX,而不是distinct uID那个人的MAX的时间
from table1
where pointid = '厂房'
and thedate in (select max(thedate) from table1 )) a 但是MAX(thedate)好象是全部记录的MAX,而不是distinct uID那个人的MAX的时间
insert #t
select 1 , '2008-8-7 13:00:00' , '办公室 '
union all select 1 , '2008-8-7 13:17:00' , '厂房'
union all select 2 , '2008-8-7 13:00:00' , '办公室'
union all select 2 , '2008-8-7 13:25:00' , '厂房'
union all select 3 , '2008-8-7 13:25:10' , '办公室'
union all select 4 , '2008-8-7 13:25:11' , '厂房'
select count(1) from #t t
inner join (
select uid,max(thedate) as thedate1 from #t group by uid )c
on c.uid=t.uid and c.thedate1=t.thedatewhere t.pointID='厂房'
from table1 a
where a.pointid = '厂房'
and a.thedate =(select max(thedate) from table1 b where b.uID=a.uID ))
试试这样可不可以吧
我测试过基本上上面的应该是正确的