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 =''

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

SELECT

*

FROM

vipcard_ar_detail

WHERE

cardno =''

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 ='';

#会员卡消费和充值记录

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--简表索引