测试表
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结果为:
如何得到最终想要的结果,最终想要的结果格式为
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