# sql 查询组合问题

0

datetime     typename    number   classify

2014/6/1    作者1           100        Author

2014/6/1    作者2           200        Author

2014/6/1    作者3           300        Author

2014/6/2    作者1           400        Author

2014/6/2    作者2           300        Author

2014/6/2    作者3           200        Author

2014/6/1                                       2014/6/2

typename        number                    typename        number

AndonLi | 初学一级 | 园豆：128

0
```/*--判断表是否存在，如果存在则删除
declare
num   number;
begin
select count(1) into num from all_tables where TABLE_NAME = 'T_Demo20140606' --and OWNER='SCOTT';
if   num=1   then
execute immediate 'drop table T_Demo20140606';
end   if;
end; */
--drop table T_Demo20140606;

Create table T_Demo20140606
(dt2 date,
typename varchar2(100),
number2 number(20),
classify varchar2(100)
);
insert into T_Demo20140606
select to_date('2014/6/1','yyyy/MM/dd'),'作者1',100,'Author' from dual union all
select to_date('2014/6/1','yyyy/MM/dd'),'作者2',200,'Author' from dual union all
select to_date('2014/6/1','yyyy/MM/dd'),'作者3', 300,'Author' from dual  union all
select to_date('2014/6/1','yyyy/MM/dd'),'读者1', 150,'Reader' from dual  union all
select to_date('2014/6/1','yyyy/MM/dd'),'读者2', 250,'Reader' from dual union all
select to_date('2014/6/1','yyyy/MM/dd'),'读者3', 350,'Reader' from dual union all
select to_date('2014/6/2','yyyy/MM/dd'),'作者1', 400,'Author' from dual union all
select to_date('2014/6/2','yyyy/MM/dd'),'作者2', 300,'Author' from dual union all
select to_date('2014/6/2','yyyy/MM/dd'),'作者3', 200,'Author' from dual union all
select to_date('2014/6/2','yyyy/MM/dd'),'读者1', 450,'Reader' from dual union all
select to_date('2014/6/2','yyyy/MM/dd'),'读者2', 350,'Reader' from dual union all

select * from T_Demo20140606;

select typeName,number2
from  T_Demo20140606 t
group by t.dt2,t.classify,typeName,number2
having to_char(t.dt2,'yyyy/MM/dd')='2014/06/01'
order by t.classify,t.number2 desc;
/*
TYPENAME    NUMBER21    作者3    3002    作者2    2003    作者1    1004    读者3    3505    读者2    2506    读者1    150
*/
select t.typeName,t.number2,rank() over(partition by t.classify order by t.classify desc, t.number2 desc) rankno
from  T_Demo20140606 t
where to_char(t.dt2,'yyyy/MM/dd')='2014/06/02' ;
/*
TYPENAME    NUMBER2    RANKNO
1    作者1    400    1
2    作者2    300    2
3    作者3    200    3
4    读者1    450    1
5    读者2    350    2
6    读者3    250    3
*/

drop table  T_Demo20140606;                                                                ```

0

0

```SELECT typeName,number,datetime
FROM Table1
ORDER BY datetime ASC,typename DESC  ```

--希望对你有用

LgV5 | 园豆：221 (菜鸟二级) | 2014-06-05 15:00

您需要登录以后才能回答，未注册用户请先注册