SELECT
'IP' [Type],
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],
([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]) [Total]
FROM(
SELECT SUM([1]) [1], SUM([2]) [2],SUM([3]) [3],SUM([4]) [4],SUM([5]) [5],SUM([6]) [6],SUM([7]) [7],SUM([8]) [8],SUM([9]) [9],SUM([10]) [10],SUM([11]) [11],SUM([12]) [12]
FROM(
SELECT [Year],ISNULL([1],0) [1],ISNULL([2],0) [2],ISNULL([3],0) [3],ISNULL([4],0) [4],ISNULL([5],0) [5],ISNULL([6],0) [6],ISNULL([7],0) [7],ISNULL([8],0) [8],ISNULL([9],0) [9],ISNULL([10],0) [10],ISNULL([11],0) [11],ISNULL([12],0) [12]
FROM [HitCount]
PIVOT(SUM(IPCount) FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) t
WHERE [Year]=@year
) t
GROUP BY [Year]
) t本人对oracle一窍不通,基于sql_server开发了一个程序,客户变卦却要oracle的,坑死我了,请大家帮我看看,谢谢!!!
'IP' [Type],
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],
([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]) [Total]
FROM(
SELECT SUM([1]) [1], SUM([2]) [2],SUM([3]) [3],SUM([4]) [4],SUM([5]) [5],SUM([6]) [6],SUM([7]) [7],SUM([8]) [8],SUM([9]) [9],SUM([10]) [10],SUM([11]) [11],SUM([12]) [12]
FROM(
SELECT [Year],ISNULL([1],0) [1],ISNULL([2],0) [2],ISNULL([3],0) [3],ISNULL([4],0) [4],ISNULL([5],0) [5],ISNULL([6],0) [6],ISNULL([7],0) [7],ISNULL([8],0) [8],ISNULL([9],0) [9],ISNULL([10],0) [10],ISNULL([11],0) [11],ISNULL([12],0) [12]
FROM [HitCount]
PIVOT(SUM(IPCount) FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) t
WHERE [Year]=@year
) t
GROUP BY [Year]
) t本人对oracle一窍不通,基于sql_server开发了一个程序,客户变卦却要oracle的,坑死我了,请大家帮我看看,谢谢!!!
解决方案 »
- 一个关于表连接查询的问题(oracle)
- ZHS16GBK导入US7ASCII字符集问题
- oracle 9i 不支持子查询中带order by 吗?
- 急问各位:我想在存储过程中执行一批处理文件,这个存储过程怎么写呀
- 求一SQL,怎么算出库中所有表记录不为空的表
- Oracle初学者初衷!--请管理员给置顶
- 我要学developer2000,谁有实用一点的资料?请help
- 初學XML,請問怎麼跟ORACLE連接,用XMLSPY?它支持Oracle8i麼?
- ORA-03113: 通信通道的文件结束??是怎么回事啊???高分相送!!
- studio在哪,找不着没法建表。。。。
- linux下oracle插入中文问题
- 实现多条件自由组合查询Oracle数据库如何设计?具体是表、存储过程
SELECT SUM([1]) [1]
FROM(
SELECT [Year],ISNULL([1],0) [1]
FROM [HitCount]
PIVOT(SUM(IPCount) FOR [Month] IN ([1])) t
WHERE [Year]=@year
) t
GROUP BY [Year]
将它转换为oracle也可以!
先谢谢了!
CHANNELID NUMBER(10) N 频道ID
NEWSID NUMBER(10) N 新闻ID
YEAR NUMBER(10) N 访问年
MONTH NUMBER(10) N 访问月
DAY NUMBER(10) N 访问日
IPCOUNT NUMBER(10) N IP数
PVCOUNT NUMBER(10) N PV数
(pYear in int)
as
begin
select 'IP' Type,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12
,(m1+m2+m3+m4+m5+m6+m7+m8+m9+m10+m11+m12) as Total
from (
select year
,sum(case when month = 1 then ipcount else 0 end) as m1
,sum(case when month = 2 then ipcount else 0 end) as m2
,sum(case when month = 3 then ipcount else 0 end) as m3
,sum(case when month = 4 then ipcount else 0 end) as m4
,sum(case when month = 5 then ipcount else 0 end) as m5
,sum(case when month = 6 then ipcount else 0 end) as m6
,sum(case when month = 7 then ipcount else 0 end) as m7
,sum(case when month = 8 then ipcount else 0 end) as m8
,sum(case when month = 9 then ipcount else 0 end) as m9
,sum(case when month = 10 then ipcount else 0 end) as m10
,sum(case when month = 11 then ipcount else 0 end) as m11
,sum(case when month = 12 then ipcount else 0 end) as m12
from hitcount where year = pYear group by year
) t
union all
select 'PV' Type,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12
,(m1+m2+m3+m4+m5+m6+m7+m8+m9+m10+m11+m12) as Total
from (
select year
,sum(case when month = 1 then PVCount else 0 end) as m1
,sum(case when month = 2 then PVCount else 0 end) as m2
,sum(case when month = 3 then PVCount else 0 end) as m3
,sum(case when month = 4 then PVCount else 0 end) as m4
,sum(case when month = 5 then PVCount else 0 end) as m5
,sum(case when month = 6 then PVCount else 0 end) as m6
,sum(case when month = 7 then PVCount else 0 end) as m7
,sum(case when month = 8 then PVCount else 0 end) as m8
,sum(case when month = 9 then PVCount else 0 end) as m9
,sum(case when month = 10 then PVCount else 0 end) as m10
,sum(case when month = 11 then PVCount else 0 end) as m11
,sum(case when month = 12 then PVCount else 0 end) as m12
from hitcount where year = pYear group by year
) pend AllStatOfMonth;
这是我自己写的,在pl/sql创建了此存储过程,但上面有个X,有3个错误提示:
第一个是:
Compilation errors for PROCEDURE CMS1_SA.ALLSTATOFMONTH
Error: PL/SQL: ORA-00933: SQL 命令未正确结束
Line: 41
Text: ) p
Error: PL/SQL: SQL Statement ignored
Line: 5
Text: select 'IP' Type,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12Error: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 42
Text: end AllStatOfMonth;
第二个是:
Compilation errors for PROCEDURE CMS1_SA.ALLSTATOFMONTHError: PL/SQL: ORA-00933: SQL 命令未正确结束
Line: 41
Text: ) pError: PL/SQL: SQL Statement ignored
Line: 5
Text: select 'IP' Type,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12Error: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 42
Text: end AllStatOfMonth;第三个是:Compilation errors for PROCEDURE CMS1_SA.ALLSTATOFMONTHError: PL/SQL: ORA-00933: SQL 命令未正确结束
Line: 41
Text: ) pError: PL/SQL: SQL Statement ignored
Line: 5
Text: select 'IP' Type,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12Error: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 42
Text: end AllStatOfMonth;
Google了很多,但对Oracle一点都不熟悉,实在是没有头绪,大家帮我看看吧,谢谢!