how to choose which row to insert with same id in

问题:

so Basically I have a table called "table_1" :

ID   Index          STATUS          TIME        DESCRIPTION
1     15          pending           1:00       Started Pending
1     16          pending           1:05       still in request
1     17          pending           1:10       still in request
1     18          complete          1:20       Transaction has been completed
2     19          pending           2:25       request has been started
2     20          pending           2:30       in progress
2     21          pending           2:35       in progess still
2     22          pending           2:40       still pending
2     23          complete          2:45       Transaction Compeleted

I need to insert these data into my second table "table_2" where only start and compelete times are included, so my "table_2" should like this:

ID   Index   STATUS          TIME          DESCRIPTION
1     15     pending         1:00          Started Pending
1     18     complete        1:20          Transaction has been completed
2     19     pending         2:25          request has been started
2     23     complete        2:45          Transaction Compeleted

if anyone can help me write sql query for this I would highly appreciate it. Thanks in advance

回答1:

INSERT INTO t2 (ID, STATUS, TIME)
SELECT ID, STATUS, MIN(TIME) FROM t1 t1top
WHERE EXISTS(SELECT * FROM t1 WHERE ID=t1top.ID AND STATUS='Complete')
GROUP BY ID, STATUS
ORDER BY CAST(ID AS UNSIGNED) ASC, STATUS DESC

After the insert is made, if you want to see the result according to your example, you have to run the following select:

SELECT ID, STATUS, TIME FROM table_1
ORDER BY CAST(ID AS UNSIGNED) ASC, STATUS DESC

thats EXACTLY RIGHT, however I dont want to just SEE it that way, it needs to be inserted in the second table in that manner as well, any idea?

回答2:

INSERT INTO table_2
SELECT id,status,min(time)
FROM table_1 AS t1
WHERE EXISTS(SELECT 1
             FROM table_1
             WHERE id=t1.id
                 AND status='complete')
GROUP BY id,status

I think that should do it for you, haven't tested it though :(

回答3:

you could get all the start times like this:

   select id, status, min(time) 
   from table_1 
   where status = 'Pending'
   group by id, status

then completion like this:

   select id, status, time
   from table_1 
   where status = 'Complete'
   group by id, status

you can use union to use both, and of course try:

   insert into table_2

回答4:

INSERT INTO myTable2 (ID, STATUS, TIME, DESCRIPTION)
SELECT t1.ID, t1.STATUS, t1.TIME, t1.DESCRIPTION FROM table_1 as t1 
WHERE STATUS = 'complete' 
OR TIME = (SELECT min(TIME) FROM table_1 WHERE ID = t1.ID) 
ORDER BY ID asc, STATUS desc