mysql case when & concat & SUBSTRING_INDEX & not & having 使用的小case
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql case when & concat & SUBSTRING_INDEX & not & having 使用的小case，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2070字，纯文字阅读大概需要3分钟。
内容图文
> now() THEN
‘参与中‘
ELSE
(
CASE
WHEN (
a.activity_doubt <> ‘*‘
AND a.activity_doubt < c.doubt
)
OR (
a.activity_praise <> ‘*‘
AND a.activity_praise > c.praise * 100
) THEN
‘未达标‘
ELSE
‘已达标‘
END
)
END
) AS state,
c.driver_id,
c.driver_phone,
c.driver_name,
c.count
FROM
(
SELECT
id,
activity_name,
activity_end_time,
SUBSTRING_INDEX(
driver_award_condition,
"-",
1
) AS activity_doubt,
SUBSTRING_INDEX(
driver_award_condition,
"-",
- 1
) AS activity_praise
FROM
car_biz_numprize_base AS b
WHERE
1 = 1
AND activity_name LIKE concat(concat(‘%‘, ‘数据‘), ‘%‘)
AND id = 1
AND NOT (
(
activity_start_time > ‘2017-10-27 17:16:00‘
)
OR (
activity_end_time < ‘2017-10-27 17:10:00‘
)
)
) AS a
INNER JOIN (
SELECT
i.driver_id,
i.driver_phone,
i.driver_name,
i.numprize_base_id,
count(order_no) AS count,
sum(order_doubt) AS doubt,
sum(order_praise) / count(order_no) AS praise
FROM
car_biz_numprize_order_item i
WHERE
1 = 1
AND driver_id = 1000063
GROUP BY
i.driver_id,
i.numprize_base_id
) AS c ON a.id = c.numprize_base_id
HAVING
state = ‘未达标‘ order by a.created_time desc, c.driver_id asc
LIMIT 0,
10
2. 需求的来源
2.1 符合活动条件的订单 达标情况查询 如下页面所示 2-1
2.2 活动相关的表
活动表2-2
符合条件订单表2-3
2.1图中 活动参与状态的查询条件 是在这两个表中不存在的字段
需从活动表2-2中 获取 driver_award_condition列 并分割条件 然后对 2-1表进行运算 然后再帅选
2.4 如果在mybatis中 拼sql 小于 大于号 放在 <![ CDATA [>]]>中
SELECT SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX("aa,bb,cc,dd", ",", 1)), ",", 1); #aa
SELECT SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX("aa,bb,cc,dd", ",", 2)), ",", 1); #bb
SELECT SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX("aa,bb,cc,dd", ",", 3)), ",", 1); #cc
SELECT SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX("aa,bb,cc,dd", ",", 4)), ",", 1); #dd
3.总结
复杂的sql 是一步一步写出来的
mysql case when & concat & SUBSTRING_INDEX & not & having 使用的小case
标签：jpg color 存在 case car com stat blog col
本文系统来源：http://www.cnblogs.com/rocky-fang/p/7767735.html
内容总结
以上是互联网集市为您收集整理的mysql case when & concat & SUBSTRING_INDEX & not & having 使用的小case全部内容，希望文章能够帮你解决mysql case when & concat & SUBSTRING_INDEX & not & having 使用的小case所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。