@ Database Design
ID pay_name pay_time (timestamp int)
@ How to arrange the table by time, like a graph, with data at the same time
The scenario before @ is
Preferred group BY (Pay_time) get all the time
At each time, loop all the data to compare whether the time is equal to the above time, but compare all the times each time.
But this wastes performance.
Reply content:
@ Database Design
ID pay_name pay_time (timestamp int)
@ How to arrange the table by time, like a graph, with data at the same time
The scenario before @ is
Preferred group BY (Pay_time) get all the time
At each time, loop all the data to compare whether the time is equal to the above time, but compare all the times each time.
But this wastes performance.
1, the first to add index to pay_time.
2. Take the amount of data you need from the database in time, select * from table where Pay_time >=t1 and Pay_time < t2 ORDER BY pay_time desc limit Pagesiz E
3, in the front-end of your data grouped by time.
SET sql_mode = ''; --不加这一条语句group by 会报错 ,加了又不太安全。建议再加个varchar类型的字段保存时间,加了字段后就不用FROM_UNIXTIME()函数了select *,FROM_UNIXTIME(a.pay_time,"%Y-%m-%d") as ptime FROM test a LEFT JOIN test b on FROM_UNIXTIME(a.pay_time,"%Y-%m-%d") = FROM_UNIXTIME(b.pay_time,"%Y-%m-%d") and a.id >= b.id GROUP BY a.id,a.pay_name,datas HAVING count(b.id) <= 3 ORDER BY a.pay_time,a.id
count(b.id) <= 3 //这里的 3 是每个时间段的条数
Then we'll just group the data according to Ptime.