[西软xms]会员卡消费和余额情况表

 

 

select * from vipcard;
#过滤卡类型财富卡（CFK）权益卡（QYK）幸福卡（XFK）
select id from vipcard where (card_class ='XFK' OR card_class ='CFK' OR card_class ='QYK');

SELECT * FROM vipcard where no ='99900362'
SELECT  id,no,name,araccnt1,ref FROM vipcard where no ='99900362'

SELECT
*
FROM
vipcard_ar_detail
WHERE
cardno ='99900362'


select * from vipcard_ar_detail WHERE ref <> '会员信息导入入账'

SELECT * from vipcard_ar_detail WHERE accnt ='A0000001208'
SELECT changed,cid,date,cardno,charge,credit,balance,ref from vipcard_ar_detail WHERE accnt ='A0000001208'



vipcard.id = vipcard_ar_detail.cid //唯一值

SELECT  id,no,name,araccnt1,ref FROM vipcard where no ='99900362';
#会员卡消费和充值记录
SELECT
vipcard_ar_detail.number,
vipcard_ar_detail.cid,
vipcard_ar_detail.date AS 操作时间,
vipcard_ar_detail.cardno AS 卡号,
vipcard_ar_detail.charge AS 消费,
vipcard_ar_detail.credit AS 充值,
vipcard_ar_detail.balance AS 余额,
vipcard_ar_detail.ref AS 备注
FROM
vipcard_ar_detail
WHERE
vipcard_ar_detail.accnt = 'A0000001208'
ORDER BY
vipcard_ar_detail.number ASC;

#会员卡余额 通过判断number的最大值
--SELECT cid,date,cardno,charge,credit,balance,ref from vipcard_ar_detail WHERE accnt ='A0000001208' AND number=(select MAX(number) from vipcard_ar_detail)
SELECT balance from vipcard_ar_detail WHERE accnt ='A0000001208' AND number=(select MAX(number) from vipcard_ar_detail)



select id from vipcard where (card_class ='XFK' OR card_class ='CFK' OR card_class ='QYK')

##过滤后等会员卡卡消费情况列表
SELECT
b.id,
a.cid,
a.number,
a.date AS `操作时间`,
a.cardno AS `卡号`,
b.name AS `姓名`,
a.charge AS `消费`,
a.credit AS `充值`,
a.balance AS `余额`,
a.ref AS `备注`
FROM
vipcard AS b ,
vipcard_ar_detail AS a
WHERE
b.id = a.cid AND
(b.card_class = 'XFK' OR
b.card_class = 'CFK' OR
b.card_class = 'QYK')
ORDER BY
`卡号` ASC,
`操作时间` ASC 







SELECT
b.id,
a.cid,
a.number,
a.date ,
a.cardno ,
b.name,
a.charge ,
a.credit,
a.balance ,
a.ref
FROM
vipcard AS b ,
vipcard_ar_detail AS a
WHERE
b.id = a.cid AND
(b.card_class = 'XFK' OR
b.card_class = 'CFK' OR
b.card_class = 'QYK')
ORDER BY
a.cardno ASC,
a.date ASC 







SELECT
b.id,
a.cid,
a.number,
a.date ,
a.cardno ,
b.name,
a.charge ,
a.credit,
a.balance ,
a.ref
FROM
vipcard AS b ,
vipcard_ar_detail AS a
WHERE
b.id = a.cid AND
(b.card_class = 'QYK')
ORDER BY
a.cardno ASC,
a.date ASC 

 

正规军解法（加了面板属性进行筛选卡等级）

select a.cardno,v.type,v.card_class,a.log_date,a.pccode,a.charge,a.credit,a.ref from vipcard_ar_detail a 
left join vipcard v on v.no=a.cardno 
where
1=1  
${card_type_sql}  ${card_class_sql} 
order by a.cardno asc;

select code,descript from vipcard_type--简表索引

select code,descript from vipcard_card_type--简表索引

会员账务情况表