236 lines
12 KiB
SQL
236 lines
12 KiB
SQL
|
||
惠享云菜窖
|
||
家庭菜窖70001457
|
||
70001457
|
||
提货码20240109155151547430
|
||
提货密码: 825239
|
||
曹路颖
|
||
|
||
|
||
SELECT
|
||
lgc.bindDate,
|
||
lgc.serialNumber,
|
||
lgc.code,
|
||
lgc.customerSid,
|
||
lc.wxMpOpenid,
|
||
lc.nick,
|
||
lc.mobile,
|
||
lg.name,
|
||
lgg.goodsNumber
|
||
FROM lpk_giftcard lgc
|
||
LEFT JOIN lpk_giftbag_goods lgg ON lgg.giftbagSid=lgc.giftbagSid
|
||
LEFT JOIN lpk_goods lg ON lg.sid=lgg.goodsSid
|
||
LEFT JOIN lpk_customer lc ON lc.sid=lgc.customerSid
|
||
WHERE lgc.code='20240109155151547430'
|
||
;
|
||
"bindDate" "serialNumber" "code" "customerSid" "wxMpOpenid" "nick" "mobile" "name" "goodsNumber"
|
||
"2024-01-21 10:47:33" "70001457" "20240109155151547430" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "张北土豆（限购200斤）" "6"
|
||
"2024-01-21 10:47:33" "70001457" "20240109155151547430" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "沙窝萝卜" "1"
|
||
"2024-01-21 10:47:33" "70001457" "20240109155151547430" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "纯红薯粉条" "2"
|
||
"2024-01-21 10:47:33" "70001457" "20240109155151547430" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "北方白菜（限购200斤）" "8"
|
||
"2024-01-21 10:47:33" "70001457" "20240109155151547430" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "泥坑藕" "1"
|
||
"2024-01-21 10:47:33" "70001457" "20240109155151547430" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "西瓜红蜜薯" "4"
|
||
"2024-01-21 10:47:33" "70001457" "20240109155151547430" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "52度良作红薯" "1"
|
||
|
||
SELECT
|
||
lc.wxMpOpenid,
|
||
lc.nick,
|
||
lc.mobile,
|
||
lg.name,
|
||
vc.goodsNumber
|
||
from vegetable_cellar vc
|
||
LEFT JOIN lpk_customer lc ON lc.sid=vc.customerSid
|
||
LEFT JOIN lpk_goods lg ON lg.sid=vc.goodsSid
|
||
WHERE
|
||
vc.customerSid='4225d713-f0d7-4ed9-93f9-92511ebef52c'
|
||
"wxMpOpenid" "nick" "mobile" "name" "goodsNumber"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "张北土豆（限购200斤）" "3"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "北方白菜（限购200斤）" "5"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "泥坑藕" "2"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "西瓜红蜜薯" "3"
|
||
|
||
|
||
-- 个人卡
|
||
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='4225d713-f0d7-4ed9-93f9-92511ebef52c'
|
||
"wxMpOpenid" "customerSid" "bindDate" "giftbagSid" "serialNumber" "code" "goodsSid" "goodsNumber" "goodsName" "cardtype"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-21 10:47:33" "50ee9567-174e-4114-8127-e75884808598" "70001457" "20240109155151547430" "61873ef1-2262-4cc5-8ee5-c266c16b15bf" "6" "张北土豆（限购200斤）" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-29 09:55:45" "99b39d43-382b-4053-8842-cd3b9714a17e" "70004401" "20240124143210729431" "61873ef1-2262-4cc5-8ee5-c266c16b15bf" "5" "张北土豆（限购200斤）" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-21 10:47:33" "50ee9567-174e-4114-8127-e75884808598" "70001457" "20240109155151547430" "99b2dd79-5773-44c3-9a6e-a67c763ee245" "1" "沙窝萝卜" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-29 09:55:45" "99b39d43-382b-4053-8842-cd3b9714a17e" "70004401" "20240124143210729431" "99b2dd79-5773-44c3-9a6e-a67c763ee245" "2" "沙窝萝卜" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-21 10:47:33" "50ee9567-174e-4114-8127-e75884808598" "70001457" "20240109155151547430" "93773d0a-d749-42dc-b03a-63a9a0f6c6ff" "2" "纯红薯粉条" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-21 10:47:33" "50ee9567-174e-4114-8127-e75884808598" "70001457" "20240109155151547430" "d3821a05-a28e-4d13-84b9-904211f97542" "8" "北方白菜（限购200斤）" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-29 09:55:45" "99b39d43-382b-4053-8842-cd3b9714a17e" "70004401" "20240124143210729431" "d3821a05-a28e-4d13-84b9-904211f97542" "7" "北方白菜（限购200斤）" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-21 10:47:33" "50ee9567-174e-4114-8127-e75884808598" "70001457" "20240109155151547430" "cc88a7d2-4a7b-42fe-8958-292cd421a3e5" "1" "泥坑藕" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-29 09:55:45" "99b39d43-382b-4053-8842-cd3b9714a17e" "70004401" "20240124143210729431" "cc88a7d2-4a7b-42fe-8958-292cd421a3e5" "2" "泥坑藕" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-29 09:55:45" "99b39d43-382b-4053-8842-cd3b9714a17e" "70004401" "20240124143210729431" "b67167a1-d6f1-4e17-80b3-f1aac7e4e4fc" "1" "玉田包尖白菜" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-21 10:47:33" "50ee9567-174e-4114-8127-e75884808598" "70001457" "20240109155151547430" "88743284-b3b8-489f-9a96-720c23d74587" "4" "西瓜红蜜薯" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-29 09:55:45" "99b39d43-382b-4053-8842-cd3b9714a17e" "70004401" "20240124143210729431" "88743284-b3b8-489f-9a96-720c23d74587" "5" "西瓜红蜜薯" "个人卡"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-21 10:47:33" "50ee9567-174e-4114-8127-e75884808598" "70001457" "20240109155151547430" "26442a48-23c1-4cfd-a156-bad7a94cc141" "1" "52度良作红薯" "个人卡"
|
||
|
||
-- 企业卡
|
||
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='4225d713-f0d7-4ed9-93f9-92511ebef52c'
|
||
|
||
-- 购买卡
|
||
SELECT
|
||
oo.payTime,
|
||
oo.totalTee,
|
||
oo.openId,
|
||
lc.nick,
|
||
lc.mobile,
|
||
ood.goodsName,
|
||
ood.partNumber
|
||
FROM ord_order oo
|
||
LEFT JOIN lpk_customer lc ON lc.sid = oo.userSid
|
||
LEFT JOIN ord_order_detail ood ON ood.orderSid=oo.sid
|
||
LEFT JOIN lpk_goods e ON e.sid = ood.goodsSid
|
||
WHERE oo.payStatus=4
|
||
AND oo.userSid='4225d713-f0d7-4ed9-93f9-92511ebef52c'
|
||
|
||
-- 转赠卡（旧数据）
|
||
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='4225d713-f0d7-4ed9-93f9-92511ebef52c' AND d.isSenior = '2'
|
||
|
||
-- 转赠记录
|
||
SELECT
|
||
tr.recipientTime,
|
||
lc.wxMpOpenid,
|
||
lc.nick,
|
||
lc.mobile,
|
||
lg.name,
|
||
trgd.goodsNumber
|
||
FROM transfer_records tr
|
||
LEFT JOIN lpk_customer lc ON lc.sid = tr.recipientSid
|
||
LEFT JOIN transfer_records_goods_details trgd ON trgd.orderSid=tr.sid
|
||
LEFT JOIN lpk_goods lg ON lg.sid = trgd.goodsSid
|
||
WHERE tr.recipientSid ='4225d713-f0d7-4ed9-93f9-92511ebef52c'
|
||
|
||
|
||
-- 预约提货，(旧数据)
|
||
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 d.customerSid='4225d713-f0d7-4ed9-93f9-92511ebef52c'
|
||
|
||
-- 预约单
|
||
SELECT
|
||
r.wxMpOpenid,
|
||
d.customerSid,
|
||
d.reserveDate,
|
||
d.sid,
|
||
s.goodsSid,
|
||
-s.goodsNumber AS goodsNumber,
|
||
e.NAME AS goodsName
|
||
FROM vege_cellar_reserve_order d
|
||
LEFT JOIN lpk_customer r ON r.sid = d.customerSid
|
||
LEFT JOIN vege_cellar_reserve_details s ON s.orderSid=d.sid
|
||
LEFT JOIN lpk_goods e ON e.sid = s.goodsSid
|
||
WHERE d.customerSid='4225d713-f0d7-4ed9-93f9-92511ebef52c'
|
||
"wxMpOpenid" "customerSid" "reserveDate" "sid" "goodsSid" "goodsNumber" "goodsName"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "7e2bf790-8204-4346-a92a-627a11668212" "61873ef1-2262-4cc5-8ee5-c266c16b15bf" "-1" "张北土豆（限购200斤）"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "1e76d9a3-5e6f-497d-8763-aaaaa2d62fac" "61873ef1-2262-4cc5-8ee5-c266c16b15bf" "-1" "张北土豆（限购200斤）"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "7e2bf790-8204-4346-a92a-627a11668212" "99b2dd79-5773-44c3-9a6e-a67c763ee245" "-1" "沙窝萝卜"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "1e76d9a3-5e6f-497d-8763-aaaaa2d62fac" "99b2dd79-5773-44c3-9a6e-a67c763ee245" "-1" "沙窝萝卜"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "1e76d9a3-5e6f-497d-8763-aaaaa2d62fac" "93773d0a-d749-42dc-b03a-63a9a0f6c6ff" "-1" "纯红薯粉条"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "7e2bf790-8204-4346-a92a-627a11668212" "d3821a05-a28e-4d13-84b9-904211f97542" "-1" "北方白菜（限购200斤）"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "1e76d9a3-5e6f-497d-8763-aaaaa2d62fac" "d3821a05-a28e-4d13-84b9-904211f97542" "-1" "北方白菜（限购200斤）"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "370c0232-192b-4c7d-bcbf-5f4329e26c8a" "b67167a1-d6f1-4e17-80b3-f1aac7e4e4fc" "-1" "玉田包尖白菜"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "7e2bf790-8204-4346-a92a-627a11668212" "88743284-b3b8-489f-9a96-720c23d74587" "-1" "西瓜红蜜薯"
|
||
"o81zC69vqMWg1p0iccj2Rup5LJ7I" "4225d713-f0d7-4ed9-93f9-92511ebef52c" "2024-01-30 00:00:00" "1e76d9a3-5e6f-497d-8763-aaaaa2d62fac" "88743284-b3b8-489f-9a96-720c23d74587" "-1" "西瓜红蜜薯"
|
||
|
||
-- 赠出的卡，数量为负值
|
||
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='4225d713-f0d7-4ed9-93f9-92511ebef52c' AND d.isSenior = '2'
|
||
|
||
-- 赠出记录
|
||
SELECT
|
||
tr.recipientTime,
|
||
lc.wxMpOpenid,
|
||
lc.nick,
|
||
lc.mobile,
|
||
lg.name,
|
||
-trgd.goodsNumber
|
||
FROM transfer_records tr
|
||
LEFT JOIN lpk_customer lc ON lc.sid = tr.customerSid
|
||
LEFT JOIN transfer_records_goods_details trgd ON trgd.orderSid=tr.sid
|
||
LEFT JOIN lpk_goods lg ON lg.sid = trgd.goodsSid
|
||
WHERE tr.customerSid ='4225d713-f0d7-4ed9-93f9-92511ebef52c'
|
||
"recipientTime" "wxMpOpenid" "nick" "mobile" "name" "-trgd.goodsNumber"
|
||
"2024-02-02 14:14:00" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "张北土豆（限购200斤）" "-6"
|
||
"2024-02-02 14:14:00" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "沙窝萝卜" "-1"
|
||
"2024-02-02 14:14:00" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "纯红薯粉条" "-1"
|
||
"2024-02-02 14:14:00" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "北方白菜（限购200斤）" "-8"
|
||
"2024-02-02 14:46:51" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "泥坑藕" "-1"
|
||
"2024-02-02 14:14:00" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "西瓜红蜜薯" "-4"
|
||
"2024-02-02 14:14:00" "o81zC69vqMWg1p0iccj2Rup5LJ7I" "惠享客户000716" \N "52度良作红薯" "-1"
|
||
|