如果没有记录表明组中的用户已注销,我将尝试插入一个条目。
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 |
+-----+--------+-------------------------+
有什么想法吗?
我有两个版本供您使用
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' )
这个版本和你预想的一样。对于记录中的每一个,您将获得一个OUT副本
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
但如果您不想重复记录,并且只想为该组输出一条记录,您可以使用以下选项:
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下(CASE WHEN Logged='OUT'THEN 1 ELSE 0 END(。对于每一组记录,我都添加了一个指示符,如果没有记录的话。在没有值的情况下将为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')
就我个人而言,我更喜欢使用HAVING,根据一些经验,它有时比大桌子更快。例如,对于我们的示例语句,已经生成下一个统计
表"工作表"。扫描计数0,逻辑读取0,物理读取0,预读读取0,lob逻辑读取0,lob物理读取0预读读取0。表"SignIn"。扫描计数2、逻辑读取2,物理读取0,预读读取0,lob逻辑读取0,lob物理读取0,lob预读读取0。
和exists语句:
表"登录"。扫描计数2,逻辑读取8,物理读取0,预读读取0,lob逻辑读取0,lob物理读取0预读读取0。
看起来HAVING语句比EXISTS 读取更少