MySQL case where & concat & Substring_index & not & have used small case

1. Code

SELECT a.id, A.activity_name, ( case when A.activity_end_time>Now () Then' Participation 'ELSE (A.activity_doubt<> ' * 'and A.activity_doubt<c.doubt) OR (a.activity_praise<> ' * 'and a.activity_praise> c.praise * 100) Then' Not compliant 'ELSE' Standards 'END)            END) as state, c.driver_id, C.driver_phone, C.driver_name, C.countfrom (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 WHERE1 = 1and activity_name like Concat (Concat ('% ', ' data '), '% ') and ID= 1And not ((Activity_start_time> ' 2017-10-27 17:16:00 ') OR (Activity_end_time< ' 2017-10-27 17:10:00 ')) as Ainner 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) /c0>/count (Order_no) as praise from Car_biz_numprize_order_item I WHERE1 = 1and driver_id= 1000063GROUP by i.driver_id, i.numprize_base_id) As C on a.ID=c.numprize_base_idhaving State= ' nonconformance ' ORDER by a.created_time DESC, c.driver_id ASCLIMIT0, 10

2. Sources of demand

2.1 Compliance with the activity criteria query as shown in the following page 2-1

2.2 Event-related tables

Activity Table 2-2

Qualifying Orders Table 2-3

2.1 The query condition for the active participation status in the diagram is a field that does not exist in the two tables

You need to get the Driver_award_condition column from Activity Table 2-2 and split the criteria and then perform the operation on the 2-1 table and then choose again.

2.4 If the mybatis in the SQL is less than the greater than the number placed in the <! [CDATA [>]]> in

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. Summary

Complex SQL is written step-by-step

MySQL case when & concat & Substring_index & not & have used small case