Mysql从具有三个条件之一的组中选择一条记录

Question

使用 MySQL。我需要根据以下条件每组storeID + profitCenter提取一条记录:

1)如果组有一个事件记录(active=1)，选择那个记录

2) 如果组没有事件记录选择最近的 (max(id))

3)如果组有多个活跃记录，选择最近的活跃记录

示例表:(为清楚起见添加了空行)

 ID  | storeID | profitCenter | active
 --------------------------------------
 1   | X       | 1            |    1
 2   | X       | 1            |    0
 3   | X       | 1            |    0

 4   | X       | 2            |    0
 5   | X       | 2            |    1

 6   | X       | 3            |    0
 7   | X       | 3            |    0
 8   | X       | 3            |    0

 9   | X       | 4            |    1
10   | X       | 4            |    1
11   | X       | 4            |    0

12   | X       | 5            |    1

13   | X       | 6            |    0

期望的结果:

 ID  | storeID | profitCenter | active
------------------------------------------ 
 1   | X       | 1            |    1
 5   | X       | 2            |    1
 8   | X       | 3            |    0
10   | X       | 4            |    1
12   | X       | 5            |    1
13   | X       | 6            |    0

单个查询会很好，但我认为由于其复杂性，我需要在存储过程中执行此操作。我需要将记录写入一个新表，以便我可以在连接等中使用它。到目前为止，我真正得到的是...

...像这样提取“单个”记录的简单查询...

 SELECT * FROM table GROUP BY storeID, profitCenter HAVING count(*) = 1 AND active = 1
UNION
SELECT * FROM table GROUP BY storeID, profitCenter HAVING count(*) = 1 AND active = 0

...产生...

 ID  | storeID | profitCenter | active
------------------------------------------ 
12   | X       | 5            |    1
13   | X       | 6            |    0

那我可以...

 SELECT sum(active),tmult.* FROM
      (SELECT t.* FROM t LEFT JOIN
           (SELECT t.* FROM t GROUP BY storeID, profitCenter HAVING count(*) = 1 AND active = 1
      UNION
           SELECT t.* FROM t GROUP BY storeID, profitCenter HAVING count(*) = 1 AND active = 0) tsing
      ON t.id = tsing.id
      WHERE tsing.id IS NULL) tmult
 GROUP BY storeid, profitCenter
 HAVING sum(active)=1;

...它给出了每个只有一个事件记录的组的事件记录:

 ID  | storeID | profitCenter | active
 --------------------------------------
 1   | X       | 1            |    1
 5   | X       | 2            |    1

是那些没有事件记录或有多个事件记录的组，我什至无法开始弄清楚。

我采取的方法是否正确？我什至应该在 SQL 中尝试这个吗？我可以编写脚本来执行此操作，但我希望不必走那条路。

如有任何建议，我们将不胜感激。

Answer 1

我相信应该这样做:

 select 
    ifnull(max(active_records.id), max(all_records.id)) id,
    all_records.store_id,
    all_records.profit_center,
    if(max(active_records.id) is null, 0, 1) active
from
    store all_records
    left outer join store active_records on all_records.id = active_records.id and active_records.active = 1
group by
    all_records.store_id,
    all_records.profit_center
;

使用 ddl 和插入的完整示例如下所示:

create table store (
    id int,
    store_id varchar(64),
    profit_center int,
    active bit
);

insert into store values ( 1   , 'X'       , 1            ,    1);
insert into store values ( 2   , 'X'       , 1            ,    0);
insert into store values ( 3   , 'X'       , 1            ,    0);
insert into store values ( 4   , 'X'       , 2            ,    0);
insert into store values ( 5   , 'X'       , 2            ,    1);
insert into store values ( 6   , 'X'       , 3            ,    0);
insert into store values ( 7   , 'X'       , 3            ,    0);
insert into store values ( 8   , 'X'       , 3            ,    0);
insert into store values ( 9   , 'X'       , 4            ,    1);
insert into store values (10   , 'X'       , 4            ,    1);
insert into store values (11   , 'X'       , 4            ,    0);
insert into store values (12   , 'X'       , 5            ,    1);
insert into store values (13   , 'X'       , 6            ,    0);

select 
    ifnull(max(active_records.id), max(all_records.id)) id,
    all_records.store_id,
    all_records.profit_center,
    if(max(active_records.id) is null, 0, 1) active
from
    store all_records
    left outer join store active_records on all_records.id = active_records.id and active_records.active = 1
group by
    all_records.store_id,
    all_records.profit_center
;

+ ------- + ------------- + ------------------ + ----------- +
| id      | store_id      | profit_center      | active      |
+ ------- + ------------- + ------------------ + ----------- +
| 1       | X             | 1                  | 1           |
| 5       | X             | 2                  | 1           |
| 8       | X             | 3                  | 0           |
| 10      | X             | 4                  | 1           |
| 12      | X             | 5                  | 1           |
| 13      | X             | 6                  | 0           |
+ ------- + ------------- + ------------------ + ----------- +
6 rows