高分解决Oracle中sql语句问题

Question

各位高手，我在Oracle中有一个表，如下（姓名中有重复）
     姓名           钱             时间
                      。
   张三           100             2008/06/15
   李四            30              2008/04/16
   张三           80             2008/06/15
   李四            70              2008/04/16
   王老五           80             2008/7/13
             。。             。。
我就想问哈，如果要选出在时间从2006-2009年中，连续6个月钱在50以上的人的姓名，sql语句应该如何写呢？
比如张三连续6个月的钱都在100以上，就把张三选出来，注意是连续的6个月。

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-01-26 20:29:03
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([姓名] varchar(6),[钱] int,[时间] datetime)
insert [tb]
select '张三',100,'2008/06/15' union all
select '李四',30,'2008/04/16' union all
select '张三',80,'2008/07/15' union all
select '李四',70,'2008/05/16' union all
select '王老五',80,'2008/7/13' union all
select '张三',100,'2008/08/15' union all
select '张三',80,'2008/09/15' union all
select '张三',100,'2008/10/15' union all
select '张三',80,'2008/11/15'
--------------开始查询--------------------------
select
 姓名
from
(
select
 b.姓名,max(b.id) as id
from
 (select id=row_number()over(partition by 姓名 order by 时间),* from tb)a,
 (select id=row_number()over(partition by 姓名 order by 时间),* from tb)b
where
  a.id=b.id-1 and datediff(mm,a.时间,b.时间)=1 and a.钱>50 
group by 
  b.姓名
)t
where
 id>=6
----------------结果----------------------------
/* 姓名
------
张三(1 行受影响)*/

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-01-26 20:29:03
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([姓名] varchar(6),[钱] int,[时间] datetime)
insert [tb]
select '张三',100,'2008/06/15' union all
select '李四',30,'2008/04/16' union all
select '张三',80,'2008/07/15' union all
select '李四',70,'2008/05/16' union all
select '王老五',80,'2008/7/13' union all
select '张三',100,'2008/08/15' union all
select '张三',80,'2008/09/15' union all
select '张三',100,'2008/10/15' union all
select '张三',80,'2008/11/15'
--------------开始查询--------------------------
select
 姓名
from
(
select
 b.姓名,b.id as id
from
 (select id=row_number()over(partition by 姓名 order by 时间),* from tb)a,
 (select id=row_number()over(partition by 姓名 order by 时间),* from tb)b
where
  a.id=b.id-1 and datediff(mm,a.时间,b.时间)=1 and a.钱>50 
group by 
  b.姓名,b.id
)t
where
 id>=6
----------------结果----------------------------
/* 姓名
------
张三(1 行受影响)*/

Answer 3

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-26 20:33:05
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([姓名] varchar(6),[钱] int,[时间] datetime)
insert #TB
select '张三',100,'2008/06/15' union all
select '李四',55,'2008/04/16' union all
select '张三',80,'2008/07/15' union all
select '李四',70,'2008/05/16' union all
select '王老五',80,'2008/7/13' union all
select '张三',80,'2008/08/15' union all
select '张三',80,'2008/09/15' union all
select '张三',80,'2008/10/15' union all
select '张三',80,'2008/11/15' union all
select '张三',40,'2008/12/15' union all
select '李四',70,'2008/06/16' union all
select '李四',60,'2008/07/16' union all
select '李四',40,'2008/08/16' union all
select '李四',70,'2008/09/16'
--------------开始查询--------------------------select * into #tt from #tb order by 姓名,时间
--select * from #tt
ALTER TABLE #tt ADD IDD INTDECLARE @IDD INT,@姓名 VARCHAR(6),@时间 datetime
SET @IDD=0UPDATE #tt 
SET IDD=@IDD,
@IDD=CASE WHEN 姓名=@姓名 AND DATEDIFF(MM,@时间,时间)=1 and 钱>50 THEN @IDD+1 ELSE 0 END,
@姓名=姓名,@时间=时间SELECT * FROM #tt where idd>=5
--drop table #tt
--drop table #tb
----------------结果----------------------------
/* 
姓名     钱           时间                                                     IDD         
------ ----------- ------------------------------------------------------ ----------- 
张三     80          2008-11-15 00:00:00.000                                5（所影响的行数为 1 行）
*/太麻烦了,汗

Answer 4

if object_id('tempdb.dbo.#TB') is not null 
drop table #TB
go 
create table #TB([姓名] varchar(6),[钱] int,[时间] datetime)
insert #TB
select '张三',100,'2008/06/15' union all
select '李四',55,'2008/04/16' union all
select '张三',80,'2008/07/15' union all
select '李四',70,'2008/05/16' union all
select '王老五',80,'2008/7/13' union all
select '张三',80,'2008/08/15' union all
select '张三',80,'2008/09/15' union all
select '张三',80,'2008/10/15' union all
select '张三',80,'2008/11/15' union all
select '张三',40,'2008/12/15' union all
select '李四',70,'2008/06/16' union all
select '李四',60,'2008/07/16' union all
select '李四',40,'2008/08/16' union all
select '李四',70,'2008/09/16' union all 
select '李五',70,'2008/09/16' union all 
select '李五',70,'2008/10/16' union all 
select '李五',70,'2008/11/16' union all 
select '李五',70,'2008/12/16' union all 
select '李五',10,'2009/01/16' union all 
select '李五',70,'2009/02/16' union all 
select '李五',70,'2009/03/16' 
go
with cte as
(
select rn=ROW_NUMBER()over(partition by 姓名 order by 时间),姓名,时间
from #TB where [钱]>=50 )
select distinct 姓名 
from cte k 
where  exists(select * from cte where k.姓名=姓名 and rn>5)
and Not exists(select * from cte where DATEADD(MONTH,-k.rn,k.时间)<> DATEADD(MONTH,-rn,时间) and k.姓名=姓名)
/*
姓名
------
张三
*/

Answer 5

--建表
create table test2(name varchar2(30),money number,time date);
--数据
insert into TEST2 (NAME, MONEY, TIME)
values ('张三', 100, to_date('01-01-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('李四', 70, to_date('01-01-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('张三', 140, to_date('01-02-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('张三', 120, to_date('01-03-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('张三', 130, to_date('01-04-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('张三', 90, to_date('01-05-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('李四', 20, to_date('01-02-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('李四', 100, to_date('01-03-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('张三', 90, to_date('01-06-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('李四', 20, to_date('01-04-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('李四', 100, to_date('01-05-2009', 'dd-mm-yyyy'));
insert into TEST2 (NAME, MONEY, TIME)
values ('李四', 100, to_date('01-06-2009', 'dd-mm-yyyy'));
commit;
--测试
select *
  from (select distinct a.name, count(*) cnt
          from (select t.* from test2 t) a,
               (select t.*, trunc(last_day(Add_months(time, 5)), 'dd') time6
                  from test2 t) b
         where a.name = b.name
           and a.time <= b.time6
           and a.money > 50
         group by a.name, b.time)
 where cnt >= 6;
--测试结果OK
SQL> 
NAME                                  CNT
------------------------------ ----------
张三                                    6

Answer 6

--不好意思，上次回复的语句少了一个条件AND a.time >= b.time
--应该是这样
SELECT *
  FROM (SELECT DISTINCT a.name, COUNT(*) cnt
          FROM (SELECT t.* FROM test2 t) a,
               (SELECT t.*, trunc(last_day(Add_months(TIME, 5)), 'dd') time6 FROM test2 t) b
         WHERE a.name = b.name AND
               a.time >= b.time AND
               a.time <= b.time6 AND
               a.money > 50
         GROUP BY a.name, b.time)
 WHERE cnt >= 6;

Answer 7

借用一下6楼的测试数据，补充几条记录
with tb as(
select name,money,to_date(time,'yyyy/mm/dd')time from (
select '张三' name,100 money,'2008/06/15' time from dual union all
select '李四',55,'2008/04/16' from dual union all
select '张三',80,'2008/07/15' from dual union all
select '李四',70,'2008/05/16' from dual union all
select '王老五',80,'2008/7/13' from dual union all
select '张三',80,'2008/08/15' from dual union all
select '张三',80,'2008/09/15' from dual union all
select '张三',80,'2008/10/15' from dual union all
select '张三',80,'2008/11/15' from dual union all
select '张三',40,'2008/12/15' from dual union all
select '李四',70,'2008/06/16' from dual union all
select '李四',60,'2008/07/16' from dual union all
select '李四',40,'2008/08/16' from dual union all
select '李四',70,'2008/09/16' from dual union all 
select '李四',70,'2008/10/16' from dual union all
select '李四',70,'2008/11/16' from dual union all
select '李四',70,'2008/12/16' from dual union all
select '李四',70,'2009/1/16' from dual union all
select '李四',70,'2009/2/16' from dual union all
select '李四',70,'2009/3/16' from dual union all
select '李五',70,'2008/09/16' from dual union all 
select '李五',70,'2008/10/16' from dual union all 
select '李五',70,'2008/11/16' from dual union all 
select '李五',70,'2008/12/16' from dual union all 
select '李五',10,'2009/01/16' from dual union all 
select '李五',70,'2009/02/16' from dual union all 
select '李五',70,'2009/03/15' from dual ))select name,min(time),max(time),max(level)
from tb t
start with money>=50 and not exists(
  select 1 from tb where name=t.name and trunc(time,'mm')=trunc(add_months(t.time,-1),'mm') and money>=50)
connect by prior name=name and money>=50
  and prior trunc(add_months(time,1),'mm')=trunc(time,'mm')
group by name,level-rownum
having max(level)>=6

Answer 8

其实也就是连续数问题。
先用money>=55元过滤记录，然后用月份-row_number()over(partition by xx order by xx)再分组，having count(*)>=6就可以了。

Answer 9

--连续6个月累计>=300的人名
SELECT DISTINCT NAME
  FROM (SELECT t.*,
               SUM(t.money) over(PARTITION BY t.name ORDER BY t.time rows BETWEEN 0 preceding AND 5 following) total
          FROM test2 t)
 WHERE total >= 300;

Answer 10

有重名就select distinct name from(...)一下嘛

Answer 11

不会有重名的，名字只是一个替代，其实在Oracle中，是另一代码，是不会有重名的情况出现的，

Answer 12

谢谢了，不过我的数据库是Oracle，有些sql的函数不能被正确识别，如last_day等等，不过有了方向，还是弄好了，谢谢大家，马上给分哈