Prerequisite: any phone number through the equipment, equipment can record the elapsed time, mobile phone number in to the database



Page spread array device (at least 2 or more) : device001 device002 device003 3 devices, as well as the scope of the acquisition time start time startTime end time endTime
How to get to a phone number first after device001 again after device002 finally after device003 number and other detailed information, SQL 233 how to write the picture number should be a

CodePudding user response:

Used to?

CodePudding user response:

Is the RDB database is common SQL syntax

CodePudding user response:

The same phone number, the same day, in a device will exist two data?

CodePudding user response:

The

reference 3 floor qq_18379499 response:

the same phone number, the same day, in a device will exist two data?

There are many article will, on the same day

CodePudding user response:

I wrote a two-dimensional presents the SQL, you see the difference with you how much;

SELECT Amy polumbo honeNo, a. d. evice device001, a. ` time ` AS time001, arjun ac mac001, a.q q qq001,
B.d evice device002, b. ` time ` AS time002, b.m ac mac002 b.q q qq003, c.d evice device003,
C. ` time ` AS time003, c. ac mac003 c.q q qq003
The FROM
(SELECT phoneNo, device, ` time `, DATE (` time `) data_date, MAC, qq FROM table_test WHERE ` time ` & gt;='2019-07-09' AND ` time ` & lt; DATE_ADD (' 2019-07-09 ', the INTERVAL 1 DAY)
AND device='device001'). A,
(SELECT phoneNo, device, ` time `, DATE (` time `) data_date, MAC, qq FROM table_test WHERE ` time ` & gt;='2019-07-09' AND ` time ` & lt; DATE_ADD (' 2019-07-09 ', the INTERVAL 1 DAY)
B='device002' AND device),
(SELECT phoneNo, device, ` time `, DATE (` time `) data_date, MAC, qq FROM table_test WHERE ` time ` & gt;='2019-07-09' AND ` time ` & lt; DATE_ADD (' 2019-07-09 ', the INTERVAL 1 DAY)
C='device003' AND device)
WHERE Amy polumbo honeNo=p. honeNo AND p. honeNo=c.p honeNo AND Amy polumbo honeNo=c.p honeNo
AND a. d. ata_date=b.d ata_date AND b.d ata_date=c.d ata_date AND a. d. ata_date=c.d ata_date;

CodePudding user response:

More than the data, need to add GROUP in the child table BY phoneNo, DATE (` time `)

CodePudding user response:

refer to 6th floor qq_18379499 response:

multiple data in the day, need to add GROUP in the child table BY phoneNo, DATE (` time `)

You may understand wrong, now in three devices, for example, all I want is the result of the cell phone number after equipment 1 first, and then after 2 equipment, finally after the number of the equipment, is not is not in this order,

CodePudding user response:

If the record is
233 device01 01:00:00 2019-7-10
233 device02 02:00:00 2019-7-10
233 device03 03:00:00 2019-7-10
233 device02 04:00:00 2019-7-10
233 device03 05:00:00 2019-7-10
233 device02 04:30:00 2019-7-10
233 device03 05:30:00 2019-7-10
How to calculate??

CodePudding user response:

I according to which line is 123 125 127 145 147 167 6 times record

CodePudding user response:

The

reference code without moving the reply on the eighth floor:

if the record is
233 device01 01:00:00 2019-7-10
233 device02 02:00:00 2019-7-10
233 device03 03:00:00 2019-7-10
233 device02 04:00:00 2019-7-10
233 device03 05:00:00 2019-7-10
233 device02 04:30:00 2019-7-10
233 device03 05:30:00 2019-7-10
How to calculate??

Just said is wrong, it should be as long as there is a 1 to 2 equipment and equipment appear even if the total number of 3, you say should be 7 times

CodePudding user response:

references to the tenth floor, knife apostrophe reply:

Quote: reference code can't reply on the eighth floor:

if the record is
233 device01 01:00:00 2019-7-10
233 device02 02:00:00 2019-7-10
233 device03 03:00:00 2019-7-10
233 device02 04:00:00 2019-7-10
233 device03 05:00:00 2019-7-10
233 device02 04:30:00 2019-7-10
233 device03 05:30:00 2019-7-10
How to calculate??

Just said is wrong, it should be as long as there is a equipment of 1 to 2 to the total number of equipment 3 even appear, you said it should be 7 times

Ideas should be such,
First take out data among all mobile phone number in the device 1,
To link all mobile phone number in the data of equipment 2
Then use the case when marking if equipment 2 time & gt; Device 1 time and use the substr interception are on the same day 1 to 0
otherHere is a little device 2 time must be

Then take labeled 1 3 all data in the device data
The same as the above use case when marking and equipment on the same day 2 time & lt; Less than three other is 0 to 1 equipment


Finally you took out all of the above marking statistics for data grouped on mobile number 1

CodePudding user response:

Don't want to write SQL, train of thought to you, there is a problem for me again

CodePudding user response:

refer to 12 floor Edmond1023 reply:

don't want to write SQL, the idea to you, there is a problem then find I

Select distinct n.D iscoverMacAddr, m.c nt from
(select t1. DiscoverMacAddr,
(case when t1. StartTimeThe from
(select * from wifiuserdata where StartTime between '08:20:22' 2019-05-28 and '2020-03-22 04:22:40'
And DeviceID='ipcarma176) t1,
(select * from wifiuserdata where StartTime between '08:20:22' 2019-05-28 and '2020-03-22 04:22:40'
And DeviceID='ipcarma177) t2,
(select * from wifiuserdata where StartTime between '08:20:22' 2019-05-28 and '2020-03-22 04:22:40'
T3 and DeviceID='ipcarma178')
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull