因为数据口径的更改,所以.强哥的SQL 比较好用.不会出麻烦. 总共有四个

日常记录下,好好看.

-- 2019年4月核销新客
SELECT
yzm2.consignee_phone AS `会员手机号码`,
yzm2.confirm_time AS `线上首购接单时间`,
yzm2.consign_time AS `线上首购核销时间`,
hcm.id AS 华创会员ID,
hcm.create_date AS 华创会员创建时间,
hcm.fisrt_shopping_time AS 华创会员首购时间
FROM
(
SELECT
tn.id,
tn.consignee_phone,
tn.confirm_time,
MIN( tn.order_time ) AS order_time,
tn.consign_time
FROM
mabao51.trade_onl AS tn
WHERE
tn.consignee_phone IS NOT NULL
AND tn.org_id NOT IN (4000, 4004)
GROUP BY
tn.consignee_phone
) AS yzm1,
(
SELECT
tn.id,
tn.consignee_phone,
tn.confirm_time,
MIN( tn.order_time ) AS order_time,
tn.consign_time
FROM
mabao51.trade_onl AS tn
WHERE
tn.consign_time IS NOT NULL
AND tn.consignee_phone IS NOT NULL
AND tn.org_id > 0
AND tn.org_id NOT IN (4000, 4004)
GROUP BY
tn.consignee_phone
) AS yzm2,
arm_changsha.ms_member_def AS hcm
WHERE
hcm.mobile = yzm1.consignee_phone
AND yzm1.consignee_phone= yzm2.consignee_phone
AND hcm.create_date >= yzm1.order_time
AND yzm2.consign_time BETWEEN "2019-04-01 00:00:00" AND "2019-05-01 00:00:00"

四月门店全部新客

-- 2019年4月41家门店全部新客
/*取出所有顾客的最早销售单的时间,然后判断是否在四月.是这个逻辑,首购判断.新客.去除一些沉默客户*/

SELECT
*
FROM
(
SELECT
gs.member_id,
gs.salesdepart_id,
MIN( gs.sales_date ) sales_date
FROM
arm_changsha.goods_sales AS gs
GROUP BY
gs.member_id
) AS gs2
LEFT JOIN arm_changsha.ms_member_def AS m ON m.id = gs2.member_id
LEFT JOIN (
SELECT
tn.org_id_erp
FROM
mabao51.trade_onl AS tn
WHERE
tn.org_id > 0
AND tn.org_id NOT IN (4000, 4004)
AND tn.order_time BETWEEN "2019-04-01 00:00:00" AND "2019-05-01 00:00:00"
GROUP BY
tn.org_id_erp
) AS yzo ON yzo.org_id_erp = gs2.salesdepart_id
WHERE
gs2.sales_date BETWEEN "2019-04-01 00:00:00"
AND "2019-05-01 00:00:00"
AND m.create_date > "2019-01-01 00:00:00"
AND yzo.org_id_erp IS NOT NULL

四月全部购买人数

-- 2019年4月41家门店全部购买人

SELECT
*
FROM
(
SELECT
gs.member_id,
gs.salesdepart_id,
gs.sales_date
FROM
arm_changsha.goods_sales AS gs
) AS gs2
LEFT JOIN (
SELECT
tn.org_id_erp
FROM
mabao51.trade_onl AS tn
WHERE
tn.org_id NOT IN (4000, 4004)
AND tn.org_id > 0
AND tn.order_time BETWEEN "2019-04-01 00:00:00" AND "2019-05-01 00:00:00"
GROUP BY
tn.org_id_erp
) AS yzo ON yzo.org_id_erp = gs2.salesdepart_id
WHERE
gs2.sales_date BETWEEN "2019-04-01 00:00:00"
AND "2019-05-01 00:00:00"
AND yzo.org_id_erp IS NOT NULL
GROUP BY
gs2.member_id

四月全部销售额

-- 2019年4月41家门店销售总额

SELECT
ROUND(
(
SUM( gsd.deal_price * gsd.sales_number ) - IFNULL( SUM( gsp.gift_balance ), 0 )
) / 10000,
2
) AS amount
FROM
(
SELECT
id,
salesdepart_id
FROM
goods_sales
WHERE
-- salesdepart_id = "11"
-- AND
sales_date BETWEEN "2019-04-01 00:00:00"
AND "2019-04-30 23:59:59"
) AS gs
LEFT JOIN goods_sales_detail AS gsd ON gsd.sales_id = gs.id
LEFT JOIN goods_sales_pay_detail gsp ON CONCAT( gsp.sales_id, '_', gsp.goods_id ) = CONCAT( gs.id, '_', gsd.goods_id )
LEFT JOIN (
SELECT
tn.org_id_erp
FROM
mabao51.trade_onl AS tn
WHERE
tn.org_id NOT IN ( 4000, 4004 )
AND tn.org_id > 0
AND tn.order_time BETWEEN "2019-04-01 00:00:00" AND "2019-05-01 00:00:00"
GROUP BY
tn.org_id_erp
) AS tn2 ON tn2.org_id_erp = gs.salesdepart_id
WHERE
tn2.org_id_erp IS NOT NULL