0

I have a login log table, its already define a id (auto increment) as primary key! but it's very difficult to catch each user data. let's see that table demo below.

+----+------+--------+
| id | uid  | money  |
+----+------+--------+
|  1 |  101 | 200.00 |
|  2 |  101 | 100.00 |
|  3 |  102 | 300.00 |
|  4 |  102 | 500.00 |
+----+------+--------+

Assume we need to the user latest 7days record, we have to filter by uid and sort it! so I want to redesign a table like below structure:

+----+---+------+-------+
| id |pid|uid  | money  |
+----+---+------+-------+
|  1 | 1 | 101 | 200.00 |
|  2 | 2 | 101 | 100.00 |
|  3 | 1 | 102 | 300.00 |
|  4 | 3 | 101 | 300.00 |
|  5 | 2 | 102 | 500.00 |
+----+---+-----+--------+

I just want to set a auto increment based on the uid. Example, if we already has store 101 on table, the pid will be incre 1 otherwise set 1. How can i reach this goal?

Frank AK
  • 1,705
  • 15
  • 28

2 Answers2

1

How about to get you a view like desired results using the existing structure

select a.*,
(
  select count(*)
  from demo b 
  where a.uid = b.uid
  and a.id > b.id
) + 1 pid
from demo a
order by a.id

DEMO

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0

Have you tried using indexes, rather than this complex idea.

Try this link What is an index in SQL?

If you were to do it as your idea. You would need to query the table first to identify if the record already exist. If it does, then fetch its last pid. Then insert a record wit pid + 1. This has a huge overhead and multiple IO operations on the database server. This would end up slowing down your entire system.

swithen colaco
  • 157
  • 1
  • 12