比较同一表中的 3 列的一组行

一尘不染

比较同一表中的 3 列的一组行

sql

我必须根据 id 为每个组比较同一表的 3 列中的值。最好说明我想要实现的目标：

create table test1 (id nvarchar(8), Name1 nvarchar(10), current_sem nvarchar(10), next_sem nvarchar(10), prev_sem nvarchar(10))

INSERT INTO test1 VALUES ('R001', 'Michael', 'Physics', 'Maths', 'Physics')
INSERT INTO test1 VALUES ('R001', 'Michael', 'Physics', 'Maths', 'Chemistry')
INSERT INTO test1 VALUES ('R003', 'Tim', 'Physics', 'Maths', 'Maths')
INSERT INTO test1 VALUES ('R002', 'John', 'Physics', 'Maths', 'Commerce')
INSERT INTO test1 VALUES ('R003', 'Tim', 'Maths', 'Maths', 'Physics')
INSERT INTO test1 VALUES ('R002', 'John', 'Maths', 'Commerce', 'Physics')
INSERT INTO test1 VALUES ('R002', 'John', 'Commerce', 'Physics', 'Maths')
INSERT INTO test1 VALUES ('R003', 'Tim', 'History', 'Civics', 'HomeEc')
INSERT INTO test1 VALUES ('R003', 'Tim', 'Drama', 'Chemistry', 'HomeEc')

表中的最后 3 列是：current_sem、、next_sem和prev_sem。

对于每个 id，我想找到在 current_sem 或 prev_sum 中存在或不存在的 next_sem 的值，并显示在名为“Sub_to_add”的伪列下。

对于相同的 id，还有另一个名为“Sub_to_remove”的伪列，其中包含在 next_sem 或 prev_sem 中不存在的 current_sem 的值。因此，如果我们看到上述数据 id R001 (Michael) 将 Maths 作为 sub_to_add 因为 Maths 不存在于 R001 的 current_sem 或 prev_sem 中。

理想情况下不应显示 R002，因为所有 next_sem 值都存在于 current_sem 或 prev_sem 中。同样没有 sub_to_remove。

R003 将在 sub_to_add 下有 Civics;Chemistry，在 sub_to_remove 下有 History:Drama。

我不确定如何在代码中显示输出，所以我添加了一个屏幕截图。

我不能next_sem <> current_sem OR next_sem <> prev_sem)在WHERE子句中使用 (，因为同一列的另一行中可能存在一个值 - R002 的示例。

id      |   Name1   |   Sub_to_add          | Sub_to_remove
--------------------------------------------------------------
R001    |   Michael |   Maths               |
R003    |   Tim     |   Civics:Chemistry    | History;Drama

所以，我在这里寻求帮助。此查询将用于 SQL Server 2019 v15。

阅读 81

2022-07-21

共1个答案

一尘不染

感谢您提醒我现在使用 PostgreSQL 而不是 SQL Server 是多么感激。这非常痛苦，主要是因为您的数据模型，但也因为 SQL Server 的限制。

我对您的数据进行了标准化，这简化了计算必要更改的过程。

with norm as (
  select id, Name1, 'current' as sem, current_sem as subj from test1
  union
  select id, Name1, 'next' as sem, next_sem as subj from test1
  union
  select id, Name1, 'prev' as sem, prev_sem as subj from test1
), rules as (
  select id, Name1, 'add' as change, subj
    from norm t
   where sem = 'next'
     and not exists (
           select 1
             from norm
            where id = t.id 
              and sem != t.sem
              and subj = t.subj
       )
  union all
  select id, Name1, 'remove' as change, subj
    from norm t
   where sem = 'current'
     and not exists (
           select 1
             from norm
            where id = t.id
              and sem != t.sem
              and subj = t.subj
       )
)
select t.id, t.Name1, 
       string_agg(
         case
           when r.change = 'add' then subj
           else null
         end,
         ';'
       ) as sub_to_add,
       string_agg(
         case 
           when r.change = 'remove' then subj
           else null
         end,
         ';'
       ) as sub_to_remove
  from (select distinct id, Name1 from test1) t
       left join rules r 
         on r.id = t.id
 group by t.id, t.Name1
 order by t.id
 ;

db<>在这里摆弄

2022-07-21