查看表字段相关定义
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
l
o
c
k
e
d
o
b
j
e
c
t
a
,
d
b
a
o
b
j
e
c
t
s
b
,
v
locked_object a,dba_objects b, v
lockedobjecta,dbaobjectsb,vsession 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
l
o
c
k
e
d
o
b
j
e
c
t
l
,
d
b
a
o
b
j
e
c
t
s
o
,
g
v
locked_object l, dba_objects o,gv
lockedobjectl,dbaobjectso,gvsession 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
l
o
c
k
e
d
o
b
j
e
c
t
l
,
d
b
a
o
b
j
e
c
t
s
o
,
g
v
locked_object l, dba_objects o,gv
lockedobjectl,dbaobjectso,gvsession 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
l
o
c
k
l
,
g
v
lock l,gv
lockl,gvsession 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
l
o
c
k
l
,
g
v
lock l,gv
lockl,gvsession 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看数据库存在的锁信息,持锁会话