TSQL -如果组中不存在特定条目，则插入条目

Question

如果没有记录显示来自某个组的用户已注销，我将尝试插入一个条目。

declare @test table (grp varchar(2),logged varchar(4),time datetime)

insert into @test (grp,logged,time)
values ('A1', 'IN','20181111 09:00:00')
      ,('A1', 'OUT','20181111 10:00:00')
      ,('A2', 'IN','20181111 09:10:00')
      ,('A2', 'IN','20181111 09:20:00')
      ,('A3', 'IN','20181111 09:30:00') 
      ,('A3', 'OUT','20181111 10:30:00')

期望输出

+-----+--------+-------------------------+
| grp | logged |          time           |
+-----+--------+-------------------------+
| A1  | IN     | 2018-11-11 09:00:00.000 |
| A1  | OUT    | 2018-11-11 10:00:00.000 |
| A2  | IN     | 2018-11-11 09:10:00.000 |
| A2  | IN     | 2018-11-11 09:20:00.000 |
| A2  | OUT    | NULL                    |
| A2  | OUT    | NULL                    |
| A3  | IN     | 2018-11-11 09:30:00.000 |
| A3  | OUT    | 2018-11-11 10:30:00.000 |
| A4  | IN     | 2018-11-11 09:40:00.000 |
| A4  | OUT    | NULL                    |
+-----+--------+-------------------------+

有什么想法吗？

Answer 1

我有两个版本给你

CREATE TABLE SignIn(grp varchar(10), logged varchar(3), [time] datetime)
INSERT INTO SignIn
VALUES
('A1','IN'  ,   '2018-11-11 09:00:00.000'   ),
('A1','OUT',    '2018-11-11 10:00:00.000'   ),
('A2','IN'  ,   '2018-11-11 09:10:00.000'   ),
('A2','IN' ,    '2018-11-11 09:20:00.000'   ),
('A3','IN'  ,   '2018-11-11 09:30:00.000'   ),
('A3','OUT',    '2018-11-11 10:30:00.000'   ),
('A4','IN',     '2018-11-11 09:40:00.000'   )

这个版本和你期望的一样。对于记录中的每一份，您都会得到一份输出副本。

INSERT INTO SignIn (grp, logged, time)
SELECT s.grp, 'OUT', NULL
FROM SignIn s
INNER JOIN (
        SELECT grp
        FROM SignIn
        GROUP BY grp
        HAVING  MAX(CASE WHEN Logged = 'OUT' THEN 1 ELSE 0 END) = 0
        ) notSignedOut ON s.grp = notSignedOut.grp

但是，如果您不希望复制记录，并且只希望组中有一个out记录，您可以使用下面的选项：

    INSERT INTO SignIn (grp, logged, time)
    SELECT grp, 'OUT', NULL
        --ISLoggedOut = MAX(CASE WHEN Logged = 'OUT' THEN 1 ELSE 0 END)
    FROM SignIn
    GROUP BY grp
    HAVING  MAX(CASE WHEN Logged = 'OUT' THEN 1 ELSE 0 END) = 0

主逻辑隐藏在ISLoggedOut =MAX(在登录时为“OUT”时为大小写，则为1 under 0结束)。对于每一组记录，我添加了指示符，如果有记录的话。万一没有值就等于0。

另一个选项是使用子句。

   INSERT INTO SignIn (grp, logged, time)
   SELECT s.grp, 'OUT', NULL
   FROM SignIn s
   WHERE NOT EXISTS (SELECT TOP 1 1 FROM SignIn i WHERE i.grp = s.grp AND Logged = 'OUT')

就我个人而言，我更喜欢使用拥有，根据一些经验，有时大表存在的速度更快。例如，对于我们的示例语句，有了产生下一个统计信息

桌子‘工作表’扫描计数0，逻辑读取0，物理读取0，预读读取0，lob逻辑读取0，lob物理读取0，lob预读读取0.桌子上的信号。扫描计数2，逻辑读取2，物理读取0，预读读取0，lob逻辑读取0，lob物理读取0，lob预读读取0.

并存在声明：

桌子上的信号。扫描计数2，逻辑读取8，物理读取0，预读读取0，lob逻辑读取0，lob物理读取0，lob预读读取0.

看起来让语句读取的次数比存在的少。