SELECT SQL_NO_CACHE tbl user tbl state COUNT tbl id as IF tbl user tbl

 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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SELECT
SQL_NO_CACHE
tbl.user,
tbl.state,
COUNT(tbl.id) as x,
IF (tbl.user = tbl2.user, 1, 0) as q
FROM
orders as tbl LEFT JOIN (
SELECT
DISTINCT tbl2.user
FROM
orders as tbl2 JOIN orders as tbl3 ON (
tbl2.id != tbl3.id
AND
FROM_UNIXTIME(tbl2.otime, "%Y-%m-%d") = FROM_UNIXTIME(tbl3.otime, "%Y-%m-%d")
AND
tbl2.phone = tbl3.phone
AND
tbl2.otime > tbl3.otime
AND
tbl3.state = 99
)
WHERE
FROM_UNIXTIME(tbl2.otime, "%Y-%m-%d") BETWEEN "2000-01-01" AND "2016-01-01"
AND
tbl2.state = 5
AND
tbl2.user IN (51,280,1085,1093,1114,1122,1203,1240,1471,1475,1515,1526,1535,1536)
GROUP BY
tbl2.phone,
FROM_UNIXTIME(tbl2.otime, "%Y-%m-%d")
) as tbl2 ON (
tbl.user = tbl2.user
)
WHERE
tbl.ssid = ''
AND
FROM_UNIXTIME(tbl.otime, "%Y-%m-%d") BETWEEN "2000-01-01" AND "2016-01-01"
AND
tbl.user IN (51,280,1085,1093,1114,1122,1203,1240,1471,1475,1515,1526,1535,1536)
GROUP BY
tbl.user,
tbl.state