Sql_server的查询如何转换为oracle的？

[yjdabc]

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的，坑死我了，请大家帮我看看，谢谢！！！

[sunshine0212]

顶一下,学习中.

[yjdabc]

create or replace procedure AllStatOfMonth
(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
) p

end 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,m12

Error: 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.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,m12

Error: 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.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,m12

Error: 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一点都不熟悉，实在是没有头绪，大家帮我看看吧，谢谢！

[「已注销」]

对楼上的兄弟表示下尊敬！

[Kobayashi]

你这样的SQL看得头痛。没有一点格式的。

[opps_zhou]

提供下表结构，你是不是想做一个汇总查询？

[yjdabc]

对，就是一个汇总

[yjdabc]

上面的Sql其实可以简化为：
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也可以！
先谢谢了！

[zhoulirong14]

sql语句在这两种数据库都差不多一样的吧。。。

[yjdabc]

HitCount表结构

HITCOUNTID NUMBER(10) N 访问统计ID（主键）
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数

[快溜]

这字段名字起得也太。。。