原创

工作中时常会用到的SQL语句实例

一、查询选店的所有关键字段

SELECT
info.foh_store_no as shopId, info.store_name,info.shop_type, biz.`day`,info.last_update_date,info.city,info.store_label,sc.transport_type,sc.provider_type,
info.latitude, info.longitude,
chn.online_status, chn.business_status,
biz.temp_close_status, biz.business_open, biz.business_time,
sc.normal_scopes, sc.shrink_scopes
FROM sse_store_info info
LEFT JOIN sse_store_channel chn ON info.foh_store_no = chn.foh_store_no
LEFT JOIN sse_channel_business_hour biz ON chn.foh_store_no = biz.foh_store_no AND chn.channel = biz.channel
LEFT JOIN sse_store_delivery_scope sc ON sc.foh_store_no = info.foh_store_no
WHERE 1=1
-- and chn.channel='mod'
-- AND info.foh_store_no IN ("54193","16631","29242","25492","63528","31567","17790","28083","30624","29922","53467","18828","23437")
AND info.foh_store_no IN (22511)
-- AND SC.provider_type = 2
-- AND SC.transport_type = 1
-- AND TO_DAYS(biz.`day`)=TO_DAYS(NOW())
-- and info.city like "%澳门%"
-- AND chn.online_status = 1
-- and chn.business_status = 1
-- and biz.business_open = "Y"
-- and biz.temp_close_status = "N"
ORDER BY biz.`day` DESC, sc.transport_type DESC

二、计算订单支付时间和完成时间的时间差（分钟）

所用到的关键字：TIMESTAMPDIFF

select creation_date,sale_order_id,order_type,foh_store_no,complete_time,pay_time,TIMESTAMPDIFF(MINUTE,pay_time,complete_time) as timediff from sse_sale_order_07
where foh_store_no=9060
and online_type=1
and sale_order_id like "720%"
and creation_date BETWEEN "2022-01-17 08:00:00" AND "2022-01-17 10:00:00"
ORDER BY creation_date ASC

三、按小时统计某家门店的线上订单

所用到的关键字：substr

select foh_store_no,count(distinct id) cnt , substr(creation_date,1,13) hours
from sse_sale_order_07 a
where foh_store_no=22081
and order_type in (301,1501)
group by substr(creation_date,1,13)
order by hours

四、查询选店日志（日志表和详情表）

select a.creation_date,a.order_latitude,a.order_longitude,a.foh_store_no,a.u,a.online_t,a.offline_t,a.vt,a.zt,a.final_load,a.formula FROM `sse_select_log_detail_14` a
WHERE a.foh_store_no in ("31830")
ORDER BY creation_date DESC,final_load DESC;

SELECT * from sse_select_log_05
where selected_store_no in ("30924","27695")
and creation_date BETWEEN "2022-01-05 09:30:00" AND "2022-01-05 10:30:00"
ORDER BY creation_date ASC

五、查询重复数据

所用到的关键字：having count

select * from sse_store_delivery_scope a where a.foh_store_no in (select foh_store_no from sse_store_delivery_scope group by foh_store_no having count(foh_store_no) > 1)
SELECT a.city,a.* from sse_store_info a where a.foh_store_no in（门店id）

六、查询电子围栏推送日志

select * from sse_log_syn a where a.log_type in ("COMMON_SCOPE","StoreDeliveryScope") AND a.creation_date BETWEEN "2022-02-25 00:00:00" AND "2022-02-25 12:00:00"

七、两列相减

所用到的关键字：ifnull

SELECT
ifnull(balance,0)-ifnull(allocate,0) as result,
b.warehouse_code,
b.balance,
b.allocate,
b.appending,
a.id AS cargoId,
a.CODE AS skuCode,
a.brand_name AS brandName,
a.brand_id AS brandId,
a.category_id AS categoryId,
a.category AS categoryName,
a.item_code AS itemCode,
a.item_name AS itemName,
a.tag_price AS price
FROM in_cargo a, in_cargo_storage b
WHERE
a.CODE = b.cargo_code
AND b.warehouse_code = 1029801
-- and a.code = "VPW22106516"
and ifnull(b.balance,0)-ifnull(b.allocate,0)>0
-- ORDER BY ifnull(b.balance,0)-ifnull(b.allocate,0) desc;

八、查询门店所属的渠道

所用到的关键字：locate

SELECT
T1.CODE,
T1.NAME,
T1.type,
T1.org_warhouse_code,
T1.channelCode,
T2.item_code,
T2.extension
FROM in_band_detail T2,
(
SELECT
a.org_code,
a. NAME,
a.org_warhouse_code,
a.brand_type,
a.type,
a. CODE,
a.channel_type_id,
a.distribution_type,
a.store_level,
b. CODE AS channelCode
FROM
sc_shop a,
bd_dict b
WHERE
a.channel_type_id = b.id
AND b. CODE IN ("G1", "G2", "G3", "G4", "G5","KC","QG")
) T1
WHERE
locate(
t1.channelCode,
t2.extension
) > 0
-- and T2.item_code in ("EVW20264088")
-- and T1.name in ("合生汇");

正文到此结束

所属分类：深度好文

本文标签： sql
版权声明： 本站原创文章，于2022年05月05日由小蜜蜂社区发布，转载请注明出处

工作中时常会用到的SQL语句实例

一、查询选店的所有关键字段

二、计算订单支付时间和完成时间的时间差（分钟）

三、按小时统计某家门店的线上订单

四、查询选店日志（日志表和详情表）

五、查询重复数据

六、查询电子围栏推送日志

七、两列相减

八、查询门店所属的渠道

热门推荐

相关文章

说给你听

本文目录

标签云

近期评论

网站信息