Oracle的sql

查看表字段相关定义

select   *   from   all_col_comments   where   table_name=upper('recsheet') ;

按小时统计，按销售累加销售金额

 select s1.code,s1.name,s1.saleday,s1.salehour,s1.amount as hoursale, sum(s2.amount) as daysale from 
        
        （
                 select code,name,saleday,to_number(salehour) as salehour, sum(amount) as amount  FROM (
                        select  f.code,f.name,b.createddate,to_char(b.createddate,'yyyy-mm-dd') as saleday,to_char(b.createddate,'hh24') as salehour,
                       -- m.cardno ,m.name ,
                       a.amount
                      FROM salesheet b  
                      INNER JOIN SALESHEETGOODS a on a.guid = b.guid
                      inner join Membercard m on m.guid = b.memberid	     
                      inner JOIN channel f on (b.channelid = f.channelid)        
                      where  TO_DATE('2020-11-19','yyyy-mm-dd') <= b.SHEETDATE  and  TO_DATE('2020-11-29','yyyy-mm-dd') >= b.SHEETDATE and f.code in ('9901','9902')
                    )   s
                  group by code,name,saleday,salehour
                  order by code,saleday,salehour 
          ）s1
          join
          （
                  select code,name,saleday,to_number(salehour) as salehour, sum(amount) as amount  FROM (
                        select  f.code,f.name,b.createddate,to_char(b.createddate,'yyyy-mm-dd') as saleday,to_char(b.createddate,'hh24') as salehour,
                       -- m.cardno ,m.name ,
                       a.amount
                      FROM salesheet b  
                      INNER JOIN SALESHEETGOODS a on a.guid = b.guid
                      inner join Membercard m on m.guid = b.memberid	     
                      inner JOIN channel f on (b.channelid = f.channelid)
                      where  TO_DATE('2020-11-19','yyyy-mm-dd') <= b.SHEETDATE  and  TO_DATE('2020-11-29','yyyy-mm-dd') >= b.SHEETDATE and f.code in ('9901','9902')
                  )   s
                  group by code,name,saleday,salehour
                  order by code,saleday,salehour 
          ） s2
          on  s1.code = s2.code and s1.name = s2.name and s1.saleday = s2.saleday
          where s1.salehour >= s2.salehour and s1.code = s2.code and s1.name = s2.name and s1.saleday = s2.saleday
          
          group by s1.code,s1.name,s1.saleday,s1.salehour,s1.amount
          
          order by  s1.code,s1.name,s1.saleday,s1.salehour

查看积分
select * from viewvipintegral where cardno=‘c87dfd31bd7541aab61a1f47465886fc’
viewvipintegral 是视图

查看表的定义
SELECT DBMS_METADATA.GET_DDL(‘TABLE’,‘TABLE_NAME’) FROM DUAL;
查看视图的定义语句

SELECT DBMS_METADATA.GET_DDL(‘VIEW’,‘VIEW_NAME’) FROM DUAL;
表名跟视图名称均要大写。

如 SELECT DBMS_METADATA.GET_DDL(‘VIEW’,‘VIEWVIPINTEGRAL’) FROM DUAL;

 CREATE OR REPLACE FORCE VIEW "REGENTA"."VIEWVIPINTEGRAL" ("CARDNO", "INTEGRAL") AS 
  select
/*
------------------------------------------
说明: 会员积分
返回:
建立人员: wk
建立日期：2015-05-04
修改 wk 2015-11-16 调整积分的算法
修改wk 2016-04-28 增加零售订单 与退货的积分
修改wk 2016-05-25 增加零售订单抵减积分
------------------------------------------
*/
--合计积分 = 期初 + 销售累计积分 + 转入积分 - 转出积分 + 充值积分 - 抵减 + 调整 - 清理(年度累计 + 年末抵减)
 cardno,Sum(balance) INTEGRAL From (
 --上线期初
 Select score balance,to_char(cardno) cardno
  From viewblanceintegral vl
 Union All
  --销售
  Select nvl(sum(SLG.INTEGRAL),0) balance,to_char(SL.cardno) cardno
       from SaleList sl
       Inner Join SALELISTGOODS SLG On SL.GUID=SLG.GUID AND SL.status=1 And sl.cardno Is Not Null
       Inner Join viewblanceintegral vl On sl.cardno=vl.cardno And sl.saledate > vl.sheetdate
       Group By SL.cardno
  Union All
	select nvl(sum(og.addintegral),0) balance,to_char(ob.buyer_cardno) cardno
	        from o2o_trade  ot
         inner join o2o_tradegoods og on og.guid = ot.guid
         inner join O2O_TradeBuyer ob on ob.guid=ot.guid
				  Inner Join viewblanceintegral vl On ob.buyer_cardno=vl.cardno And trunc(ot.sheetdate) > vl.sheetdate
         where ot.status=1
         group by ob.buyer_cardno
	Union all
  --积分转卡--转入卡号
  select nvl(sum(balance),0) balance,to_char(incardno) cardno
      from IntegralTrans it
      Inner Join viewblanceintegral vl On it.incardno=vl.cardno And it.sheetdate > vl.sheetdate
         where status=1 Group By incardno
  Union All
  --积分转卡--转出卡号
  select nvl(sum(balance),0)* -1 balance,to_char(outcardno) cardno
    from IntegralTrans it
      Inner Join viewblanceintegral vl On it.outcardno=vl.cardno And it.sheetdate > vl.sheetdate
         where status=1 Group By outcardno
  Union All
  --充值产生积分 充值积分
  select nvl(sum(integralvalue),0) balance,to_char(ce.cardno)
    from CardRecharge ce
    Inner Join viewblanceintegral vl On ce.cardno=vl.cardno And ce.sheetdate > vl.sheetdate
         where status=1 Group By ce.cardno
  Union All
  --积分明细 --type类型 1=积分兑换/2=抵减积分/4=零售退货产生积分/5=零售订单付款扣减积分
  select nvl(sum(Integral),0) * -1 balance,Guid cardno
         from MemberIntegrationDetail md
        Inner Join viewblanceintegral vl On md.guid=vl.cardno And md.saledate> vl.sheetdate
         where type IN (1,2,4,5)  and DirectFlag=-1
         Group By Guid
 Union All
  --积分调整
  select nvl(sum(Integral),0) balance,Guid cardno
         from MemberIntegrationDetail md
         Inner Join viewblanceintegral vl On md.guid=vl.cardno And md.saledate> vl.sheetdate
         where type=3 Group By Guid
 Union All
  --积分清零
  select nvl(sum(integration)+sum(ClearINTEGR),0) * -1 balance, mc.cardno
     from memberintegrationClear mc
     Inner Join viewblanceintegral vl On mc.cardno =vl.cardno And mc.sheetdate > vl.sheetdate
        Group By mc.cardno
        ) Group By cardno

select fn_calintegralvalue('c87dfd31bd7541aab61a1f47465886fc') from dual;

查看函数的定义
SELECT * FROM USER_PROCEDURES;

SELECT * FROM USER_SOURCE WHERE NAME = 'FN_CALINTEGRALVALUE'; 

名称均要大写

Oracle的NVL函数用法
从两个表达式返回一个非 null 值。

语法

NVL(eExpression1, eExpression2)

参数
eExpression1, eExpression2

如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。

返回值类型

字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值

说明

在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。

select nvl(a.name,‘空得’) as name from student a join school b on a.ID=b.ID

注意：两个参数得类型要匹配

 1 SELECT           T.D_FDATE,
 2                        T.VC_ZHCODE,
 3                        NVL(SUM(T.F_FZQSZ), 0) f_price_b,
 4                        NVL(SUM(T.F_FZQCB), 0) f_cost_b,
 5                        NVL(SUM(T.F_FGZ_ZZ), 0) f_gz_b,
 6                        NVL(SUM(T.F_FYZQSZ), 0) f_price_Y,
 7                        NVL(SUM(T.F_FYZQCB), 0) f_cost_Y,
 8                        NVL(SUM(T.F_FYGZ_ZZ), 0) f_gz_Y,
 9                        T.VC_SOURCE,
10                        SYSDATE d_updatetime
11                   FROM GZ_FUND_GZB T            

比如这样的判断就很重要啦，因为你不知道哪一行是 is not null 的，也不知道接下来是否要对这个单元格进行运算操作，因此，不能给列填 null，就给它一个 0 ，便于查看，也便于运算。

查询导购业绩报表

 select a.*
       FROM salesheet b 
      INNER JOIN SalesManApportion a on a.saleGuid = b.guid
      INNER JOIN Businessperson  bp on bp.guid = a.businessId
      
      where b.manualid='330311922203050003e'
      --- salesheet  销售表
      --- SalesManApportion  导购业绩分摊表
      --- Businessperson 导购信息表

分组后取每组的前10

       select 
        rankNum AS rankNum,
        scode as branchCode,
        businesspatternname,
			  goodsNo AS sku,
			  goodsName AS description,
			  saleNum AS quantity,
			  amount AS soldValue
	   		from (
        
		      	 select row_number() over ( partition BY scode  ORDER BY amount desc) as rankNum,businesspatternname,scode,goodsNo,goodsName,saleNum,amount from (
                          
				      	SELECT
                f.code as scode,
                f.name as 
                bp.businesspatternname,
						    e.goodsno AS goodsNo,
					      e.goodsname AS goodsName,
					      sum(a.quantity) AS saleNum,
				        sum(a.amount) AS amount
				       FROM SALESHEETGOODS a
						    LEFT JOIN salesheet b on a.guid = b.guid
					      LEFT JOIN goods e on (a.goodsid = e.goodsid)
					      LEFT JOIN channel f on (b.channelid = f.channelid)
					      LEFT JOIN CHANNEL C3 ON (f.Parentid = C3.CHANNELID)
					      Left Join vgoodscategorysales sa On e.salescategoryid=sa.GoodsSaleCategoryid
					      left join vgoodscategorystory br on br.GoodsStoryCategoryid = e.STORYCATEGORYID
					      left join channeltypelist ct on f.channeltypeid = ct.id
                left join BusinessPatternlist bp  on bp.id= f.BUSINESSPATTERNID
                left join varea  va on va.id = f.AREAID	
			      	 where				      	 				      	 
			         
							-- and sa.goodssalecategory1 = #{goodssalecategory1}
									
							TO_DATE('2022-01-01','yyyy-mm-dd') <= b.SHEETDATE
              -- and bp.businesspatternname='直营'
              and sa.goodssalecategory1='套件'
		        
		        group by f.code,bp.businesspatternname,e.goodsno,e.goodsname having sum(a.amount) > 0 
            
            )
            
            ) 
		        where rankNum  < 6

查看表锁
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY, c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL
from v$locke{d}_{o}bjecta,db{a}_{o}bjectsb,v$session c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid ;

alter system kill session ‘245,43584’;

select object_name,machine,s.program,s.sid,
s.sid||‘,’||s.serial# as killid,
p.spid as os_pid,
s.sql_address,
s.status,
decode(l.locked_mode, 0, ‘None’,
1, ‘Null’,
2, ‘Row-S’,
3, ‘Row-X’,
4, ‘Share’,
5, ‘S/Row-X’,
6, ‘Exclusive’, ‘Unknown’) locked_mode,
s.username,
s.process,
s.sql_id
from gv$locke{d}_{o}bjectl,db{a}_{o}bjectso,gv$session s,gv$process p
where l.object_id=o.object_id and l.session_id=s.sid
and s.paddr=p.addr
and object_name=upper(‘&tablename’) and o.owner=upper(‘&owner’) order by 1;

select object_name,machine,s.program,s.sid,
s.sid||‘,’||s.serial# as killid,
p.spid as os_pid,
s.sql_address,
s.status,
decode(l.locked_mode, 0, ‘None’,
1, ‘Null’,
2, ‘Row-S’,
3, ‘Row-X’,
4, ‘Share’,
5, ‘S/Row-X’,
6, ‘Exclusive’, ‘Unknown’) locked_mode,
s.username,
s.process,
s.sql_id
from gv$locke{d}_{o}bjectl,db{a}_{o}bjectso,gv$session s,gv$process p
where l.object_id=o.object_id and l.session_id=s.sid
and s.paddr=p.addr
and object_name=upper(‘&tablename’) and o.owner=upper(‘&owner’) order by 1;"

---

这个语句可以查看那个表锁了，需要输入表名和用户名

select decode(request,0,‘Holder:’,‘Waiter:’)||s.inst_id||‘:’||s.sid||‘,’||s.serial# sess, s.BLOCKING_SESSION,s.status,
id1,id2,lmode,request,l.type,ctime,s.username,s.sql_id,s.PREV_SQL_ID,s.event,s.SQL_EXEC_START
–,s.service_name
from gv$lockl,gv$session s
where (id1,id2,l.type) in
(select id1,id2,type from gv$lock where request>0)
and l.sid=s.sid
and l.inst_id=s.inst_id
order by id1,ctime desc,request;
/

select decode(request,0,‘Holder:’,‘Waiter:’)||s.inst_id||‘:’||s.sid||‘,’||s.serial# sess, s.BLOCKING_SESSION,s.status,
id1,id2,lmode,request,l.type,ctime,s.username,s.sql_id,s.PREV_SQL_ID,s.event,s.SQL_EXEC_START
–,s.service_name
from gv$lockl,gv$session s
where (id1,id2,l.type) in
(select id1,id2,type from gv$lock where request>0)
and l.sid=s.sid
and l.inst_id=s.inst_id
order by id1,ctime desc,request;
/"

---

这个SQL看数据库存在的锁信息，持锁会话