如何在sql中调用临时表

Question

      Date             Time        Mode ID
    2017-01-01  13:00:00.0000000    3   10
    2017-01-01  14:00:00.0000000    1   10
    2017-01-01  15:00:00.0000000    3   10
    2017-01-01  15:30:00.0000000    1   10

这是一个临时表，我只想将时间列显示为2列，1列模式=3，其他列为mode=1。

这是一个临时表，我只想要下面的输出：

          Date         InTime(Mode-3)    OutTime(Mode-1)     ID

         2017-01-01   13:00:00.0000000  14:00:00.0000000    10

         2017-01-01   15:00:00.0000000  15:30:00.0000000    10

Answer 1

根据数据和数据类型/模式(如果表名为timeTable)，这是可行的：

    SELECT DATE, time AS 'InTime(Mode-3)',
      (SELECT TOP 1 time FROM timeTable
          WHERE mode = 1 
            AND id = outerTable.id 
            AND date = outerTable.date 
            AND time > outerTable.time 
          ORDER BY date, time) AS 'OutTime(Mode-1)',
      ID
    FROM timeTable AS outerTable 
    WHERE mode = 3

• outerQuery只选择即时模式= 3。
• 在innerQuery中，选择与所选时间相对应的下一个超时时间，并且只返回第一个。既然是按日期和时间订购的，那就应该是下一个了。仅用给定的数据进行测试

输出：

    Date       |  InTime(Mode-3)     |   OutTime(Mode-1)   |  ID
---------------|---------------------|---------------------|------
   2017-01-01  |  13:00:00.0000000   |  14:00:00.0000000   |  10
   2017-01-01  |  15:00:00.0000000   |  15:30:00.0000000   |  10

仅供参考：

我使用了这个表模式

 CREATE TABLE timeTable(
     date DATE,
     time TIME,
     mode INTEGER,
     id INTEGER
 );

更新：

时差：

SELECT *, DATEDIFF(MINUTE,INTIME,OUTTIME) AS [DIFFERENCE] FROM (
    SELECT [DATE], [time] AS INTIME,
      (SELECT TOP 1 [time] FROM timeTable
          WHERE [mode] = 1 
            AND [id] = outerTable.id 
            AND [date] = outerTable.date 
            AND [time] > outerTable.time 
          ORDER BY [date], [time]) AS OUTTIME,
      [ID]
    FROM [timeTable] AS outerTable 
    WHERE [mode] = 3
) WholeData

Answer 2

猜测您需要一个方法来创建具有固定值的交替行(1和3)。您可以使用

情况下，当ROW_NUMBER()超过(按日期排序)%2=0，则为1其他3

作为模式列的逻辑。

Answer 3

尝尝这个,

DECLARE @TB TABLE (DATETIME VARCHAR(30),ID INT)
INSERT INTO @TB VALUES
('2017-01-01 13:00:00.0000000',10),
('2017-01-01 14:00:00.0000000',10),
('2017-01-01 15:00:00.0000000',10),
('2017-01-01 15:30:00.0000000',10 )

SELECT  SUBSTRING(DATETIME,0,11) DATE
        ,SUBSTRING(DATETIME,12,LEN(DATETIME)) TIME
        ,CASE WHEN ROW_NUMBER() OVER (ORDER BY DATETIME)%2 = 0 THEN 1 ELSE 3 END MODE
        ,ID
FROM    @TB