一条SQL语句：where STATETIME=TO_DATE('2008-05-12','YYYY-MM-DD')

如果我要查寻出有最多记录的的一台设备编号，怎么查？
字段有：VEHICLEID,STATETIME，state

因为你表里的STATETIME肯定不是0点0分
你用这样的
trunc(STATETIME)=TO_DATE('2008-05-12','YYYY-MM-DD') 再去试试,肯定后下面一句一样的

select a.VEHICLEID,a.STATETIME,a.state from TABELS_his a,
(
select a.VEHICLEID
from(
select a.VEHICLEID,count(*)
from TABELS_his
order by 2 desc)
where rownum=1
) b
where a.VEHICLEID=b.a.VEHICLEID

STATETIME也需要格式化一下，因为STATETIME可能包含有小时、分钟等等。

是这样吗？提示 a和VEHICLEID标示符无效！select a.VEHICLEIDfrom TABELS_his a, (select a.VEHICLEIDfrom (select a.VEHICLEID,count(*) from TABELS_his order by 2 desc) where rownum=1) b where a.VEHICLEID=b.a.VEHICLEID

SELECT a.vehicleid, a.statetime, a.state
  FROM tabels_his a,
       (SELECT a.vehicleid
          FROM (SELECT   a.vehicleid, COUNT (*)
                    FROM tabels_his a
                ORDER BY 2 DESC)
         WHERE ROWNUM = 1) b
WHERE a.vehicleid = b.a.vehicleid少写了个a

select a.GPSID
from devicestate_his a,
(select a.GPSID
from (select a.GPSID,count(*)
from devicestate_his a
order by 2 desc)
where rownum=1)b
where a.GPSID=b.a.GPSID 依然不对呀！！！！！！！！！！！！

你的select count(*) from TABELS where STATETIME=TO_DATE('2008-05-12','YYYY-MM-DD')
这条SQL有问题。
如果这一天有数据，如果下面的SQL查不出来，你找我，我把数据库给吃了。
select count(*) from TABELS where to_char(STATETIME,'yyyy-mm-dd')='2006-03-12'

表名：TABELS_HIS
字段：
数据： SID VEHID STATETIME nid
1 132494989 31804589 2008-5-13 1:17:21 132494511
2 132494990 31804938 2008-5-13 1:17:21 132493999
3 132494991 31804611 2008-5-13 1:17:21 132493538
4 132494992 31804368 2008-5-13 1:17:21 132494144
5 132494993 31804321 2008-5-13 1:17:21 132493867
6 132494994 31804320 2008-5-13 1:17:22 132494163
7 132494995 31804355 2008-5-13 1:17:22 132494043
8 132494996 31804650 2008-5-13 1:17:22 132493469
9 132494997 31804375 2008-5-13 1:17:22 132494804

嗯，我上面写的语句有几处有小错误，测试了下
你贴的数据居然没有重复数据，没出来结果不对，我改了一条数据
WITH tabels_his AS
     (SELECT 132494989 SID, 31804589 vehicleid, '2008-5-13 1:17:21' statetime,
             132494511 nid
        FROM DUAL
      UNION ALL
      SELECT 132494990, 31804938, '2008-5-13 1:17:21', 132493999
        FROM DUAL
      UNION ALL
      SELECT 132494991, 31804611, '2008-5-13 1:17:21', 132493538
        FROM DUAL
      UNION ALL
      SELECT 132494992, 31804368, '2008-5-13 1:17:21', 132494144
        FROM DUAL
      UNION ALL
      SELECT 132494993, 31804321, '2008-5-13 1:17:21', 132493867
        FROM DUAL
      UNION ALL
      SELECT 132494994,  31804355, '2008-5-13 1:17:22', 132494163
        FROM DUAL
      UNION ALL
      SELECT 132494995, 31804355, '2008-5-13 1:17:22', 132494043
        FROM DUAL
      UNION ALL
      SELECT 132494996, 31804650, '2008-5-13 1:17:22', 132493469
        FROM DUAL
      UNION ALL
      SELECT 132494997, 31804375, '2008-5-13 1:17:22', 132494804
        FROM DUAL)SELECT a.*
  FROM tabels_his a,
       (SELECT vehicleid
          FROM (SELECT   vehicleid, COUNT (*)
                    FROM tabels_his
                GROUP BY vehicleid
                ORDER BY 2 DESC)
         WHERE ROWNUM = 1) b
WHERE a.vehicleid = b.vehicleid
结果
Row# SID VEHICLEID STATETIME NID1 132494994 31804355 2008-5-13 1:17:22 132494163
2 132494995 31804355 2008-5-13 1:17:22 132494043

测试了下，白天写的语句都有点小错误
不过你贴的数据居然没有重复数据，所以每条纪录都是一，我改了下语句，并列第一的全列出来
WITH tabels_his AS
     (SELECT 132494989 SID, 31804589 vehicleid, '2008-5-13 1:17:21' statetime,
             132494511 nid
        FROM DUAL
      UNION ALL
      SELECT 132494990, 31804938, '2008-5-13 1:17:21', 132493999
        FROM DUAL
      UNION ALL
      SELECT 132494991, 31804611, '2008-5-13 1:17:21', 132493538
        FROM DUAL
      UNION ALL
      SELECT 132494992, 31804368, '2008-5-13 1:17:21', 132494144
        FROM DUAL
      UNION ALL
      SELECT 132494993, 31804321, '2008-5-13 1:17:21', 132493867
        FROM DUAL
      UNION ALL
      SELECT 132494994, 31804320, '2008-5-13 1:17:22', 132494163
        FROM DUAL
      UNION ALL
      SELECT 132494995, 31804355, '2008-5-13 1:17:22', 132494043
        FROM DUAL
      UNION ALL
      SELECT 132494996, 31804650, '2008-5-13 1:17:22', 132493469
        FROM DUAL
      UNION ALL
      SELECT 132494997, 31804375, '2008-5-13 1:17:22', 132494804
        FROM DUAL)SELECT a.*
  FROM tabels_his a,
       (SELECT vehicleid
          FROM (SELECT vehicleid, DENSE_RANK () OVER (ORDER BY rn DESC) rn
                  FROM (SELECT   vehicleid, COUNT (*) rn
                            FROM tabels_his
                        GROUP BY vehicleid))
         WHERE rn = 1) b
WHERE a.vehicleid = b.vehicleid
结果
Row# SID VEHICLEID STATETIME NID1 132494989 31804589 2008-5-13 1:17:21 132494511
2 132494990 31804938 2008-5-13 1:17:21 132493999
3 132494991 31804611 2008-5-13 1:17:21 132493538
4 132494992 31804368 2008-5-13 1:17:21 132494144
5 132494993 31804321 2008-5-13 1:17:21 132493867
6 132494994 31804320 2008-5-13 1:17:22 132494163
7 132494995 31804355 2008-5-13 1:17:22 132494043
8 132494996 31804650 2008-5-13 1:17:22 132493469
9 132494997 31804375 2008-5-13 1:17:22 132494804

改了下，把一条纪录改成出现两次
WITH tabels_his AS
     (SELECT 132494989 SID, 31804589 vehicleid, '2008-5-13 1:17:21' statetime,
             132494511 nid
        FROM DUAL
      UNION ALL
      SELECT 132494990, 31804938, '2008-5-13 1:17:21', 132493999
        FROM DUAL
      UNION ALL
      SELECT 132494991, 31804611, '2008-5-13 1:17:21', 132493538
        FROM DUAL
      UNION ALL
      SELECT 132494992, 31804368, '2008-5-13 1:17:21', 132494144
        FROM DUAL
      UNION ALL
      SELECT 132494993, 31804321, '2008-5-13 1:17:21', 132493867
        FROM DUAL
      UNION ALL
      SELECT 132494994, 31804355, '2008-5-13 1:17:22', 132494163
        FROM DUAL
      UNION ALL
      SELECT 132494995, 31804355, '2008-5-13 1:17:22', 132494043
        FROM DUAL
      UNION ALL
      SELECT 132494996, 31804650, '2008-5-13 1:17:22', 132493469
        FROM DUAL
      UNION ALL
      SELECT 132494997, 31804375, '2008-5-13 1:17:22', 132494804
        FROM DUAL)SELECT a.*
  FROM tabels_his a,
       (SELECT vehicleid
          FROM (SELECT vehicleid, DENSE_RANK () OVER (ORDER BY rn DESC) rn
                  FROM (SELECT   vehicleid, COUNT (*) rn
                            FROM tabels_his
                        GROUP BY vehicleid))
         WHERE rn = 1) b
WHERE a.vehicleid = b.vehicleid
结果
Row# SID VEHICLEID STATETIME NID1 132494994 31804355 2008-5-13 1:17:22 132494163
2 132494995 31804355 2008-5-13 1:17:22 132494043

CSDN大抽筋
先是回贴显示错误，却看到回贴数在增加，却看不见
好不容易正常了，刚才的回贴全部显示出来，全部重复了

谢谢，辛苦了，终于查出了罪魁祸首，都是这条超大的SQL语句造成的，请看一下这个语句除了什么问题？
该语句导致一个设备的一条数据被写入了上千万次呀

" insert all into sany_devicestate_his "
    " ( "
    "     sid,prevsid,gpsid,uname,state,statetime "
    " ) "
    " values "
    " ( "
    "     seq_sany_devicestate_his.nextval, "
    "     prevsid, gpsid, uname, wzstate, wzstatetime "
    " ) "
    " select nvl(ttt.sid,0) as prevsid, tt.gpsid as gpsid, tt.uname as uname, t.wzstate+200 as wzstate, t.wzstatetime "
    " from sany_devicestate t, sany_deviceuser tt left join "
    "     ( "
    "         select temp1.sid as sid, temp1.state as state, temp1.gpsid as gpsid from sany_devicestate_his temp1, "
    "           (select gpsid, max(statetime) as statetime from sany_devicestate_his where ((state>=200) and (state<250)) group by gpsid) temp2 "
    "         where temp1.gpsid=temp2.gpsid and temp1.statetime = temp2.statetime "
    "     ) ttt on (tt.gpsid = ttt.gpsid) "
    " where (t.usid = tt.usid) and (tt.ttype = 16) and ((ttt.state is NULL) or (t.wzstate+200 <> ttt.state)) "
    "     and exists (select 1 from gps_lastresult ttt where tt.gpsid=ttt.gpsid) ");

你先执行下你的下面的select语句，看看出来的结果正常否
这里不知道你的数据，也不知道你想要的结果，无法判断你的数据是否正常
select nvl(ttt.sid,0) as prevsid, tt.gpsid as gpsid, tt.uname as uname, t.wzstate+200 as wzstate, t.wzstatetime "
    " from sany_devicestate t, sany_deviceuser tt left join "
    "     ( "
    "         select temp1.sid as sid, temp1.state as state, temp1.gpsid as gpsid from sany_devicestate_his temp1, "
    "           (select gpsid, max(statetime) as statetime from sany_devicestate_his where ((state>=200) and (state <250)) group by gpsid) temp2 "
    "         where temp1.gpsid=temp2.gpsid and temp1.statetime = temp2.statetime "
    "     ) ttt on (tt.gpsid = ttt.gpsid) "
    " where (t.usid = tt.usid) and (tt.ttype = 16) and ((ttt.state is NULL) or (t.wzstate+200  <> ttt.state)) "
    "     and exists (select 1 from gps_lastresult ttt where tt.gpsid=ttt.gpsid)

调试易

一条SQL语句：where STATETIME=TO_DATE('2008-05-12','YYYY-MM-DD')

解决方案 »