如何选择在sql中使用相同ID插入哪一行? [英] how to choose which row to insert with same id in sql?

问题描述

所以基本上我有一个名为table_1"的表:

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

我需要将这些数据插入到我的第二个表table_2"中，其中只包含开始和完成时间，所以我的table_2"应该是这样的:

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

如果有人能帮我为此编写 sql 查询，我将不胜感激.提前致谢

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

推荐答案

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:

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?

这篇关于如何选择在sql中使用相同ID插入哪一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！