主页 / dba / 问题 / 171798
Accepted
Ganesh_55
Asked: 2017-04-24 20:48:40 +0800 CST

获取一周的登录时间

  • 772

我有两张这样的桌子

Tc_Date
2016-02-01 00:00:00
2016-02-02 00:00:00
2016-02-03 00:00:00
2016-02-04 00:00:00
2016-02-05 00:00:00
2016-02-06 00:00:00
2016-02-07 00:00:00
2016-02-08 00:00:00
2016-02-09 00:00:00
2016-02-10 00:00:00

和

EmpID   Office_Date          Login_Time                 Logout_Time
397     2016-02-01 00:00:00  2016-02-01 08:35:09.780    2016-02-01 17:18:56.230
397     2016-02-02 00:00:00  2016-02-02 08:28:50.713    2016-02-02 17:10:36.997
397     2016-02-03 00:00:00  2016-02-03 08:27:38.550    2016-02-03 17:22:49.037
397     2016-02-04 00:00:00  2016-02-04 08:34:26.653    2016-02-04 17:11:04.540
397     2016-02-05 00:00:00  2016-02-05 08:36:12.833    2016-02-05 17:07:22.797
397     2016-02-09 00:00:00  2016-02-09 08:35:58.840    2016-02-09 17:15:40.203
397     2016-02-10 00:00:00  2016-02-10 08:30:51.940    2016-02-10 17:08:58.480
396     2016-02-01 00:00:00  2016-02-01 08:42:39.000    2016-02-01 17:57:39.000
396     2016-02-02 00:00:00  2016-02-02 07:49:36.090    2016-02-02 17:05:00.470
396     2016-02-03 00:00:00  2016-02-03 07:49:54.583    2016-02-03 17:14:57.663
396     2016-02-04 00:00:00  2016-02-04 07:54:15.087    2016-02-04 17:05:38.373
396     2016-02-05 00:00:00  2016-02-05 07:51:12.133    2016-02-05 17:00:42.870
396     2016-02-08 00:00:00  2016-02-08 07:58:06.833    2016-02-08 17:01:52.750
396     2016-02-09 00:00:00  2016-02-09 07:56:26.730    2016-02-09 17:04:31.650
396     2016-02-10 00:00:00  2016-02-10 07:43:16.463    2016-02-10 17:03:19.480

我想要这样的答案

Empid  2016-02-01  2016-02-02  2016-02-03  2016-02-04  2016-02-05  2016-02-06...

397    08:35:09    08:28:50    08:27:38    08:34:26    08:36:12     Absent

396    08:42:39    07:49:36    07:49:54    07:54:15    07:51:12     Absent
sql-server

1 个回答

  1. Best Answer

    您可以使用动态 PIVOT 解决方案来解决它。

    查看有关 PIVOT 的 MS-Docs。

    DECLARE @col AS nvarchar(MAX),
        @cmd AS nvarchar(MAX);

SET @col = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(DATE,dt,101)) 
            FROM Tc_Date
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
            ,1,1,'')

SET @cmd =   'SELECT * '
           + ' FROM  (SELECT EmpId,  Office_Date, CONVERT(VARCHAR(20), Login_time, 108) LoginTime FROM Logins) src'
           + ' PIVOT (MAX(LoginTime) FOR Office_Date IN (' + @col + ')) pvt';

EXECUTE (@cmd);
GO

    空号 | 2016-02-01 | 2016-02-02 | 2016-02-03 | 2016-02-04 | 2016-02-05 | 2016-02-06 | 2016-02-07 | 2016-02-08 | 2016-02-09 | 2016-02-10
----： | :-------- | :-------- | :-------- | :-------- | :-------- | :-------- | :-------- | :-------- | :-------- | :----------
  396 | 08:42:39 | 07:49:36 | 07:49:54 | 07:54:15 | 07:51:12 | 空       | 空       | 07:58:06 | 07:56:26 | 07:43:16  
  397 | 08:35:09 | 08:28:50 | 08:27:38 | 08:34:26 | 08:36:12 | 空       | 空       | 空       | 08:35:58 | 08:30:51

    dbfiddle在这里

    • 0

相关问题