>
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
