如何从一个临时表中插入行到另一个临时表中

问题描述：

ID LogDate  LogTime   InoutMode  
    1 2017-02-23 19:30:00.0000000 1 
    2 2017-02-23 20:00:00.0000000 0 
    3 2017-02-23 20:30:00.0000000 1 
    4 2017-02-23 21:00:00.0000000 0 
    5 2017-02-23 21:30:00.0000000 1 
    6 2017-02-24 08:00:00.0000000 0

上述结果来自临时表。现在我想将LogTime插入到另一个临时表中，如下所示。如何从一个临时表中插入行到另一个临时表中

 InOutMode-1 =>InTime 
     InOutMode-0 =>OutTime 

    InTime   OutTime 
    19:30:00.0000000 20:00:00.0000000 
    20:30:00.0000000 21:00:00.0000000 
    21:30:00.0000000 08:00:00.0000000

它已经回答了检查http://*.com/a/42665218/7012137，其同样，你就必须在不同的日期柱 – Pream

答

使用2个cte，一个用于InoutMode与1和其他0，并且还根据Id列或日期和时间列的顺序给出行号。然后加入这两个cte的。

查询

;with cte as(
    select [rn] = row_number() over(
     order by [Id] -- also order by [LogDate], [LogTime] 
    ), * 
    from #your_temp_table_name 
    where [InoutMode] = 1 
), 
cte2 as(
    select [rn] = row_number() over(
     order by [Id] -- also order by [LogDate], [LogTime] 
    ), * 
    from #your_temp_table_name 
    where [InoutMode] = 0 
) 
select 
    t1.[LogTime] [InTime], 
    t2.[LogTime] [OutTime] 
from cte t1 
left join cte2 t2 
on t1.[rn] = t2.[rn];

如果你想结果集到一个新的临时表。然后最后一部分可以被修改为

select 
    t1.[LogTime] [InTime], 
    t2.[LogTime] [OutTime] 
into #your_new_temp_table_name 
from cte t1 
left join cte2 t2 
on t1.[rn] = t2.[rn];

Find demo here

答

使用INSERT INTO .. SELECT FROM结构像

insert into #temp2 
select ID, LogDate, 
case when InoutMode = 1 then LogTime end as InTime, 
case when InoutMode = 0 then LogTime end as OutTime, 
InoutMode  
from #temp1

答

要插入一个表格的行到另一个表，你可以这样写SQL查询：

INSERT INTO anotherTemp (InTime) 
    SELECT LogTime 
    FROM Temp 
    WHERE InoutMode = 1 

INSERT INTO anotherTemp (OutTime) 
    SELECT LogTime 
    FROM Temp 
    WHERE InoutMode = 0

请参阅此链接更http://www.dofactory.com/sql/insert-into

如何从一个临时表中插入行到另一个临时表中

相关推荐