表结构:
CALL_HOUR,
SITE_ID,
API_ID
CALL_COUNT,
ERR_COUNT输入数据:
INSERT INTO
(CALL_HOUR, SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 1, 1, 3, 2)
INSERT INTO
(CALL_HOUR, SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 1, 2, 2, 1)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 1, 3, 4, 3)
INSERT INTO
(CALL_HOUR, SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 2, 1, 2, 1)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 2, 2, 5, 3)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 2, 3, 6, 3)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 3, 1, 9, 4)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 3, 2, 4, 1)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 3, 3, 7, 2)现在希望用sql语句产生如下形式的数据格式
HOUR API_ID SITE01_CALLCOUNT SITE01_ERRCOUNT SITE02_CALLCOUNT SITE02_ERRORCOUNT SITE03_CALLCOUNT SITE03_ERRCOUNT
00:00-00:59 1 3 2 2 1 9 4
HOUR API_ID SITE01_CALLCOUNT SITE01_ERRCOUNT SITE02_CALLCOUNT SITE02_ERRORCOUNT SITE03_CALLCOUNT SITE03_ERRCOUNT
00:00-00:59 2 2 1 5 3 4 1
HOUR API_ID SITE01_CALLCOUNT SITE01_ERRCOUNT SITE02_CALLCOUNT SITE02_ERRORCOUNT SITE03_CALLCOUNT SITE03_ERRCOUNT
00:00-00:59 3 4 3 6 3 7 2
CALL_HOUR,
SITE_ID,
API_ID
CALL_COUNT,
ERR_COUNT输入数据:
INSERT INTO
(CALL_HOUR, SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 1, 1, 3, 2)
INSERT INTO
(CALL_HOUR, SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 1, 2, 2, 1)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 1, 3, 4, 3)
INSERT INTO
(CALL_HOUR, SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 2, 1, 2, 1)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 2, 2, 5, 3)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 2, 3, 6, 3)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 3, 1, 9, 4)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 3, 2, 4, 1)
INSERT INTO
(CALL_HOUR,SITE_ID,API_ID,CALL_COUNT,ERR_COUNT)
VALUES(00:00-00:59, 3, 3, 7, 2)现在希望用sql语句产生如下形式的数据格式
HOUR API_ID SITE01_CALLCOUNT SITE01_ERRCOUNT SITE02_CALLCOUNT SITE02_ERRORCOUNT SITE03_CALLCOUNT SITE03_ERRCOUNT
00:00-00:59 1 3 2 2 1 9 4
HOUR API_ID SITE01_CALLCOUNT SITE01_ERRCOUNT SITE02_CALLCOUNT SITE02_ERRORCOUNT SITE03_CALLCOUNT SITE03_ERRCOUNT
00:00-00:59 2 2 1 5 3 4 1
HOUR API_ID SITE01_CALLCOUNT SITE01_ERRCOUNT SITE02_CALLCOUNT SITE02_ERRORCOUNT SITE03_CALLCOUNT SITE03_ERRCOUNT
00:00-00:59 3 4 3 6 3 7 2
大致知道怎么回事
先用row_number()函数对相同API_ID值的列赋以序号
然后利用这个序号
select case when rn=1 then .... end SITE01_CALLCOUNT ,
case when rn=2 then .... end SITE02_CALLCOUNT ,
.....
再group by api_id聚合一下就行了
具体代码你自己研究,我现在没空..
sum(decode(site_id,1,call_count)) site01_callcount,
sum(decode(site_id,1,err_count)) site01_errcount,
sum(decode(site_id,2,call_count)) site02_callcount,
sum(decode(site_id,2,err_count)) site02_errcount,
sum(decode(site_id,3,call_count)) site03_callcount,
sum(decode(site_id,3,err_count)) site03_callcount
from t
group by call_hour,api_id
order by call_hour,api_id