sql中选择WITHLEFT join时,将现有行标记为重复行

qpgpyjmq  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(278)

我想做一个 LEFT JOIN 合并一个表和视图,并且右表有重复项(用户名)。我想保留它们,但添加另一列以将它们标记为 isDuplicate='TRUE' .
我想用 CASE WHEN EXISTS 但如果不能让它正常工作,就会产生错误的结果:

SELECT vc.id AS [id]
,vc.server AS [server]
,vc.username AS [vc_username]
,s.username AS [s_username]
,s.name AS [name]
,s.email AS [email]
,CASE WHEN EXISTS (SELECT s.username WHERE vc.username = s.username)
       THEN 'TRUE'
       ELSE 'FALSE'
          END AS [isDuplicate]
FROM v_view vc
       LEFT JOIN s_table s ON vc.username = s.username

理想情况下,我不希望将行的第一次出现标记为重复。e、 第一次用户名出现时,它没有标记为重复,第二次它是重复的。如果不可能,那么只需将所有没有唯一用户名的行标记为 isDuplicate='TRUE' .
示例数据:
查看:

id   server   username
1    server1  user1
2    server2  user2
3    server3  user3

表格:

username    name     email
user1       Bob      bob@example.com
user1       BobAdmin bobadm@example.com
user2       John     john@example.com
user3       Daniel   daniel@example.com
user4       Adam     adam@example.com

预期结果:

id  server    vc_username  s_username   name      email                isDuplicate
1   server1   user1        user1        Bob       bob@example.com      FALSE
1   server1   user1        user1        BobAdmin  bobadm@example.com   TRUE
2   server2   user2        user2        John      john@example.com     FALSE
3   server3   user3        user3        Daniel    daniel@example.com   FALSE

如果第一种方法不可行,可选择:

id  server    vc_username  s_username   name      email                isDuplicate
1   server1   user1        user1        Bob       bob@example.com      TRUE
1   server1   user1        user1        BobAdmin  bobadm@example.com   TRUE
2   server2   user2        user2        John      john@example.com     FALSE
3   server3   user3        user3        Daniel    daniel@example.com   FALSE
46scxncf

46scxncf1#

我想你想要 row_number() 或者 count(*) . 要标记所有重复的用户名:

SELECT . . .,
       (CASE WHEN COUNT(*) OVER (PARTITION BY username) > 1
             THEN 'TRUE'
             ELSE 'FALSE'
       END) AS [isDuplicate]
FROM v_view vc LEFT JOIN
     s_table s
     ON vc.username = s.username;

你会用 row_number() . . . = 1 如果你想识别出所有的重复,除了一个。

izj3ouym

izj3ouym2#

你不再需要exists子句了。你可以选择case逻辑或者iif逻辑。

SELECT vc.id AS [id]
,vc.server AS [server]
,vc.username AS [vc_username]
,s.username AS [s_username]
,s.name AS [name]
,s.email AS [email]
,IIF(s.username IS NOT NULL,'TRUE','FALSE') AS IsDuplicate
FROM v_view vc
       LEFT JOIN s_table s ON vc.username = s.username

Or

SELECT vc.id AS [id]
,vc.server AS [server]
,vc.username AS [vc_username]
,s.username AS [s_username]
,s.name AS [name]
,s.email AS [email]
,CASE WHEN s.username IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS IsDuplicate
FROM v_view vc
       LEFT JOIN s_table s ON vc.username = s.username

相关问题