继续学习代码，有想学的加关注点赞收藏[通俗易懂]

select

–a.sktno 款台,a.jlbh 小票,vipid,a.jysj 交易时间,c.rydm 收银员号,c.PERSON_NAME 收银员,

–b.inx,

d.spcode 货号,b.barcode 条码,d.name 品名,d.lsdj 后台零售价,sum(b.xssl) 销售数量,sum(b.xsje) 销售金额

from xsjl a,xsjlc b,ryxx c,spbarcode d

where a.sktno=b.sktno and a.jlbh=b.jlbh

–and a.sktno=47

and a.sky=c.PERSON_ID

and b.barcode=d.barcode(+)

and b.xssl>100

group by d.spcode,b.barcode ,d.name,d.lsdj

–and d.spcode=102091468

–and d.lsdj>b.lsdj

order by d.name;

select a.sktno 款台,a.jlbh 小票,vipid,a.jysj 交易时间,c.rydm 收银员号,c.PERSON_NAME 收银员,

b.inx,d.spcode 货号,b.barcode 条码,d.name 品名,d.lsdj 后台零售价,b.lsdj 款台零售价,b.xssl 销售数量,b.xsje 销售金额

from xsjl a,xsjlc b,ryxx c,spbarcode d

where a.sktno=b.sktno and a.jlbh=b.jlbh

–and a.sktno=47

and a.sky=c.PERSON_ID

and b.barcode=d.barcode(+)

and b.xssl>10

–and d.spcode=102091468

–and d.lsdj>b.lsdj

order by a.sktno,a.jlbh,b.inx;

八．缺货明细

select e.group_no 采购号,g.group_name 采购名,e.supplier 供应商号,e.item 货号,e.descr 描述,e.stock 库存,e.dms DMS,e.last_received 最后到货日,e.last_sold 最后销售日

from erp_jxc_qh e,groups g

where e.location=100108 and e.run_date=’&日期’ and e.group_no=g.group_no order by e.group_no,e.supplier

查数据中心畅销品

select count(*) from cmx_item_loc_info@rms where loc=100108 and quick_seller_type=’Y’;

select count(*) from erp_item_loc where quick_seller_type=’Y’ and location in (100108);

查询小票明细

select to_char(trunc(a.jysj),’yyyy-mm-dd’) 日期,

to_char(a.jysj,’HH24:MI:SS’) 时间,

(SELECT CUR_VAL FROM BFCSLS8.BFCONFIG WHERE JLBH=0) 店号,

a.sktno 机台,

a.jlbh 发票号,

d.rydm 收银员号,

d.PERSON_NAME 收银员,

c.spcode 货号,

b.barcode 过机条码,

c.name 品名,

b.lsdj 零售价,

b.xssl 销量,

b.xsje 销售金额

from bf_pos8.xsjl_cx a,

bf_pos8.xsjlc_cx b,

spbarcode c,

ryxx d

where a.sktno=b.sktno and a.jlbh=b.jlbh

and b.barcode=c.barcode

and a.sky=d.PERSON_ID

and trunc(a.jysj)>=to_date(&开始日期,’yyyymmdd’)

and trunc(a.jysj)<=to_date(&结束日期,’yyyymmdd’)

–and c.spcode=&货号

and a.sktno=&款台号

and d.rydm=&收银员号

–and a.jlbh=&小票号

order by to_char(trunc(a.jysj),’yyyy-mm-dd’),a.sktno,a.jlbh,b.inx;

鲁花压榨玉米油900ml/瓶 收银员绩效

select (select cur_val from bfconfig where jlbh=0) 店号,c.rydm 收银员编号,c.person_name 收银员,spcode 货号,d.barcode 条码,d.name 品名,

d.lsdj 零售单价,sum(xssl) 销售数量,sum(b.xsje) 销售金额

from SKTXSJL a,bf_pos8.SKTXSJLC b,BFPUB_CS.RYXX c,spxx_jb d

where a.sktno=b.sktno and a.jlbh=b.JLBH

and a.sky=c.person_id

and b.sp_id=d.sp_id

and B.BARCODE in (6916168618909)

and trunc(a.jysj) BETWEEN to_date(&开始日期,’yyyymmdd’) AND TO_DATE(&结束日期,’yyyymmdd’)

group by c.rydm,c.person_name,spcode,d.barcode,d.name,d.lsdj

order by c.rydm

select ‘金兴店’ 店名,

item 商品,

descr 商品描述,

decode(sale_qty,0,null,round(sale_amount/sale_qty,2)) 平均售价,

gor_qty 进货数量,

sale_qty 销售数量,

stock_on_hand 库存数量,

return_qty 退货数量,

tran_date 日期

from erp_jxc_item

where item in (800039383,800279710) and

tran_date between ‘2017.03.01’ and ‘2017.05.22’ and

location=100108

order by item,tran_date;

粽子 收银员绩效

select a.sktno 款台,a.jlbh 小票,vipid,a.jysj 交易时间,c.rydm 收银员号,c.PERSON_NAME 收银员,

b.inx,d.spcode 货号,b.barcode 条码,d.name 品名,d.lsdj 后台零售价,b.lsdj 款台零售价,b.xssl 销售数量,b.xsje 销售金额

from sktxsjl a,sktxsjlc b,ryxx c,spbarcode d

where a.sktno=b.sktno and a.jlbh=b.jlbh and d.spcode=803756895

and a.sky=c.PERSON_ID and b.barcode=d.barcode(+)

and trunc(jysj) between to_date(&日期1,’yyyymmdd’) and to_date(&日期2,’yyyymmdd’)

order by a.sktno,a.jlbh,b.inx

粽子 按供应商查

select supplier,item,descr,sum(sale_qty),sum(sale_amount) from erp_jxc_item where supplier=21013451 and location=100108

and tran_date between to_date(20170501,’yyyymmdd’) and to_date(20170531,’yyyymmdd’)

group by item,descr,supplier

having sum(sale_qty)<>0

order by descr

select group_no,sum(unvat_sale_amount),sum(sale_amount),to_char(tran_date,’yyyymm’) from erp_jxc_item

where location =100108 and tran_date>=’2017.01.01′ and group_no in (101,102,103,104,201)

group by group_no,to_char(tran_date,’yyyymm’)

order by to_char(tran_date,’yyyymm’)

select (select cur_val from bfconfig where jlbh=0) 店号,A.SKTNO 款台号,a.jlbh 小票号,a.jysj 时间,c.rydm 收银员编号,c.person_name 收银员,spcode 货号,d.barcode 条码,d.name 购物袋品名,

d.lsdj 零售单价,sum(xssl) 销售数量,sum(b.xsje) 销售金额

from SKTXSJL a,bf_pos8.SKTXSJLC b,BFPUB_CS.RYXX c,spxx_jb d

where a.sktno=b.sktno and a.jlbh=b.JLBH

and a.sky=c.person_id

and b.sp_id=d.sp_id

AND SPCODE IN (800017187)

–and B.BARCODE in (6907992440057)

and trunc(a.jysj)>=to_date(&开始日期,’yyyymmdd’)

group by c.rydm,c.person_name,spcode,d.barcode,d.name,d.lsdj,a.sktno,a.jlbh,a.jysj