Sql巨星级别问题啊~~~~~~~~~不知道对高手来说是不是很简单的问题啊~

Question

filedType filedName filedValue   time
type1       name1    value1      2010-10-11
type1      name1    value1      2010-10-12
type1      name2    value1      2010-10-13
type1      name2    value1      2010-10-14
type1      name2    value1      2010-10-15
type1      name3    value1      2010-10-16type2      name1    value1      2010-10-17
type2      name1    value1      2010-10-18
type2      name1    value1      2010-10-1type3      name2    value1      2010-10-11
一、需求：
1、针对filedType分组
2、分组后count filedName,只显示count结果Top 2的。
3、在2的结果基础上，针对filedType，只显示每个分组按时间排列的前2条。
二、例子：
例如数据如上啊，希望结果是：
filedType filedName filedValue    time             
type1 name1 value1                2010-10-11       
type1 name1 value1                2010-10-12   
    
type2 name1 value1                2010-10-1  
type2 name1 value1                2010-10-17 
      
type3 name2 value1                2010-10-11       
                                        
其中数据
type1 name3 value1                2010-10-16      被过滤掉了。type1 name2 value1                2010-10-13      被过滤掉了。 
type1 name2 value1                2010-10-14      被过滤掉了。
type1 name2 value1                2010-10-15      被过滤掉了。type2 name1 value1                2010-10-18      被过滤掉了。
三、解释：
1、首先数据有三种类型
type1
type2
type32、然后type1的filedName有三种类型分别是
name1 2条记录。
name2 3条记录。
name3 1条记录。3、只保留按照filedName类型数量最多的前两个类型的数据即
name1 2条记录。
name2 3条记录。
所以去掉name3这条记录4、因为每个filedType只按照时间保留2条记录所以去掉
type1 name2 value1                2010-10-13      被过滤掉了。 
type1 name2 value1                2010-10-14      被过滤掉了。
type1 name2 value1                2010-10-15      被过滤掉了。
这三条记录5、type2、type3类型逻辑同上。
SELECT *
  FROM test
 WHERE (filedType, filedName) IN 
       (SELECT filedType, filedName
          FROM (SELECT filedType,
                       filedName,
                       row_number() over(PARTITION BY filedType, filedName ORDER BY cnt DESC) row_num
                  FROM (SELECT filedType, filedName, COUNT(*) cnt
                          FROM test
                         GROUP BY filedType, filedName))
         WHERE row_num <= 2);我觉得上面这个sql就能基本表达我的意思~~只是没有加上针对filedType，只显示每个分组按时间排列的前2条。这个逻辑~不知道怎么写麻烦大家了啊。

Answer 1

1、首先数据有三种类型
type1
type2
type32、然后type1的filedName有三种类型分别是
name1 2条记录。
name2 3条记录。
name3 1条记录。应该是对filedType filedName 分组

Answer 2

用ms sql解决一下，
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (filedType varchar(5),filedName varchar(5),filedValue varchar(6),time datetime)
insert into #tb
select 'type1','name1','value1','2010-10-11' union all
select 'type1','name1','value1','2010-10-12' union all
select 'type1','name2','value1','2010-10-13' union all
select 'type1','name2','value1','2010-10-14' union all
select 'type1','name2','value1','2010-10-15' union all
select 'type1','name3','value1','2010-10-16' union all
select 'type2','name1','value1','2010-10-17' union all
select 'type2','name1','value1','2010-10-18' union all
select 'type2','name1','value1','2010-10-1' union all
select 'type3','name2','value1','2010-10-11'
select distinct t.* from #tb t 
join 
(
select filedType,filedName,n=count(*) from #tb group by filedType,filedName
) a
on a.filedtype=t.filedtype and a.filedname=t.filedname 
where (
select count(*) from 
(
select filedType,filedName,n=count(*) from #tb group by filedType,filedName
) b
where b.filedtype=a.filedtype and b.n>a.n)<2
and (select count(*) from #tb where filedType=t.filedType and time<t.time)<2 filedType filedName filedValue time
--------- --------- ---------- -----------------------
type1     name1     value1     2010-10-11 00:00:00.000
type1     name1     value1     2010-10-12 00:00:00.000
type2     name1     value1     2010-10-01 00:00:00.000
type2     name1     value1     2010-10-17 00:00:00.000
type3     name2     value1     2010-10-11 00:00:00.000(5 行受影响)

Answer 3

根据上面的，修改下即可
--oracle 版SQL> alter session set nls_date_format = 'yyyy-mm-dd';会话已更改。SQL>  insert into tb
  2   select 'type1','name1','value1','2010-10-11' from dual union all
  3   select 'type1','name1','value1','2010-10-12' from dual  union all
  4   select 'type1','name2','value1','2010-10-13' from dual  union all
  5   select 'type1','name2','value1','2010-10-14' from dual  union all
  6   select 'type1','name2','value1','2010-10-15' from dual  union all
  7   select 'type1','name3','value1','2010-10-16' from dual  union all
  8   select 'type2','name1','value1','2010-10-17' from dual  union all
  9   select 'type2','name1','value1','2010-10-18' from dual  union all
 10   select 'type2','name1','value1','2010-10-1' from dual  union all
 11   select 'type3','name2','value1','2010-10-11' from dual ;已创建10行。SQL> select distinct t.* from tb t 
  2  join 
  3  (
  4   select filedType,filedName,count(*)as n from tb group by filedType,filedName
  5  ) a
  6  on a.filedtype=t.filedtype and a.filedname=t.filedname 
  7  where (
  8    select count(*) from 
  9     (
 10      select filedType,filedName,count(*) as n from tb group by filedType,filedName
 11     ) b
 12     where b.filedtype=a.filedtype and b.n>a.n)<2
 13  and (select count(*) from tb where filedType=t.filedType and time<t.time)<2;FILED FILED FILEDV TIME
----- ----- ------ ----------
type3 name2 value1 2010-10-11
type2 name1 value1 2010-10-01
type1 name1 value1 2010-10-12
type1 name1 value1 2010-10-11
type2 name1 value1 2010-10-17

Answer 4

楼主google下oracle的分析函数吧，你这些问题对于分析函数都是小case

Answer 5

create table filed
(filedType char(5), filedName char(5), filedValue char(6), time date);insert into filed values('type1','name1','value1',date'2010-10-11');
insert into filed values('type1','name1','value1',date'2010-10-12');
insert into filed values('type1','name2','value1',date'2010-10-13');
insert into filed values('type1','name2','value1',date'2010-10-14');
insert into filed values('type1','name2','value1',date'2010-10-15');
insert into filed values('type1','name3','value1',date'2010-10-16');insert into filed values('type2','name1','value1',date'2010-10-17');
insert into filed values('type2','name1','value1',date'2010-10-18');
insert into filed values('type2','name1','value1',date'2010-10-1');insert into filed values('type3','name2','value1',date'2010-10-11');with t1 as(
select filedType,filedName,filedValue,time,
count(1) over (partition by filedType,filedName) cnt
from filed
),
t2 as(
select filedType,filedName,filedValue,time,
dense_rank() over (partition by filedType order by cnt desc) dr
from t1
),
t3 as(
select filedType,filedName,filedValue,time,
row_number() over (partition by filedType order by time) rn
from t2 
where dr<=2
)
select filedType,filedName,filedValue,time from t3 where rn<=2;
/*
type1 name1 value1 2010-10-11
type1 name1 value1 2010-10-12
type2 name1 value1 2010-10-01
type2 name1 value1 2010-10-17
type3 name2 value1 2010-10-11
*/

Answer 6

针对楼主给出的SQL：
求filedType, filedName的row_num<=2还有可能cnt相同的呢？这个顺序不确定了，是否需要考虑？

Answer 7

借用5楼的建表及插数据语句
 
SQL> select * from filed;
 
FILEDTYPE FILEDNAME FILEDVALUE TIME
--------- --------- ---------- -----------
type1     name1     value1     2010-10-11
type1     name1     value1     2010-10-12
type1     name2     value1     2010-10-13
type1     name2     value1     2010-10-14
type1     name2     value1     2010-10-15
type1     name3     value1     2010-10-16
type2     name1     value1     2010-10-17
type2     name1     value1     2010-10-18
type2     name1     value1     2010-10-1
 
9 rows selected
 
SQL> 
SQL> insert into filed values('type3','name2','value1',date'2010-10-11');
 
1 row inserted
SQL> SELECT filedType,filedName,filedValue,time FROM
  2  (SELECT filedType,filedName,filedValue,time,ROW_number()over(PARTITION BY filedtype ORDER BY filedType,filedName,filedValue,time) rn
  3    FROM filed)
  4  WHERE rn<=2;
 
FILEDTYPE FILEDNAME FILEDVALUE TIME
--------- --------- ---------- -----------
type1     name1     value1     2010-10-11
type1     name1     value1     2010-10-12
type2     name1     value1     2010-10-1
type2     name1     value1     2010-10-17
type3     name2     value1     2010-10-11
 
SQL> 

Answer 8

额。分组内只按时间排序啊，去掉几个排序字段。
SQL> SELECT filedType,filedName,filedValue,time FROM
  2  (SELECT filedType,filedName,filedValue,time,ROW_number()over(PARTITION BY filedtype ORDER BY filedType,time) rn
  3    FROM filed)
  4  WHERE rn<=2;
 
FILEDTYPE FILEDNAME FILEDVALUE TIME
--------- --------- ---------- -----------
type1     name1     value1     2010-10-11
type1     name1     value1     2010-10-12
type2     name1     value1     2010-10-1
type2     name1     value1     2010-10-17
 
SQL> 

Answer 9

select filedType, filedName, filedValue, time
    from (select filedType, filedName, filedValue, time,
                 rank() over (partition by filedtype order by time) r
              from (select filedType, filedName, filedValue, time,
                           count(*) over(partition by filedtype,filedname) ct
                        from t)
              where ct>=2)
    where r<=2
    order by filedtype,time;
               

Answer 10

重新排下格式select filedType, filedName, filedValue, time
    from (select filedType, filedName, filedValue, time,
                 rank() over (partition by filedtype order by time) r
              from (select filedType, filedName, filedValue, time,
                           count(*) over(partition by filedtype,filedname) ct
                        from t)
              where ct>=2)
    where r<=2
    order by filedtype,time;

Answer 11

with t_test 
as
(select 'type1' filedType ,'name1' filedName ,'value1' filedValue ,'2010-10-11' dtime from dual
union all
select 'type1' filedType ,'name1' filedName ,'value1' filedValue ,'2010-10-12' dtime from dual
union all
select 'type1' filedType ,'name2' filedName ,'value1' filedValue ,'2010-10-13' dtime from dual
union all
select 'type1' filedType ,'name2' filedName ,'value1' filedValue ,'2010-10-14' dtime from dual
union all
select 'type1' filedType ,'name2' filedName ,'value1' filedValue ,'2010-10-15' dtime from dual
union all
select 'type1' filedType ,'name3' filedName ,'value1' filedValue ,'2010-10-10' dtime from dual
union all
select 'type2' filedType ,'name1' filedName ,'value1' filedValue ,'2010-10-17' dtime from dual
union all
select 'type2' filedType ,'name1' filedName ,'value1' filedValue ,'2010-10-18' dtime from dual
union all
select 'type2' filedType ,'name1' filedName ,'value1' filedValue ,'2010-10-1' dtime from dual
union all
select 'type3' filedType ,'name2' filedName ,'value1' filedValue ,'2010-10-11' dtime from dual)select * from (
select b.*,row_number()over(partition by filedtype order by dtime )j from (
select * from (
select a.*,dense_rank()over(partition by filedtype order by n desc )m  from (
select t_test.* ,count(1)over(partition by filedtype,filedName )n from t_test) a
)where m <=2
)b) 
where j < = 2