速求oracle语句，今天晚上要！谢谢

2012-04-09 09:33:30 288 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

浅分析Oracle语句优化规则 1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种： a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL 阅读详情

create table sell(

id int not null primary key ,

name varchar(100) not null,

dt date not null,

price decimal (10,2) not null
);

insert into sell(id, name, dt, price)
select 1, '香烟' ,to_date('2012-01-01 12:03:00','yyyy-MM-dd HH24:mi:ss'),12.00 from dual
union

select 2, '衣服' ,to_date('2012-03-01 12:03:00','yyyy-MM-dd HH24:mi:ss'),240.00 from dual
union
select 3, '手机' ,to_date('2012-04-01 12:03:00','yyyy-MM-dd HH24:mi:ss'),1200.00 from dual
union

select 4, '可乐' ,to_date('2012-08-01 12:03:00','yyyy-MM-dd HH24:mi:ss'),5.00 from dual
union
select 5, '啤酒' ,to_date('2012-01-01 12:03:00','yyyy-MM-dd HH24:mi:ss'),10.00 from dual

/*表一
季度消费金额
*/
**** ****
**** ****

/*表二
1季度 2季度 3季度 4季度
*/
**** **** **** ****
**** **** **** ****

...全文

Windows下TensorRT+YOLOv11部署避坑指南：从CUDA配置到模型加密全流程本文详细解析了在Windows平台部署YOLOv11与TensorRT的全流程，重点涵盖从CUDA环境配置、工程编译适配到模型加密保护的实战指南。基于wang-xinyu的tensorrtx仓库，提供了详细的版本选择、编译问题修复方案，并深入探讨了如何对TensorRT引擎进行加密，以实现高效且安全的端到端部署。阅读详情

lt1299416898

基础和管理 17,379 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

zlb_chen 2012-04-10

打赏
举报



 create table sell(



  id int not null primary key ,

   

  name varchar(100) not null,

   

  dt date not null,

   

  price decimal (10,2) not null

);



select to_char(dt,'mm') from sell 



insert into sell(id, name, dt, price)

select 1, '香烟' ,to_date('2012-01-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),12.00 from dual

union   



select 2, '衣服' ,to_date('2012-03-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),240.00 from dual

union   

select 3, '手机' ,to_date('2012-04-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),1200.00 from dual

union   



select 4, '可乐' ,to_date('2012-08-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),5.00 from dual

union   

select 5, '啤酒' ,to_date('2012-12-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),10.00 from dual

  



--1 

select jd,sum(price) as je 

from (select case  when to_char(dt,'mm')   between '01' and '03' then 1

                   when to_char(dt,'mm')   between '04' and '06' then 2 

                   when to_char(dt,'mm')   between '07' and '09' then 3

                   when to_char(dt,'mm')   between '10' and '12' then 4 end as jd,

                     price  from sell

       ) t

group by jd

order by jd;

--2

select sum(aa) 第1季度,sum(bb) 第2季度 ,sum(cc) 第3季度,sum(dd) 第4季度 from (

select case jd when 1 then je end as aa,case jd when 2 then je end as bb,

       case jd when 3 then je end as cc,case jd when 4 then je end as dd from ( 

         select jd,sum(price) as je 

from (select case  when to_char(dt,'mm')   between '01' and '03' then 1

                   when to_char(dt,'mm')   between '04' and '06' then 2 

                   when to_char(dt,'mm')   between '07' and '09' then 3

                   when to_char(dt,'mm')   between '10' and '12' then 4 end as jd,

                     price  from sell

       ) t

group by jd ) a  )b

大明湖畔_帅锅 2012-04-10

打赏
举报

[Quote=引用 6 楼的回复:]

不妥，N年的 1 季度怎么可以算在一起？
逻辑有问题
[/Quote]

构造的是2012年的月份然后提取的4个季度如果要计算多年的每个季度可以添加一个年份来分组

coolkisses 2012-04-10

打赏
举报

不妥，N年的 1 季度怎么可以算在一起？
逻辑有问题

大明湖畔_帅锅 2012-04-10

打赏
举报

这贴还没结？昨天发了2个结了一个？



select sum(nvl(q1,0)) 季度1,sum(nvl(q2,0)) 季度2,sum(nvl(q3,0)) 季度3,sum(nvl(q4,0)) 季度4

from

    (select case when to_char(c1,'q')=1 then sum(price) end q1, 

           case when to_char(c1,'q')=2 then sum(price) end q2,

           case when to_char(c1,'q')=3 then sum(price) end q3,

           case when to_char(c1,'q')=4 then sum(price) end q4

    from

      (select add_months(date'2011-12-01',level) c1

      from dual

      connect by level <= 12) a,sell b

    where to_char(c1,'yyyy-mm')=to_char(dt,'yyyy-mm')

    group by to_char(c1,'q'))



     季度1  季度2  季度3  季度4

--------------------------------------

1    262    1200    5    0

guo624587253 2012-04-10

打赏
举报

SELECT sum(CASE to_char(dt, 'Q')
WHEN '1' THEN price
else 0
END) "第一季度",
sum(CASE to_char(dt, 'Q')
WHEN '2' THEN price
else 0
END) "第2季度",
sum(CASE to_char(dt, 'Q')
WHEN '3' THEN price
else 0
END) "第3季度",
sum(CASE to_char(dt, 'Q')
WHEN '4' THEN price
else 0
END) "第4季度"
from sell ;

guo624587253 2012-04-10

打赏
举报

guo624587253 2012-04-10

打赏
举报

select to_char(dt, 'Q ') ,sum(price) from sell group by to_char(dt, 'Q ') ;

大明湖畔_帅锅 2012-04-09

打赏
举报



select to_char(c1,'q') 季度,nvl(sum(price),0) 消费金额 from

  (select add_months(date'2011-12-01',level) c1

  from dual

  connect by level <= 12) a left join sell b on to_char(c1,'yyyy-mm')=to_char(dt,'yyyy-mm')

group by to_char(c1,'q') 

order by to_char(c1,'q')





      季度    消费金额

----------------------------

1    1    262

2    2    1200

3    3    5

4    4    0

PS：新人注意结贴

ssqtjffcu 2012-04-09

打赏
举报

[Quote=引用 3 楼的回复:]
SQL code

/*表一
季度消费金额
*/
with quarter as --4个季度
(select level q1 from dual connect by level <= 4),
sell_q as --某年度下，各季度汇总
(select to_char(dt, 'Q') q2, sum(price) sum_p
from sell
……
[/Quote]
不错！！

larrychen_bmw 2012-04-09

打赏
举报



/*表一

季度 消费金额

*/

with quarter as --4个季度

 (select level q1 from dual connect by level <= 4),

sell_q as      --某年度下，各季度汇总

 (select to_char(dt, 'Q') q2, sum(price) sum_p

    from sell

   where dt between date '2012-01-01' and date '2012-12-31' + 0.99999

   group by to_char(dt, 'Q'))

select q1 季度, nvl(sum_P, 0) 消费金额

from quarter, sell_q

where q1 = q2 (+) 

order by 1;



季度	消费金额

1	262

2	1200

3	5

4	0









/*表二

1季度 2季度 3季度 4季度

*/  

with quarter as --4个季度

 (select level q1 from dual connect by level <= 4),

sell_q as --某年度下，各季度汇总

 (select to_char(dt, 'Q') q2, sum(price) sum_p

    from sell

   where dt between date '2012-01-01' and date '2012-12-31' + 0.99999

   group by to_char(dt, 'Q')),

ret as   --表1的结果

 (select q1 q, nvl(sum_P, 0) p

    from quarter, sell_q

   where q1 = q2(+))

--将表1结果转换成横向   

select (select p from ret where q = '1') 一季度,

       (select p from ret where q = '2') 二季度,

       (select p from ret where q = '3') 三季度,

       (select p from ret where q = '4') 四季度

  from dual;

 

一季度	二季度	三季度	四季度

262	1200	5	0

ssqtjffcu 2012-04-09

打赏
举报



select jd, sum(price)

  from (select id,

               name,

               dt,

               price,

               case

                 when dt between to_date('2012-01-01', 'yyyy-mm-dd') and

                      to_date('2012-04-01', 'yyyy-mm-dd') then

                  '1季度'

                 when dt between to_date('2012-04-01', 'yyyy-mm-dd') and

                      to_date('2012-07-01', 'yyyy-mm-dd') then

                  '2季度'

                 when dt between to_date('2012-07-01', 'yyyy-mm-dd') and

                      to_date('2012-10-01', 'yyyy-mm-dd') then

                  '3季度'

                 when dt between to_date('2012-10-01', 'yyyy-mm-dd') and

                      to_date('2013-01-01', 'yyyy-mm-dd') then

                  '4季度'

               end jd

          from sell)

 group by jd

JD    SUM(PRICE)

----- ----------

1季度        262

2季度       1200

3季度          5





select sum(case

             when dt between to_date('2012-01-01', 'yyyy-mm-dd') and

                  to_date('2012-04-01', 'yyyy-mm-dd') then

              price

             else

              0

           end) "1季度",

       sum(case

             when dt between to_date('2012-04-01', 'yyyy-mm-dd') and

                  to_date('2012-07-01', 'yyyy-mm-dd') then

              price

             else

              0

           end) "2季度",

       sum(case

             when dt between to_date('2012-07-01', 'yyyy-mm-dd') and

                  to_date('2012-10-01', 'yyyy-mm-dd') then

              price

             else

              0

           end) "3季度",

       sum(case

             when dt between to_date('2012-10-01', 'yyyy-mm-dd') and

                  to_date('2013-01-01', 'yyyy-mm-dd') then

              price

             else

              0

           end) "4季度"

  from sell t

 

       1季度        2季度        3季度        4季度

---------- ---------- ---------- ----------

       262       1200          5          0

速求oracle语句，今天晚上要！谢谢

相关推荐