表查询

Question

   我现在有一张表，自段位ID，name，startdate，其中startdate内容格式为‘201010’，现我想查询该表所有的人员，startdate不连续的信息。如：
1  smith   201001
2  smith   201002
3  smith   201004这个信息属于错误信息，我希望将它查询出来，不知道sql或procedure怎么写？

Answer 1

WITH tab AS(
SELECT 1 id, 'smith'NAME, '201001'startdate FROM dual UNION ALL 
SELECT 2, 'smith', '201002' FROM dual UNION ALL 
SELECT 3, 'smith', '201004' FROM dual
),
tmp AS(  --生产连续的日期临时表
SELECT To_Char(Add_Months(Trunc(SYSDATE,'yyyy'),LEVEL-1),'yyyymm') rq FROM dual
CONNECT BY LEVEL<=(SELECT To_Number(SubStr(Max(startdate),5,2)) FROM tab)
)
SELECT b.id, b.NAME,a.rq FROM tmp a,tab b
WHERE NOT EXISTS(SELECT 1 FROM tab WHERE a.rq=startdate) 
ORDER BY 1

Answer 2

SQL> 
SQL> with tableA as
  2  (
  3  select  1 id ,'smith' name, to_date('201001','yyyymm') startdate from dual union all
  4  select  2 id ,'smith' name, to_date('201002','yyyymm') startdate from dual union all
  5  select  3 id ,'smith' name, to_date('201004','yyyymm') startdate from dual
  6  )
  7  select *
  8    from tableA a where name in
  9         (select name
 10            from tableA
 11           group by name
 12           having (max(startdate)-min(startdate))<>count(*))
 13  ;        ID NAME  STARTDATE
---------- ----- -----------
         3 smith 2010-4-1
         2 smith 2010-2-1
         1 smith 2010-1-1

Answer 3

with tb as
(select 1 id,'smith' name,'201001' startdate from dual union all
select 2,'smith','201002' from dual union all
select 3,'smith','201004' from dual)
select a.*
from tb a
where not exists(select 1 from tb b where a.name=b.name and a.startdate=b.startdate-1)
SQL> 
SQL> with tb as
  2  (select 1 id,'smith' name,'201001' startdate from dual union all
  3  select 2,'smith','201002' from dual union all
  4  select 3,'smith','201004' from dual)
  5  select a.*
  6  from tb a
  7  where not exists(select 1 from tb b where a.name=b.name and a.startdate=b.startdate-1)
  8  /
 
        ID NAME  STARTDATE
---------- ----- ---------
         2 smith 201002
         3 smith 201004
 

Answer 4

------上面的有点不对
SQL> 
SQL> with tableA as
  2  (
  3  select  1 id ,'smith' name, to_date('201001','yyyymm') startdate from dual union all
  4  select  2 id ,'smith' name, to_date('201002','yyyymm') startdate from dual union all
  5  select  3 id ,'smith' name, to_date('201006','yyyymm') startdate from dual
  6  )
  7  select a.*
  8    from tableA a
  9   where exists
 10   (select *
 11            from (select name,
 12                         sum(case
 13                               when startdate = nextdate then
 14                                1
 15                               when startdate - add_months(nextdate, 1) = 0 then
 16                                1
 17                               else
 18                                0
 19                             end) cnt,
 20                         count(*) rowcnt
 21                    from (select id,
 22                                 name,
 23                                 startdate,
 24                                 nvl(lag(startdate, 1)
 25                                     over(partition by name order by startdate),
 26                                     startdate) nextdate
 27                            from tableA) t
 28                   group by name) b
 29           where a.name = b.name
 30             and b.cnt <> b.rowcnt)
 31  ;        ID NAME  STARTDATE
---------- ----- -----------
         1 smith 2010-1-1
         2 smith 2010-2-1
         3 smith 2010-6-1SQL> 

Answer 5

这张表中有一万条数据，其中每个人员都有几条数据，那么就要判断每个的月份是否是连续的，怎么通过一条sql查询出月份不是连续的人员的id？上面的大侠的sql没看明白...

Answer 6

---数据测试
-------先按照name分组，按时间排序，使用lag函数找到上一行的时间为nextdate,
-------如果相差的时间为一个月，表示时间连续，然后和改分则的所有行数比较，如果相等，代表连续，不相-----等代表不连续
SQL> with tableA as
  2  (
  3  select  1 id ,'smith' name, to_date('201001','yyyymm') startdate from dual union all
  4  select  2 id ,'smith' name, to_date('201002','yyyymm') startdate from dual union all
  5  select  3 id ,'smith' name, to_date('201003','yyyymm') startdate from dual union all
  6  select  4 id ,'Jokson' name, to_date('201001','yyyymm') startdate from dual union all
  7  select  5 id ,'Jokson' name, to_date('201002','yyyymm') startdate from dual union all
  8  select  6 id ,'Jokson' name, to_date('201009','yyyymm') startdate from dual
  9  )
 10  select a.*
 11    from tableA a
 12   where exists
 13   (select *
 14            from (select name,
 15                         sum(case
 16                               when startdate = nextdate then
 17                                1
 18                               when startdate - add_months(nextdate, 1) = 0 then
 19                                1
 20                               else
 21                                0
 22                             end) cnt,
 23                         count(*) rowcnt
 24                    from (select id,
 25                                 name,
 26                                 startdate,
 27                                 nvl(lag(startdate, 1)
 28                                     over(partition by name order by startdate),
 29                                     startdate) nextdate
 30                            from tableA) t
 31                   group by name) b
 32           where a.name = b.name
 33             and b.cnt <> b.rowcnt)
 34  ;        ID NAME   STARTDATE
---------- ------ -----------
         4 Jokson 2010-1-1
         5 Jokson 2010-2-1
         6 Jokson 2010-9-1SQL> 

Answer 7

这个日期是不固定的，人员个数达到万级，所以上面写死的“select  1 id ,'smith' name, to_date('201001','yyyymm') startdate from dual union all”这个是不行的

Answer 8

with table as这个是我们给你造的虚拟的数据，仅供测试呵呵。相当于你的表

Answer 9

SELECT DISTINCT 人员ID
  FROM (SELECT 人员ID, startdate, lead(startdate) over(PARTITION BY 人员ID ORDER BY startdate) ldate
          FROM 表)
 WHERE ldate IS NOT NULL AND
       add_months(to_date(startdate, 'yyyymmdd'), 1) <> to_date(ldate, 'yyyymmdd');使用分析函数lag或lead是较好的方法，如果数据量大，慢是不可避免的，上亿数据肯定是要有时间条件并且是分区的。要不然~~~~~~~~~~~
有了SQL，存储过程不难写了吧，你要怎么样使用临表都可以的。