时间戳

首页 > 解决方案 > 在一个表中同时选择两行

php - 在一个表中同时选择两行

问题描述

我有这张桌子：

USERID  CHECKTIME                CHECKTYPE
1       2018-10-21 09:01:08.000  I
1       2018-10-21 10:00:51.000  I
1       2018-10-22 13:19:23.000  1
1       2018-10-22 15:56:11.000  0
1       2018-10-22 16:51:27.000  O

我需要像这样选择两行：

userid   in                         out
1        2018-10-22 13:19:23.000    2018-10-22 15:56:11.000

但相同的日期和相同的用户 ID 日期和用户 ID 来自帖子页面 php 数据库是 sqlserver 与 php 的连接

SELECT TOP 1 checktime, userid, checktype
FROM CHECKINOUT 
WHERE userid = '$idd' 
    AND CAST(checktime As DATE)='$x'
    AND checktype='1' 
UNION ALL
SELECT TOP 1 checktime, userid, checktype
FROM CHECKINOUT 
WHERE userid='$idd'
    AND CAST(checktime As DATE)='$x'
    AND checktype='0'

标签： phpsql-server

解决方案

干得好

CREATE TABLE T
    ([UserId] int, [CheckDate] datetime, [CheckType] BIT)
;

INSERT INTO T
    ([UserId], [CheckDate], [CheckType])
VALUES
    (1, '2018-10-21 09:01:08', 1),
    (1, '2018-10-21 10:00:51', 1),
    (1, '2018-10-22 13:19:23', 1),
    (1, '2018-10-22 15:56:11', 0),
    (1, '2018-10-22 16:51:27', 0)
;

SELECT DISTINCT UserID,
       (SELECT MAX(CheckDate) FROM T WHERE CheckType = 1 ANDUserID = TT.UserID) [In],
       (SELECT MIN(CheckDate) FROM T WHERE CheckType = 0 AND UserID = TT.UserID) [Out]
FROM T TT
WHERE UserID = 1;

您的样本中有错误的数据（我猜），因为在您的查询中检查'1'并'0'在您的查询中，这意味着没有'I'，因此您可以使用BITdatatype 而不是VARCHAR.

如果你真的有'I'支票类型，那就是

SELECT DISTINCT UserID,
       (SELECT MAX(CheckDate) FROM T WHERE CheckType = '1' AND UserID = TT.UserID) [In],
       (SELECT MIN(CheckDate) FROM T WHERE CheckType = '0' AND UserID = TT.UserID) [Out]
FROM T TT
WHERE UserID = 1;

结果

+--------+---------------------+---------------------+
| UserID |         In          |         Out         |
+--------+---------------------+---------------------+
|      1 | 22/10/2018 13:19:23 | 22/10/2018 15:56:11 |
+--------+---------------------+---------------------+

推荐阅读