sql练习记录

三表关联如果字段为0则表示是散客卡

select a.shop_id as id,b.shop_name,a.balance,a.point,
(IF(a.card_type_id<>0,concat(c.card_type,"(测试环境测试11)"),'散客卡(测试环境测试11)')) as card from members a INNER JOIN
shops b on a.shop_id=b.id LEFT JOIN card_types c on a.card_type_id=c.id where a.mobile_phone=11111111111;

select中if判断的使用：

select a.arword_name , a.id,b.psy_coin,c.nickname,
if((select for_arword_view from pay_free_strategy where status!=0)=1,1,
if((select a.end_date from pay_vip_record a LEFT JOIN um_consumer b on b.id=a.csid where b.phone_no=xxxx GROUP BY a.end_date desc limit 1)>=NOW(),1,
if(b.psy_coin=0,1,
if(c.pay_desc<>0,1,0)))) as is_play from cm_arword a
LEFT JOIN pay_strategy b on a.price_sid=b.id
LEFT JOIN (select u.id,u.nickname,o.target,o.target_name,o.pay_desc,o.create_date
from um_consumer u LEFT JOIN um_watch_record o on u.id=o.csid where date(o.create_date) = curdate() and u.phone_no=xxx) as c
on a.id=c.target
where a.cid="614310764227854336" and a.audit_status=1 and a.del_flag=0

select a.id,a.video_name,a.cid,image_uri,b.psy_coin,
if(b.psy_coin=0,1,
(if(((NOW()>=(select start_time from pay_free_strategy where status=1 and for_arword_view=1)
and NOW() <=(select end_time from pay_free_strategy where status=1 and for_arword_view=1))),1,
(if(NOW()<=(select c.end_date from pay_vip_record c LEFT JOIN um_consumer d on c.csid=d.id where d.phone_no="1310000000" ORDER BY c.end_date desc limit 1),1,
(if(a.id in(select product_id from pay_trade_order e LEFT JOIN um_consumer f on e.csid=f.id where f.phone_no="1310000000"),1,0))
)))))
as is_play
from cm_video a
LEFT JOIN pay_strategy b on a.play_price_sid=b.id where a.cid="614313147179728896" and a.video_type=1 and a.del_flag=0
and a.id in (select target_id from cm_recommend where sid=(select sid from um_consumer where phone_no="1310000000")) ORDER BY a.update_date desc;

幼儿园视频：

SELECT
a.video_name,
b.psy_coin as 下载价格,
c.psy_coin as 播放价格,
if(b.psy_coin=0,1,
(if(((NOW()>=(select start_time from pay_free_strategy where status=1 and for_arword_view=1)
and NOW() <=(select end_time from pay_free_strategy where status=1 and for_arword_view=1))),1,
(if(NOW()<=(select c.end_date from pay_vip_record c LEFT JOIN um_consumer d on c.csid=d.id where d.phone_no="1310000000" ORDER BY c.end_date desc limit 1),1,
(if(a.id in(select product_id from pay_trade_order e LEFT JOIN um_consumer f on e.csid=f.id where f.phone_no="1310000000"),1,0))
)))))
as is_buydown,
if(
c.psy_coin=0,1,
if(((NOW()>=(select start_time from pay_free_strategy where status=1 and for_arword_view=1)
and NOW() <=(select end_time from pay_free_strategy where status=1 and for_arword_view=1))),1,
if(NOW()<=(select c.end_date from pay_vip_record c LEFT JOIN um_consumer d on c.csid=d.id where d.phone_no="1310000000" ORDER BY c.end_date desc limit 1),1,
if(a.id in(select product_id from pay_trade_order e LEFT JOIN um_consumer f on e.csid=f.id where f.phone_no="1310000000"),1,0)
)))
as is_play
FROM
cm_video a
LEFT OUTER JOIN pay_strategy b ON a.download_price_sid=b.id
LEFT OUTER JOIN pay_strategy c on a.play_price_sid=c.id
WHERE
a.video_type = 3
AND a.audit_status = 1
AND a.del_flag = 0
AND (a.kid=(select sid from um_consumer where phone_no="1310000000") or a.share_status=1)