SELECT SQL_NO_CACHE orders date orders phone FROM SELECT FROM_UNIXTIME

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT
SQL_NO_CACHE
orders.date,
orders.phone
FROM
(
SELECT
FROM_UNIXTIME(orders.otime, "%Y%m%d") as date,
orders.phone as phone,
COUNT(DISTINCT orders.state) as q,
GROUP_CONCAT(DISTINCT orders.state SEPARATOR ",") as states
FROM
orders
WHERE
FROM_UNIXTIME(orders.otime, "%Y%m%d") BETWEEN "20140101" AND "20150101"
GROUP BY
FROM_UNIXTIME(orders.otime, "%Y%m%d"),
orders.phone
) as orders
WHERE
orders.q > 1
AND
FIND_IN_SET(99, orders.states) > 0
GROUP BY
orders.date,
orders.phone