lipinka_admin/docs/数据查询/18033776066数据.sql

2024-02-01 15:13:34
20
o81zC67mJFwaMBPxrgYpFuDGNNSY
惠享客户0003851
18033776066
桃园支行

SELECT 
  lc.wxMpOpenid,
  lc.nick,
  lc.mobile,
  lc.sid
FROM lpk_customer lc 
WHERE lc.mobile='18033776066'
;
"wxMpOpenid"	"nick"	"mobile"	"sid"
"o81zC67mJFwaMBPxrgYpFuDGNNsY"	"惠享客户0003851"	"18033776066"	"e43901e8-4185-4a45-8ccb-e077c6e38e1e"

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='e43901e8-4185-4a45-8ccb-e077c6e38e1e'


-- 个人卡
	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='e43901e8-4185-4a45-8ccb-e077c6e38e1e' 
-- 企业卡
	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='e43901e8-4185-4a45-8ccb-e077c6e38e1e' 

-- 购买卡
	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='e43901e8-4185-4a45-8ccb-e077c6e38e1e' 

-- 转赠卡（旧数据）
	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='e43901e8-4185-4a45-8ccb-e077c6e38e1e' 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 ='e43901e8-4185-4a45-8ccb-e077c6e38e1e' 
	
	
	-- 预约提货，(旧数据)
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='e43901e8-4185-4a45-8ccb-e077c6e38e1e' 

	-- 预约单
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='e43901e8-4185-4a45-8ccb-e077c6e38e1e' 
	-- 赠出的卡，数量为负值
	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='e43901e8-4185-4a45-8ccb-e077c6e38e1e' AND d.isSenior = '2' 
	
	-- 赠出记录
		SELECT 
		   tr.createTime,
			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 ='e43901e8-4185-4a45-8ccb-e077c6e38e1e'