正文
oracle怎么查季度,oracle按季度统计
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
oracle数据库中怎么查询当前年每月的天数，按自然月统计，谢谢！
楼上的只统计了一个月的吧
WITH t AS
(SELECT ROWNUM
FROM DUAL
CONNECT BY ROWNUM = 12)
SELECT TO_CHAR (LAST_DAY (TO_DATE ( TO_CHAR (SYSDATE, 'YYYY')
|| LPAD (TO_CHAR (ROWNUM), 2, 0),
'YYYYMM'
)
),
'YYYY-MM'
)
|| '月',
TO_CHAR (LAST_DAY (TO_DATE ( TO_CHAR (SYSDATE, 'YYYY')
|| LPAD (TO_CHAR (ROWNUM), 2, 0),
'YYYYMM'
)
),
'DD'
)
|| '天'
FROM t
直接全选复制粘贴运行
ORACLE查询
SELECT year AS '年',
(SELECT sale
FROM Sells
WHERE (quarter = 1) AND (year = Sells_1.year)) AS '1季度',
(SELECT sale
FROM Sells AS Sells_4
WHERE (quarter = 2) AND (year = Sells_1.year)) AS '2季度',
(SELECT sale
FROM Sells AS Sells_3
WHERE (quarter = 3) AND (year = Sells_1.year)) AS '3季度',
(SELECT sale
FROM Sells AS Sells_2
WHERE (quarter = 4) AND (year = Sells_1.year)) AS '4季度'
FROM Sells AS Sells_1
GROUP BY year
sql2005下做的，oracle下应该也可以用
--------------------------------------------------
SELECT 生产厂商,
SUM(CASE WHEN 产品类型 = '电话' THEN 1 ELSE 0 END) AS '电话',
SUM(CASE WHEN 产品类型 = '烟' THEN 1 ELSE 0 END) AS '烟',
SUM(CASE WHEN 产品类型 = '酒' THEN 1 ELSE 0 END) AS '酒'
FROM 表名
GROUP BY 生产厂商
sql2005下做的，oracle下应该也可以用
oracle查询12个月份
首先表需要有一个字段，是日期型的。
举例：test表，字段有
name varchar2(10),
value number(6),
vdate date.
查询2010年12月份的数据
select * from test where to_char(vdate,'yyyy-mm')='2010-12';
oracle中查询某一季度数据的sql?
一般情况下，你的明细数据表都有个日期字段来表明你明细的时间，
数据量比较小可以这样
select sum(...) from tabname where to_char(时间,'q')=你想查询的季度
如果数据量比较大，建议你根据明细表建立一个快速刷新的物化视图，物化视图是根据季度、星期等等的预先统计数据，到时候你要的数据直接从物化视图中查询即可
ORACLE怎么查各个季度的销售额？
SQL select * from orderr;
PRODID ORDID ORDERDATA
---------- ---------- --------------
1 100 01-1月 -12
2 200 01-1月 -12
SQL select * from PRODUCT;
PRODID PROD
---------- ----
1 车
2 床
SQL select * from ORDERDETAIL;
PRODID PRICE COUNT ORDID
---------- ---------- ---------- ----------
1 50000 10 100
2 30000 15 200
SQL select "PRODUCT"."PRODNA" "产品名称","ORDERR"."ORDERDATA" "订购日期","ORDERDETAIL"."PRICE"*"ORDERDETAIL"."COUNT" "产品销售额",
2 to_char("ORDERR"."ORDERDATA",'q') "订购季度"
3 from PRODUCT,ORDERDETAIL,ORDERR
4 where "PRODUCT"."PRODID"="ORDERDETAIL"."PRODID" AND "ORDERDETAIL"."ORDID"="ORDERR"."ORDID"
5 group by "ORDERR"."ORDERDATA","ORDERDETAIL"."PRICE"*"ORDERDETAIL"."COUNT",to_char("ORDERR"."ORDERDATA",'q'),"PRODUCT"."PRODNA";
产品 订购日期 产品销售额 订购季度
---- -------------- ---------- ----------
床 01-1月 -12 450000 1
车 01-1月 -12 500000 1
有什么问题？
另外说一下：
1、你“产品名称”前面的双引号有问题。
2、在双引号引用中是必须区分大小写的。
3、orderdata前的表名呢？
