主页 / dba / 问题 / 160041
Accepted
Ganesh_55
Asked: 2017-01-06 01:19:31 +0800 CST

从 SQL Server 获取计数

  • 772

我有这样的表：

ID      Office_Date        Login_Time
397 2015-09-21 00:00:00 2015-09-21 09:00:00.000
397 2015-09-22 00:00:00 2015-09-22 10:00:04.000
397 2015-09-23 00:00:00 2015-09-23 08:13:41.967
397 2015-09-24 00:00:00 2015-09-24 08:16:29.267
397 2015-09-25 00:00:00 2015-09-25 08:12:41.510
397 2015-09-28 00:00:00 2015-09-28 08:10:23.200
397 2015-09-29 00:00:00 2015-09-29 08:15:57.493
397 2015-09-30 00:00:00 2015-09-30 08:19:33.973
397 2015-10-01 00:00:00 2015-10-01 08:22:52.790

我想得到这样的：

id    logintime>7'oclock   logintime >7:30   logintime>8'0 clock
397        0                     0                    5
sql-server pivot

2 个回答

  1. Best Answer

    假设：

    1. logintime>7'oclock方法logintime > 7 AND logintime <= 7.30
    2. logintime >7:30 方法logintime > 7.30 AND logintime <= 8.00
    3. logintime>8'0时钟装置logintime > 8.00

    同样根据提供的数据，logintime>8'0应该包含 9 而不是 5。如果需要考虑任何其他逻辑，请更新问题。

    下面的选择查询应该可以帮助您获得所需的结果。

    WITH CTE AS(
    SELECT 
        ID
        , CASE 
            WHEN CAST(LoginTime AS TIME) > '07:00:00.0000000' AND CAST(LoginTime AS TIME) <= '07:30:00.0000000' THEN 1
            ELSE 0
        END AS After7
        , CASE 
            WHEN CAST(LoginTime AS TIME) > '07:30:00.0000000' AND CAST(LoginTime AS TIME) <= '08:00:00.0000000' THEN 1
            ELSE 0
        END AS After7_30
        , CASE 
            WHEN CAST(LoginTime AS TIME) > '08:00:00.0000000' THEN 1
            ELSE 0
        END AS After8
        FROM tblCTS_Test
)
SELECT ID
    , SUM(After7) AS 'logintime>7''oclock'
    , SUM(After7_30) AS 'logintime >7:30'
    , SUM(After8) AS 'logintime>8''0 clock'
FROM CTE
GROUP BY ID
    • 1

  2. 不确定您希望如何对数据进行分组（您需要进行微小的更改），但这是一个使用数据透视表的示例。

    CREATE TABLE #TMP(ID INT ,Office_date DATE,Login_time DATETIME)

INSERT INTO #TMP VALUES
(397,'2015-09-21 00:00:00','2015-09-21 09:00:00.000')
,(397,'2015-09-22 00:00:00','2015-09-22 10:00:04.000')
,(397,'2015-09-23 00:00:00','2015-09-23 08:13:41.967')
,(397,'2015-09-24 00:00:00','2015-09-24 08:16:29.267')
,(397,'2015-09-25 00:00:00','2015-09-25 08:12:41.510')
,(397,'2015-09-28 00:00:00','2015-09-28 08:10:23.200')
,(397,'2015-09-29 00:00:00','2015-09-29 08:15:57.493')
,(397,'2015-09-30 00:00:00','2015-09-30 08:19:33.973')
,(397,'2015-10-01 00:00:00','2015-10-01 08:22:52.790')
--Extra test rows
,(397,'2015-10-02 00:00:00','2015-10-01 07:22:52.790')
,(397,'2015-10-03 00:00:00','2015-10-01 07:32:52.790')

SELECT ID,After7,After730,After8
FROM (
    SELECT
        ID
        ,CASE
            WHEN DATEPART(HH,Login_time) >= 8 THEN 'After8'
            WHEN DATEPART(HH,Login_time) >= 7 AND DATEPART(MI,Login_time) > 30 THEN 'After730'
            WHEN DATEPART(HH,Login_time) >= 7 THEN 'After7'
            END AS Band
        ,1 AS val
    FROM
        #TMP) up
    PIVOT (COUNT(VAL) FOR BAND IN (After7, After730,After8)) AS pvt
ORDER BY ID

    替代 CASE 语句

    SELECT
    ID
    ,CASE 
        WHEN DATEDIFF(MI,Office_date,Login_time)/60.0 BETWEEN 8 AND 8.5 THEN 'After8'
        WHEN DATEDIFF(MI,Office_date,Login_time)/60.0 BETWEEN 7.5 AND 8 THEN 'After730'
        WHEN DATEDIFF(MI,Office_date,Login_time)/60.0 BETWEEN 7 AND 7.5 THEN 'After7'
        END AS Band
    ,1 AS val
FROM
    #TMP
    • 1

相关问题