测试的sql
幼教视频全部
'''sql中需传的参数为：phone_no，phone_no（当前登录账号），cid（视频分类），video_type（1 幼教视频， 2 合作方视频，3校方视频），del_flag（0未删除，1删除），audit_status（ 0未审核，1通过，2未通过）'''
videolist='''SELECT a.id, a.video_name, a.cid, CONCAT( "http://happyanimation-site.oss-cn-shenzhen.aliyuncs.com/", image_uri )as 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 =% s ORDER BY c.end_date DESC LIMIT 1 ),''1'', ( IF ( a.id = ( SELECT pay_target FROM pay_vcoin_record WHERE pay_target = a.id AND TO_DAYS(create_date) = to_days(now()) AND csid = ( SELECT id FROM um_consumer WHERE phone_no =% s )),''1'',''||0||''))))))) AS is_play FROM cm_video a LEFT JOIN pay_strategy b ON a.play_price_sid = b.id WHERE a.cid = % s AND a.video_type =% s AND a.del_flag =% s AND a.audit_status =% s ORDER BY psy_coin, a.update_date DESC'''
学校推荐
SELECT
a.id,
a.video_name,
(
UNIX_TIMESTAMP(a.update_date) * 1000
) AS update_date,
CONCAT(
"http://happyanimation-site.oss-cn-shenzhen.aliyuncs.com/",
image_uri
) AS 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 ="18780010265"
ORDER BY
c.end_date DESC
LIMIT 1
),
'1',
(
IF (
a.id = (
SELECT
pay_target
FROM
pay_vcoin_record
WHERE
pay_target = a.id
AND TO_DAYS(create_date) = to_days(now())
AND csid = (
SELECT
id
FROM
um_consumer
WHERE
phone_no ="18780010265"
)
),
'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.audit_status =1
and a.id in (select target_id from cm_recommend where sid in (select sid from um_consumer where phone_no="18780010265"))
ORDER BY
psy_coin,
a.update_date DESC
最新文章
- int与string类型的转换
- Unity 延迟执行一段代码的较为优雅的方式
- Android 把电话保存到现有联系人 已有联系人
- Microsoft SQL Server Compact 4.0&&ADO.NET Entity Framework 4.1&&MVC3
- SAE平台的文件I/O处理
- Leetcode那点事儿
- execute immediate的简单用法(oracle)
- 2014年TI杯大学生电子设计竞赛地区赛使用仪器及器件、设备
- linux仅修改文件夹权限；linux 分别批量修改文件和文件夹权限
- EntityFramework Core并发导致显示插入主键问题
- docke镜像上传到dockerhub仓库和阿里云docker仓库的方法
- [BZOJ 2064]分裂
- iOS----------APP怎样做更安全
- mysql插件的初始化
- wamp本地可以访问，远程无法访问，报错：client denied by server configuration
- LeetCode算法题-Lowest Common Ancestor of a Binary Search Tree
- HDU--5519 Sequence II （主席树）
- Puppet软件资源管理
- 18-Python3 迭代器与生成器
- 编写一个c++工程