You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
371 lines
10 KiB
371 lines
10 KiB
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- 个人卡
|
|
CREATE TABLE tmp_cust_goods(
|
|
SELECT
|
|
r.wxMpOpenid,
|
|
d.customerSid,
|
|
d.bindDate,
|
|
d.giftbagSid,
|
|
d.serialNumber,
|
|
d.code,
|
|
s.goodsSid,
|
|
s.goodsNumber,
|
|
e.NAME AS goodsName,
|
|
'个人卡' cardtype
|
|
FROM lpk_giftcard d
|
|
LEFT JOIN lpk_customer r ON r.sid = d.customerSid
|
|
LEFT JOIN lpk_giftbag_goods s ON s.giftbagSid=d.giftbagSid
|
|
LEFT JOIN lpk_goods e ON e.sid = s.goodsSid
|
|
WHERE d.customerSid IS NOT NULL AND d.customerSid <> ''
|
|
ORDER BY r.wxMpOpenid
|
|
);
|
|
|
|
-- 企业卡
|
|
INSERT INTO tmp_cust_goods
|
|
SELECT
|
|
r.wxMpOpenid,
|
|
d.customerSid,
|
|
d.bindDate,
|
|
d.giftbagSid,
|
|
d.serialNumber,
|
|
d.code,
|
|
s.goodsSid,
|
|
s.goodsNumber,
|
|
e.NAME AS goodsName,
|
|
'企业卡' cardtype
|
|
FROM emp_card d
|
|
LEFT JOIN lpk_customer r ON r.sid = d.customerSid
|
|
LEFT JOIN lpk_giftbag_goods s ON s.giftbagSid=d.giftbagSid
|
|
LEFT JOIN lpk_goods e ON e.sid = s.goodsSid
|
|
WHERE d.customerSid IS NOT NULL AND d.customerSid <> ''
|
|
ORDER BY r.wxMpOpenid;
|
|
|
|
-- 购买卡
|
|
INSERT INTO tmp_cust_goods
|
|
SELECT
|
|
r.wxMpOpenid,
|
|
d.customerSid,
|
|
d.bindDate,
|
|
d.sid,
|
|
d.serialNumber,
|
|
d.code,
|
|
s.goodsSid,
|
|
s.goodsNumber,
|
|
e.NAME AS goodsName,
|
|
'购买卡' cardtype
|
|
FROM emp_card_gift d
|
|
LEFT JOIN lpk_customer r ON r.sid = d.customerSid
|
|
LEFT JOIN emp_card_gift_goods s ON s.empCardGiftSid=d.sid
|
|
LEFT JOIN lpk_goods e ON e.sid = s.goodsSid
|
|
WHERE d.customerSid IS NOT NULL AND d.customerSid <> '' AND d.isSenior = '1'
|
|
ORDER BY r.wxMpOpenid;
|
|
|
|
-- 转赠卡
|
|
INSERT INTO tmp_cust_goods
|
|
SELECT
|
|
r.wxMpOpenid,
|
|
d.customerSid,
|
|
d.bindDate,
|
|
d.sid,
|
|
d.serialNumber,
|
|
d.code,
|
|
s.goodsSid,
|
|
s.goodsNumber,
|
|
e.NAME AS goodsName,
|
|
'转赠卡' cardtype
|
|
FROM emp_card_gift d
|
|
LEFT JOIN lpk_customer r ON r.sid = d.customerSid
|
|
LEFT JOIN emp_card_gift_goods s ON s.empCardGiftSid=d.sid
|
|
LEFT JOIN lpk_goods e ON e.sid = s.goodsSid
|
|
WHERE d.customerSid IS NOT NULL AND d.customerSid <> '' AND d.isSenior = '2'
|
|
ORDER BY r.wxMpOpenid;
|
|
|
|
|
|
-- 预约提货，数量为负值
|
|
INSERT INTO tmp_cust_goods
|
|
SELECT
|
|
r.wxMpOpenid,
|
|
d.customerSid,
|
|
d.reserveDate,
|
|
d.sid,
|
|
d.storeSid,
|
|
d.cardCode,
|
|
s.goodsSid,
|
|
-s.goodsNumber AS goodsNumber,
|
|
e.NAME AS goodsName,
|
|
d.cardType cardtype
|
|
FROM lpk_reserve_order d
|
|
LEFT JOIN lpk_customer r ON r.sid = d.customerSid
|
|
LEFT JOIN lpk_reserve_order_goods s ON s.orderSid=d.sid
|
|
LEFT JOIN lpk_goods e ON e.sid = s.goodsSid
|
|
WHERE 1=1
|
|
ORDER BY r.wxMpOpenid;
|
|
|
|
|
|
-- 赠出的卡，数量为负值
|
|
INSERT INTO tmp_cust_goods
|
|
SELECT
|
|
r.wxMpOpenid,
|
|
d.empCardCustomerSid,
|
|
d.grantDate,
|
|
d.sid,
|
|
d.serialNumber,
|
|
d.code,
|
|
s.goodsSid,
|
|
-s.goodsNumber AS goodsNumber,
|
|
e.NAME AS goodsName,
|
|
'赠出' cardtype
|
|
FROM emp_card_gift d
|
|
LEFT JOIN lpk_customer r ON r.sid = d.empCardCustomerSid
|
|
LEFT JOIN emp_card_gift_goods s ON s.empCardGiftSid=d.sid
|
|
LEFT JOIN lpk_goods e ON e.sid = s.goodsSid
|
|
WHERE d.empCardCustomerSid IS NOT NULL AND d.empCardCustomerSid <> '' AND d.isSenior = '2'
|
|
ORDER BY r.wxMpOpenid;
|
|
|
|
select * from tmp_cust_goods;
|
|
|
|
-- 统计
|
|
INSERT INTO vegetable_cellar(sid,customerSid,goodsSid,goodsNumber)
|
|
SELECT
|
|
UUID(),
|
|
customerSid,
|
|
goodsSid,
|
|
SUM(goodsNumber) AS goodsNumber
|
|
FROM tmp_cust_goods
|
|
GROUP BY customerSid,goodsSid
|
|
HAVING goodsNumber>0
|
|
|
|
|
|
|
|
update vegetable_cellar vc set affiliation=(select brandId from lpk_goods lg where vc.goodsSid=lg.sid) where 1=1
|
|
|
|
|
|
|
|
聂金义:
|
|
默认名称为“惠享客户000142”
|
|
|
|
聂金义:
|
|
把“小主”换成“惠享客户”
|
|
SELECT * FROM lpk_customer WHERE nick LIKE '小主%'
|
|
|
|
SELECT * FROM lpk_customer WHERE nick IS NOT NULL
|
|
|
|
UPDATE lpk_customer set nick=REPLACE(nick,'小主','惠享客户') WHERE nick LIKE '小主%'
|
|
|
|
SELECT * FROM lpk_goods WHERE sid='ac146c0f-9002-4203-8f59-ff7c46d3159c'
|
|
|
|
|
|
SELECT * FROM lpk_goods WHERE sid IN (
|
|
SELECT goodsSid FROM vegetable_cellar WHERE affiliation IS NULL GROUP BY goodsSid
|
|
)
|
|
|
|
|
|
|
|
SELECT * FROM lpk_giftcard WHERE serialNumber='00000010'
|
|
|
|
|
|
SELECT * FROM lpk_customer WHERE sid='e0f2b745-8055-48bc-a506-ff09dd82ddb5'
|
|
|
|
|
|
SELECT * FROM lpk_giftbag WHERE sid='8e667618-63de-480c-ad1e-2747b76aa99b'
|
|
|
|
|
|
SELECT * FROM lpk_giftbag_goods lgg
|
|
LEFT join lpk_goods lg on lg.sid=lgg.goodsSid
|
|
WHERE lgg.giftbagSid='8e667618-63de-480c-ad1e-2747b76aa99b'
|
|
|
|
|
|
|
|
SELECT * FROM lpk_giftcard WHERE serialNumber in(
|
|
'00000001',
|
|
'00000002',
|
|
'00000003',
|
|
'00000004',
|
|
'00000005',
|
|
'00000006',
|
|
'00000007',
|
|
'00000008',
|
|
'00000009',
|
|
'00000010',
|
|
'00000011',
|
|
'00000012',
|
|
'00000013',
|
|
'00000014',
|
|
'00000015',
|
|
'00000016',
|
|
'00000017',
|
|
'00000018',
|
|
'00000019',
|
|
'00000020'
|
|
)
|
|
|
|
|
|
|
|
SELECT
|
|
r.wxMpOpenid,
|
|
serialNumber,customerSid,giftbagSid
|
|
FROM lpk_giftcard lg
|
|
LEFT JOIN lpk_customer r ON r.sid = lg.customerSid
|
|
WHERE serialNumber in(
|
|
'00000001',
|
|
'00000002',
|
|
'00000003',
|
|
'00000004',
|
|
'00000005',
|
|
'00000006',
|
|
'00000007',
|
|
'00000008',
|
|
'00000009',
|
|
'00000010',
|
|
'00000011',
|
|
'00000012',
|
|
'00000013',
|
|
'00000014',
|
|
'00000015',
|
|
'00000016',
|
|
'00000017',
|
|
'00000018',
|
|
'00000019',
|
|
'00000020'
|
|
)
|
|
"wxMpOpenid" "serialNumber" "customerSid" "giftbagSid"
|
|
"o81zC6xQKV_Rau7cntsaUwOkf9NM" "00000013" "c89f8631-e401-4010-a223-0caf3fabd5c6" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"o81zC6xQKV_Rau7cntsaUwOkf9NM" "00000015" "c89f8631-e401-4010-a223-0caf3fabd5c6" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"o81zC65z6tNd5L6ximemKJKBc-4w" "00000010" "e0f2b745-8055-48bc-a506-ff09dd82ddb5" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"o81zC610j5hKap2qQL_IxskermLY" "00000006" "a43573f1-e94f-455c-ad5d-3979bf56864e" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"o81zC610j5hKap2qQL_IxskermLY" "00000008" "a43573f1-e94f-455c-ad5d-3979bf56864e" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"o81zC6xZjXUAHJqbEkeHtec2vPPo" "00000001" "e157745c-856a-472d-a81e-2975312bf171" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"o81zC60V3ymrfjgK-BifvcyWfJBo" "00000014" "05ba58d6-f1f0-4f68-9bcc-62ceeaf4c088" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"o81zC6xfASEwu63AUvjeeJ6_t4oU" "00000002" "e569243c-1830-4316-8b4e-d4568b5a151d" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
\N "00000003" "" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
\N "00000004" "" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
\N "00000005" "" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
\N "00000007" "" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
\N "00000009" "" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
\N "00000011" "" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
\N "00000012" "" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
|
|
|
|
|
|
"serialNumber" "customerSid" "giftbagSid"
|
|
"00000001" "e157745c-856a-472d-a81e-2975312bf171" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"00000002" "e569243c-1830-4316-8b4e-d4568b5a151d" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"00000006" "a43573f1-e94f-455c-ad5d-3979bf56864e" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"00000008" "a43573f1-e94f-455c-ad5d-3979bf56864e" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"00000010" "e0f2b745-8055-48bc-a506-ff09dd82ddb5" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"00000013" "c89f8631-e401-4010-a223-0caf3fabd5c6" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"00000014" "05ba58d6-f1f0-4f68-9bcc-62ceeaf4c088" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
"00000015" "c89f8631-e401-4010-a223-0caf3fabd5c6" "8e667618-63de-480c-ad1e-2747b76aa99b"
|
|
|
|
SELECT COUNT(1) FROM vegetable_cellar; -- 970
|
|
|
|
select * from vegetable_cellar where customerSid='a43573f1-e94f-455c-ad5d-3979bf56864e'; -- 14
|
|
|
|
delete FROM vegetable_cellar where customerSid='a43573f1-e94f-455c-ad5d-3979bf56864e';
|
|
|
|
SELECT * FROM tmp_cust_goods WHERE customerSid='a43573f1-e94f-455c-ad5d-3979bf56864e';
|
|
|
|
|
|
INSERT INTO vegetable_cellar(sid,customerSid,goodsSid,goodsNumber)
|
|
SELECT
|
|
UUID(),
|
|
customerSid,
|
|
goodsSid,
|
|
SUM(goodsNumber) AS goodsNumber
|
|
FROM tmp_cust_goods
|
|
where customerSid='a43573f1-e94f-455c-ad5d-3979bf56864e' and serialNumber not in ('00000006','00000008')
|
|
GROUP BY customerSid,goodsSid
|
|
HAVING goodsNumber>0
|
|
|
|
|
|
SELECT COUNT(1) FROM vegetable_cellar; -- 970
|
|
|
|
select * from vegetable_cellar where customerSid='c89f8631-e401-4010-a223-0caf3fabd5c6'; -- 17
|
|
|
|
delete FROM vegetable_cellar where customerSid='c89f8631-e401-4010-a223-0caf3fabd5c6';
|
|
|
|
SELECT * FROM tmp_cust_goods WHERE customerSid='c89f8631-e401-4010-a223-0caf3fabd5c6';
|
|
|
|
|
|
INSERT INTO vegetable_cellar(sid,customerSid,goodsSid,goodsNumber)
|
|
SELECT
|
|
UUID(),
|
|
customerSid,
|
|
goodsSid,
|
|
SUM(goodsNumber) AS goodsNumber
|
|
FROM tmp_cust_goods
|
|
where customerSid='c89f8631-e401-4010-a223-0caf3fabd5c6' and serialNumber not in ('00000013','00000015')
|
|
GROUP BY customerSid,goodsSid
|
|
HAVING goodsNumber>0
|
|
|
|
|
|
SELECT COUNT(1) FROM vegetable_cellar; -- 958
|
|
|
|
|
|
select * from vegetable_cellar where customerSid='05ba58d6-f1f0-4f68-9bcc-62ceeaf4c088'; -- 14
|
|
|
|
delete FROM vegetable_cellar where customerSid='05ba58d6-f1f0-4f68-9bcc-62ceeaf4c088';
|
|
|
|
SELECT * FROM tmp_cust_goods WHERE customerSid='05ba58d6-f1f0-4f68-9bcc-62ceeaf4c088';
|
|
|
|
|
|
INSERT INTO vegetable_cellar(sid,customerSid,goodsSid,goodsNumber)
|
|
SELECT
|
|
UUID(),
|
|
customerSid,
|
|
goodsSid,
|
|
SUM(goodsNumber) AS goodsNumber
|
|
FROM tmp_cust_goods
|
|
where customerSid='05ba58d6-f1f0-4f68-9bcc-62ceeaf4c088' and serialNumber not in ('00000014')
|
|
GROUP BY customerSid,goodsSid
|
|
HAVING goodsNumber>0
|
|
|
|
|
|
delete FROM vegetable_cellar where customerSid='e157745c-856a-472d-a81e-2975312bf171';
|
|
|
|
INSERT INTO vegetable_cellar(sid,customerSid,goodsSid,goodsNumber)
|
|
SELECT
|
|
UUID(),
|
|
customerSid,
|
|
goodsSid,
|
|
SUM(goodsNumber) AS goodsNumber
|
|
FROM tmp_cust_goods
|
|
where customerSid='e157745c-856a-472d-a81e-2975312bf171' and serialNumber not in ('00000001')
|
|
GROUP BY customerSid,goodsSid
|
|
HAVING goodsNumber>0
|
|
|
|
|
|
delete FROM vegetable_cellar where customerSid='e569243c-1830-4316-8b4e-d4568b5a151d';
|
|
|
|
INSERT INTO vegetable_cellar(sid,customerSid,goodsSid,goodsNumber)
|
|
SELECT
|
|
UUID(),
|
|
customerSid,
|
|
goodsSid,
|
|
SUM(goodsNumber) AS goodsNumber
|
|
FROM tmp_cust_goods
|
|
where customerSid='e569243c-1830-4316-8b4e-d4568b5a151d' and serialNumber not in ('00000002')
|
|
GROUP BY customerSid,goodsSid
|
|
HAVING goodsNumber>0
|
|
|
|
|
|
delete FROM vegetable_cellar where customerSid='e0f2b745-8055-48bc-a506-ff09dd82ddb5';
|
|
|
|
INSERT INTO vegetable_cellar(sid,customerSid,goodsSid,goodsNumber)
|
|
SELECT
|
|
UUID(),
|
|
customerSid,
|
|
goodsSid,
|
|
SUM(goodsNumber) AS goodsNumber
|
|
FROM tmp_cust_goods
|
|
where customerSid='e0f2b745-8055-48bc-a506-ff09dd82ddb5' and serialNumber not in ('00000010')
|
|
GROUP BY customerSid,goodsSid
|
|
HAVING goodsNumber>0
|
|
|
|
|