所以基本上我有一个名为“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”应该如下所示:
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 查询,我将非常感激。
提前致谢
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
完全正确,但是我不想只是这样看它,它也需要以这种方式插入到第二个表中,知道吗?
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)