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--简表索引
会员账务情况表