店大侠数据库

cokan

2022 年 09 月 21 日

23 次浏览

暂无评论

5670字数

默认分类

店大侠数据库

diandaxia

dbo.top_area (城市地址表)
top_attrs (未发货订单)
top_exception_word （等待审核归档名）
top_export_excel （未知订单）
top_item （商品库）
top_item_outlog （出库订单）
top_item_sku （SKU表）
top_order （商品表）
top_trade （订单表）
- top_trade_id （序号）
- top_tid （订单编号）
- top_sku_id (线上商品编号)
- top_short_title （商品简称）
- top_outer_iid （商品编码）
- top_outer_sid （商品二级编码）
- top_status （发货状态）
  - WAIT_SELLER_SEND_GOODS （未发货）
  - WAIT_BUYER_CONFIRM_GOODS （已发货）
- top_exception_status （审核状态）
  - FALSE （无）
  - True （待审核）

数据库查询

查询未发货订单产品数量

SELECT
   top_short_title,top_outer_iid,top_outer_sid,
   SUM( top_num ) AS number 
FROM
   dbo.top_order 
WHERE 
  top_tid 
IN 
   (SELECT top_tid from dbo.top_trade WHERE top_status = 'WAIT_SELLER_SEND_GOODS' AND top_exception_status = 'FALSE') 
GROUP BY
   top_short_title,top_outer_iid,top_outer_sid 
HAVING
   SUM( top_num ) >= 1 
ORDER BY
   number DESC;

查询未发货产品订单数量

SELECT
   top_short_title,top_outer_iid,top_outer_sid,
   count( top_outer_sid ) AS number 
FROM
   dbo.top_order 
WHERE 
  top_tid 
IN 
   (SELECT top_tid from dbo.top_trade WHERE top_status = 'WAIT_SELLER_SEND_GOODS' AND top_exception_status = 'FALSE') 
GROUP BY
   top_short_title,top_outer_iid,top_outer_sid 
HAVING
   count( top_outer_sid ) >= 1 
ORDER BY
   number DESC;

查询当日发货数据

SELECT 
   top_short_title,top_outer_iid,top_outer_sid,
SUM 
   (top_num) AS '数量' ,SUM (top_shifu) AS '金额'
FROM 
   dbo.top_order 
WHERE 
   top_tid IN 
   (SELECT top_tid from dbo.top_trade WHERE  Convert(varchar(10),[top_print_logistics_time],120) = Convert(varchar(10),getDate(),120))
--(SELECT top_tid from dbo.top_trade WHERE top_print_logistics_time >= '2022-09-22 00:00:00')
--(SELECT top_tid from dbo.top_trade Where Year([top_print_logistics_time]) = Year(GETDATE()))
--
--   top_short_title = '亮肌莹润精华液'or top_short_title = '3D双面洁面刷'or top_short_title = '温和卸妆水'or top_short_title = '胶原肽光丝绒口红'
-- top_outer_iid = '682723706774'
GROUP BY
   top_short_title,top_outer_iid,top_outer_sid
HAVING
   count( top_num ) >= 1
ORDER BY
  '数量' DESC;

查询所有订单产品

SELECT 
   top_short_title,top_outer_iid,top_outer_sid,
SUM 
   (top_num) AS '数量' ,SUM (top_shifu) AS '金额'
FROM 
   dbo.top_order 
WHERE 
   top_tid IN 
   (SELECT top_tid from dbo.top_trade where top_print_logistics_status=1)
GROUP BY
   top_short_title,top_outer_iid,top_outer_sid
HAVING
   count( top_num ) >= 1
ORDER BY
  top_outer_sid DESC;

查询指定商品的销售数据

SELECT 
   top_short_title,top_outer_iid,top_outer_sid,
SUM 
   (top_num) AS '数量' ,SUM (top_shifu) AS '金额'
FROM 
   dbo.top_order 
WHERE 
   top_short_title = '亮肌莹润精华液'or top_short_title = '3D双面洁面刷'or top_short_title = '温和卸妆水'or top_short_title = '胶原肽光丝绒口红'
-- top_outer_iid = '682723706774'
GROUP BY
   top_short_title,top_outer_iid,top_outer_sid
HAVING
   count( top_num ) >= 1
ORDER BY
  top_outer_sid DESC;

查询当月订单

SELECT * from dbo.top_trade Where Month([top_print_logistics_time]) = Month(GETDATE());

查询未发货订单（不包含待审核）

SELECT * FROM dbo.top_order WHERE top_tid IN 
(SELECT top_tid from dbo.top_trade WHERE top_status = 'WAIT_SELLER_SEND_GOODS' AND top_exception_status = 'FALSE') ;

查询指定订单编号明细

SELECT * from dbo.top_order WHERE top_tid = '2900471546759726416';

查询指定订单编号订单

SELECT * from dbo.top_trade WHERE top_tid = '2900973927385200659';

查询未发货指定商品二级编码订单数量（不包含待审核）

SELECT COUNT(*) FROM dbo.top_order WHERE top_tid IN 
(SELECT top_tid from dbo.top_trade WHERE top_status = 'WAIT_SELLER_SEND_GOODS' AND top_exception_status = 'FALSE') 
AND top_outer_sid = 'SX314-01' ;

查询指定商品二级编码所有订单

SELECT * FROM dbo.top_order WHERE top_outer_sid='SX314-01';

查询指定商品二级编码所有订单并包含面膜的订单

select * from dbo.top_order where top_outer_sid = 'HM101-02'or top_outer_iid  LIKE '%面膜%';

查询指定实付价格并 top_num_iid 不为空的订单

select * from dbo.top_order where top_shifu  = '19.8' and top_num_iid <> '' ;

查询商品二级编码为空的所有订单

SELECT * from dbo.top_order where top_outer_sid ='';

查询指定商品标题并 top_outer_sid 不为空的订单

SELECT * FROM dbo.top_order WHERE top_title = '樱花物语钻石无痕化妆刷便携粉底散粉腮红修容刷干湿两用不易吃粉' AND top_outer_sid = '';

更新

1.给指定商品标题并二级编码为空的订单替换商品编码、二级编码、商品简称

UPDATE dbo.top_order SET top_outer_iid = '赠品面膜' ,top_outer_sid = 'HM101-02',top_short_title = '赠品面膜' WHERE top_title = '【官方赠品】 面膜 数量有限 /送完即止 /' AND top_outer_sid = '';

2.给指定商品编码、二级编码的订单替换商品编码、商品简称

UPDATE dbo.top_order SET top_outer_iid = '赠品面膜' ,top_short_title = '赠品面膜' WHERE top_outer_iid = '赠品大麻叶面膜' AND top_outer_sid = 'HM101-02';

3.给指定二级编码并商品编码不为'赠品面膜'的订单替换商品编码

UPDATE dbo.top_order SET top_outer_iid = '赠品面膜' WHERE top_outer_sid ='HM101-02' AND top_outer_iid <> '赠品面膜';

4.查询所有洗脸刷销售数量

SELECT
   top_short_title,top_outer_iid,top_outer_sid,
   count( top_outer_sid ) AS number 
FROM
   dbo.top_order 
WHERE 
  top_tid 
IN 
   (SELECT top_tid 
    from dbo.top_order 
where top_outer_sid = 'SX314-01'or top_outer_sid = 'SX314-02'
OR top_outer_sid = 'SX314-01+02'OR top_outer_sid = 'SX314-01*2'OR top_outer_sid = 'SX314-02*2') 
and top_outer_sid = 'SX314-01'or top_outer_sid = 'SX314-02'
OR top_outer_sid = 'SX314-01+02'OR top_outer_sid = 'SX314-01*2'OR top_outer_sid = 'SX314-02*2'
GROUP BY
   top_short_title,top_outer_iid,top_outer_sid 
HAVING
   count( top_outer_sid ) >= 1 
ORDER BY
   number DESC;

店大侠数据库