SELECT pidors id pidors2 id pidors2 user pidors2 date pidors2 phone DI

 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
SELECT
/*pidors.id, pidors2.id, pidors2.user, pidors2.date, pidors2.phone*/
DISTINCT pidors2.user
FROM
(
SELECT
/*DISTINCT orders.user as users,*/
orders.id as id,
orders.`user` as `user`,
FROM_UNIXTIME(orders.otime, "%Y%m%d") as date,
orders.phone as phone,
COUNT(DISTINCT orders.state) as q,
COUNT(orders.id) as c
/*DISTINCT orders.state 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
ORDER BY q DESC
) as pidors
JOIN (
SELECT
orders.id as id,
orders.`user` as `user`,
FROM_UNIXTIME(orders.otime, "%Y%m%d") as date,
orders.phone as phone
FROM
orders
WHERE
FROM_UNIXTIME(orders.otime, "%Y%m%d") BETWEEN "20140101" AND "20150101"
) as pidors2 ON (pidors.date = pidors2.date AND pidors.phone = pidors2.phone)
WHERE pidors.q>1
ORDER BY pidors.phone
/*LEFT JOIN orders o ON (FROM_UNIXTIME(o.otime, "%Y%m%d") = pidors.date AND o.phone = pidors.phone)
WHERE FROM_UNIXTIME(o.otime, "%Y%m%d") BETWEEN "20150101" AND "20150107"*/