代码之家  ›  专栏  ›  技术社区  ›  pelkas13

将重复项的值更改为一

  •  0
  • pelkas13  · 技术社区  · 6 年前

    我有一张桌子:

    id location_id company_id
    1            1          1
    2            2          1
    3            3          1
    4            4          5
    5            5          5
    6            6          5
    

    问题是如何将location\u id的所有值更改为一家公司中的最低值,如下所示:

    id location_id company_id
    1            1          1
    2            1          1
    3            1          1
    4            4          5
    5            4          5
    6            4          5
    

    编辑: McNets代码正常工作,但我还有一个问题,这是我编辑的代码:

    update t1
    set location_id = t2.min_loc
    from opinion t1 
    join (select company_id, min(location_id) min_loc
          from opinion
          group by company_id) t2
    on t2.company_id = t1.company_id
    join (select id, house_number, street, city
          from location) t3
    on t3.id = t1.location_id
    where t3.house_number is null and (t3.street is null or t3.street = '') and t3.city is null
    

    它在SQL Server 2016上工作,但我的数据库是MariaDB,我得到一个错误

    Syntax error near 'from opinion t1 join (select company_id, min(location_id) min_loc from op' at line 3
    

    有什么建议吗?

    1 回复  |  直到 6 年前
        1
  •  1
  •   McNets    6 年前
    create table tbl (id int, location_id int, company_id int)
    insert into tbl values
    (1, 1, 1),
    (2, 2, 1),
    (3, 3, 1),
    (4, 4, 5),
    (5, 5, 5),
    (6, 6, 5);
    GO
    
    6 rows affected
    
    update tbl t1
    join (select company_id, min(location_id) min_loc
          from   tbl
          group by company_id) t2
    on t2.company_id = t1.company_id
    set location_id = t2.min_loc;
    
    ✓
    
    select * from tbl;
    
    id | location_id | company_id
    -: | ----------: | ---------:
     1 |           1 |          1
     2 |           1 |          1
     3 |           1 |          1
     4 |           4 |          5
     5 |           4 |          5
     6 |           4 |          5
    

    D小提琴 here