如何选择与SQL相同的ID插入哪一行？(how to choose which row to ins

所以基本上，我已经叫一表“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

我需要将这些数据插入到我的第二个表“TABLE_2”只有开始和compelete时间都包括在内，所以我的“TABLE_2”应该是这样的：

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

如果任何人都可以帮我写的SQL查询为了这个，我会非常感激。 提前致谢

Answer 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

插入作出后，如果你想看到的结果，根据你的榜样，你必须运行以下选择：

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

那是完全正确的，但我不想只看到这样的说法，它需要在第二个表中插入以这种方式为好，什么想法？

Answer 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

我认为，应该为你做，虽然没有测试它:(

Answer 3:

你可以得到所有的开始时间是这样的：

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

然后完成这样的：

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

你可以使用union同时使用，当然试试：

   insert into table_2

Answer 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

文章来源: how to choose which row to insert with same id in sql?