如何从第一行插入记录 [英] How to insert record from first row

问题描述

i有一个表差异包含两列



签到

结帐



i写这个查询将其他表中的记录插入签到

  INSERT   INTO  diff（CHECKIN） SELECT  checktime 来自 checkinout 其中 sensorid = '  1' 

结果就像..



checkin .................out

------- --------

8/19/2013 14:53 ..... Null

8/19/2013 12:50 ..... Null



那么我是执行另一个查询..

  INSERT   INTO  diff（CHECKOUT） SELECT  checktime 来自 checkinout 其中 sensorid = '  2' 

结果如下所示..

 checkin ............. checkout 
 ------- -------- 
 8 / 19/2013 14:53 ..... Null 
 8/19/2013 12:50 ..... Null 
 Null ............... .8 / 19/2013 12:50 
 Null ................ 8/19/2013 1:50 

但我希望显示为

 2013年8月19日14:53 ..... 8/19 / 2013 12:50 
 8/19/2013 12:50 ..... 8/19/2013 12:50 

解决方案

在这种情况下，我看到设计缺陷或信息不完整。无论如何，一个解决方案可能是

 选择 A.checkin，B。结帐
  FROM  
（
 选择 ROW_NUMBER（）ID，checkin 
  FROM  diff 
 其中 checkout 是  null  
） INNER   JOIN  
（
 选择 ROW_NUMBER（）ID，结帐
 来自 diff 
 其中 checkin    null  
）B  ON  A.ID = B.ID 

< blockquote>要获得所需的结果，您应该使用UPDATE查询;它将更新所选列中的值。 INSERT查询在表格中添加了一个新行，因此您无法按照您的预期获得结果。

Hi,
i have one table diff contain two columns

Checkin
Checkout

i am writing this query to insert record from other table into checkin

INSERT   INTO diff(CHECKIN)  SELECT checktime from checkinout where sensorid='1'

the result is like..

checkin.............checkout
------- --------
8/19/2013 14:53.....Null
8/19/2013 12:50.....Null

then i am executing the other query..

INSERT   INTO diff(CHECKOUT)  SELECT checktime from checkinout where sensorid='2'

the result look like this..

checkin.............checkout
-------             --------
8/19/2013 14:53.....Null
8/19/2013 12:50.....Null
Null................8/19/2013 12:50
Null................8/19/2013 1:50

but i want to be display like

8/19/2013 14:53.....8/19/2013 12:50
8/19/2013 12:50.....8/19/2013 12:50

解决方案

I see either a design flaw or incomplete information in this scenario. Anyway one solution could be

select A.checkin, B.checkout
FROM
(
select ROW_NUMBER() ID, checkin
FROM diff
where checkout is null
) A INNER JOIN 
(
select ROW_NUMBER() ID, checkout
from diff
where checkin is null
) B ON A.ID = B.ID

To get your desired results you should use the UPDATE query; it will update the values in the selected column. While INSERT query adds a new row in the table due to which you are unable to get the results as per your expectations.

这篇关于如何从第一行插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！