测试表
create table TEST
(
USER_NAME VARCHAR2(60) not null,
TESTDATE DATE,
STATION VARCHAR2(200)
)加入测试数据
INSERT INTO TEST ('user1',sysdate,'satation1')
INSERT INTO TEST ('user1',sysdate-1,'satation2')
INSERT INTO TEST ('user2',sysdate-2,'satation2')SELECT * FROM TEST
查询结果:
然后执行:
select to_char(testdate,'yyyy/mm/dd'),count(*),STATION from test group by to_char(testdate,'yyyy/mm/dd'),STATION结果为:
如何得到最终想要的结果,最终想要的结果格式为
create table TEST
(
USER_NAME VARCHAR2(60) not null,
TESTDATE DATE,
STATION VARCHAR2(200)
)加入测试数据
INSERT INTO TEST ('user1',sysdate,'satation1')
INSERT INTO TEST ('user1',sysdate-1,'satation2')
INSERT INTO TEST ('user2',sysdate-2,'satation2')SELECT * FROM TEST
查询结果:
然后执行:
select to_char(testdate,'yyyy/mm/dd'),count(*),STATION from test group by to_char(testdate,'yyyy/mm/dd'),STATION结果为:
如何得到最终想要的结果,最终想要的结果格式为
解决方案 »
- oracle的网络配置
- Oracle存储过程的测试问题
- sql语句子查询转换
- Oracle9.2在ASP.NET2.0下乱码,高手请进!可以加分!
- redhat as 4.0下的oracle9.2.0.4,发现在点用内存很多,有什么办法降低内存占用
- 更改表的所有者,请高手进来看看(100分),不够再加
- 请教一下关于oracle9i中建立存储过程的语法问题
- 疑问:Oracle8.0 FOR NT 与Windows2000不兼容????????????????
- oracle的触发器中是不是不可以创建表?
- linux下oracle9i的安装
- 请教一个proc连接oracle时使用at_link的问题
- oracle 语句错误,请指教
from test group by to_char(testdate,'yyyy/mm/dd');
str_sql varchar2(4000);
begintime date;
endtime date;
begin
begintime := sysdate - 10;
endtime := sysdate + 1;
str_sql := 'select to_char(testdate,''yyyy/mm/dd'') as testdate, ' || chr(10);
for item in (select distinct station from test where testdate between begintime and endtime)
loop
str_sql := str_sql || 'case when station = ''' || item.station || ''' then count(distinct user_name) else 0 end as ' || item.station || ',' || chr(10);
end loop;
str_sql := substr(str_sql, 1, length(str_sql) - 2);
str_sql := str_sql || ' from test ' || chr(10)
|| ' group by to_char(testdate,''yyyy/mm/dd''),station';
dbms_output.put_line(str_sql);
end;
最后打印出来的 str_sql 语句就是你要的
SQL> select t.datetime,sum(decode(station,'satation1',cnt,0)) num1,
2 sum(decode(station,'satation2',cnt,0)) num2
3 from (select trunc(testdate) datetime,count(*) cnt,station
4 from test
5 group by trunc(testdate),station) t
6 group by t.datetime;DATETIME NUM1 NUM2
------------------- ---------- ----------
2011-08-20 00:00:00 4 0
2011-08-19 00:00:00 4 3SQL> select * from test;USER_NAME TESTDATE STATION
---------- ------------------- -------------------------
user1 2011-08-20 14:58:06 satation1
user1 2011-08-20 14:58:08 satation1
user1 2011-08-20 14:58:12 satation1
user1 2011-08-20 14:58:14 satation1
user2 2011-08-19 14:58:54 satation1
user2 2011-08-19 14:58:55 satation1
user2 2011-08-19 14:58:56 satation1
user2 2011-08-19 14:58:59 satation1
user2 2011-08-19 14:59:17 satation2
user2 2011-08-19 14:59:18 satation2
user2 2011-08-19 14:59:18 satation2已选择11行。
就你这种,可是我PL/sql里面出来的都是0