主页 / dba / 问题 / 200520
Accepted
Shiva
Asked: 2018-03-17 23:23:00 +0800 CST

识别组中两列之间的相关记录

  • 772

我需要识别和更新Co_Related_Item列下的共同相关记录，如下所示。表名是CoRelated。

场景解释：
在第一组中，即[GroupID] = 1，我们可以看到b=c、c=d 和 e=d，因此b=c=d=e并且需要标记为“是”。它们是相互关联的。但是x与组中的任何项目都不相关，因此它是一个不同的项目。所以我们可以忽略这条记录。

+----------+---------+-------+-------+-----------------+
|                                       Required result|
+----------+---------+-------+-------+-----------------+
| UniqueID | GroupID | Item1 | Item2 | Co_Related_Item |
+----------+---------+-------+-------+-----------------+
|        1 |       1 | x     |       |                 |
|        2 |       1 | b     | c     | YES             |
|        3 |       1 | c     | d     | YES             |
|        4 |       1 | d     |       | YES             |
|        5 |       1 | e     | d     | YES             |
|        6 |       1 | e     |       | YES             |
|        7 |       1 | e     | c     | YES             |
--------------------------------------------------------
|        8 |       2 | a     |       |                 |
|        9 |       2 | b     |       |                 |
--------------------------------------------------------
|       10 |       3 | x     |       |                 |
|       11 |       3 | y     |       |                 |
|       12 |       3 | a     | b     | YES             |
|       13 |       3 | b     |       | YES             |
--------------------------------------------------------
|       14 |       4 | x     |       |                 |
|       15 |       4 | y     |       |                 |
|       16 |       4 | x     |       |                 |
|       17 |       4 | c     |       | YES             |
|       18 |       4 | d     |       | YES             |
|       19 |       4 | d     | c     | YES             |
--------------------------------------------------------
|       20 |       5 | a     | c     | YES             |
|       21 |       5 | b     | c     | YES             |
|       22 |       5 | b     |       | YES             |
|       23 |       5 | c     |       | YES             |
+----------+---------+-------+-------+-----------------+

我的第一次尝试：

Update a SET a.Co_Related_item = 'Y' from CoRelated a inner join CoRelated b on a.[GroupID] = b.[GroupID] and (a.Item1 = b.Item2 or b.item1 = a.Item2)

我的第二次尝试：
我添加了一个新列Flag并执行了如下所示的两步查询。

1)
Update a SET a.Flag = b.Item1 from CoRelated a inner join CoRelated b on a.[GroupID] = b.[GroupID] where (a.Item1 = b.Item2 or b.item1 = a.Item2)

2)
Update a SET a.Flag = b.Item1 from CoRelated a inner join CoRelated b on a.[GroupID] = b.[GroupID] and (a.Item1 = b.Flag or a.item2 = b.Flag)
where a.Flag is null

但是我的查询遗漏了一些项目。

注意：我的表中有 600 万条记录和 300K GroupID。请给出一个有效的查询。

sql-server

1 个回答

  1. Best Answer

    您的更新声明并未涵盖所有可能性。见记录 id 6 和 22；这些记录没有标记。

    为了有一个有效的查询，你还需要在你的表上有一些索引。以 column 开头的GroupID...

    --准备部分

    create table dbo.tblSource
(UniqueID int not null
,GroupID int not null
,Item1 char(1) not null
,Item2 char(1) null
,Result char(1) null
,Flag char(1) null
,FlagRes char(1) null)

insert into dbo.tblSource(UniqueID,GroupID,Item1,Item2,Result)
 select 1 , 1, 'x',NULL, '' UNION ALL
 select 2 , 1, 'b','c', 'Y' UNION ALL
 select 3 , 1, 'c','d', 'Y' UNION ALL
 select 4 , 1, 'd',NULL, 'Y' UNION ALL
 select 5 , 1, 'e','d', 'Y' UNION ALL
 select 6 , 1, 'e',NULL, 'Y' UNION ALL
 select 7 , 1, 'e','c', 'Y' UNION ALL
 select 8 , 2, 'a',NULL, '' UNION ALL
 select 9 , 2, 'b',NULL, '' UNION ALL
 select 10 , 3, 'x',NULL, '' UNION ALL
 select 11 , 3, 'y',NULL, '' UNION ALL
 select 12 , 3, 'a','b', 'Y' UNION ALL
 select 13 , 3, 'b',NULL, 'Y' UNION ALL
 select 14 , 4, 'x',NULL, '' UNION ALL
 select 15 , 4, 'y',NULL, '' UNION ALL
 select 16 , 4, 'x',NULL, '' UNION ALL
 select 17 , 4, 'c',NULL, 'Y' UNION ALL
 select 18 , 4, 'd',NULL, 'Y' UNION ALL
 select 19 , 4, 'd','c', 'Y' UNION ALL
 select 20 , 5, 'a','c', 'Y' UNION ALL
 select 21 , 5, 'b','c', 'Y' UNION ALL
 select 22 , 5, 'b',NULL, 'Y' UNION ALL
 select 23 , 5, 'c',NULL, 'Y'

    你的更新

    Update a SET a.Flag = b.Item1 from tblSource a inner join tblSource b on a.[GroupID] = b.[GroupID] where (a.Item1 = b.Item2 or b.item1 = a.Item2)


Update a SET a.Flag = b.Item1 from tblSource a inner join tblSource b on a.[GroupID] = b.[GroupID] and (a.Item1 = b.Flag or a.item2 = b.Flag)
where a.Flag is null

    这就是我带来的：

    update s1
set flagRes = case when exists(select 1 from dbo.tblSource as s2 
                           where s2.GroupID  = s1.GroupID 
                             and s2.UniqueID <> s1.UniqueID 
                             and 
                             (
                             (s1.item2 is null and s1.item1 = s2.item1 and s2.item2 is not null) 
                             or
                             (s1.item2 is null and s1.item1 = s2.item2) 
                             or
                             (s1.item2 is not null and (s1.item2 = s2.item1 or s1.item2 = s2.item2)) 
                             )

                 ) then 'y'else '' end
from tblSource as s1

    选择它，输出：

    select UniqueID,GroupID,Item1,Item2,Result as yourResult
,case when exists(select 1 from dbo.tblSource as s2 
                           where s2.GroupID  = s1.GroupID 
                             and s2.UniqueID <> s1.UniqueID 
                             and 
                             (
                             (s1.item2 is null and s1.item1 = s2.item1 and s2.item2 is not null) 
                             or
                             (s1.item2 is null and s1.item1 = s2.item2) 
                             or
                             (s1.item2 is not null and (s1.item2 = s2.item1 or s1.item2 = s2.item2)) 
                             )

                 ) then 'y'else '' end as queryRes
        ,flag
        ,flagRes
from dbo.tblSource as S1


UniqueID    GroupID Item1   Item2   yourResult  queryRes    flag    flagRes                             
1           1       x       null                            null    null
2           1       b       c       Y           y           c       y
3           1       c       d       Y           y           b       y
4           1       d       null    Y           y           c       y
5           1       e       d       Y           y           d       y
6           1       e       null    Y           y           null    y
7           1       e       c       Y           y           c       y
8           2       a       null                            null    null
9           2       b       null                            null    null
10          3       x       null                            null    null
11          3       y       null                            null    null
12          3       a       b       Y           y           b       y
13          3       b       null    Y           y           a       y
14          4       x       null                            null    null
15          4       y       null                            null    null
16          4       x       null                            null    null
17          4       c       null    Y           y           d       y
18          4       d       null    Y           y           c       y
19          4       d       c       Y           y           c       y
20          5       a       c       Y           y           c       y
21          5       b       c       Y           y           c       y
22          5       b       null    Y           y           null    y
23          5       c       null    Y           y           a       y

    dbfiddle在这里

    • 1

相关问题