我有一个视图,但在select中使用时,结果会因where子句的不同而不同:

select * from cases_keywords2 ck where case_id = 10732 or case_id = 10733 or case_id = 10740`

返回值:

case_id keywords_ids keywords
10732 1,52,118 sanity,automated,alm
10733
10740 1 sanity

但是

select * from cases_keywords2 ck where case_id = 10733 or case_id = 10740

返回:

case_id keywords_ids keywords
10733
10740 118 alm

注意case\u id=10740的更改值.

select
    `c`.`id` AS `case_id`,
    coalesce(group_concat(`t`.`id` separator ','), '') AS `keyword_ids`,
    coalesce(group_concat(`t`.`keyword` separator ','), '') AS `keywords`
from
    (`synergy`.`case` `c`
left join (
    select
        `ck`.`case_id` AS `case_id`,
        `k`.`id` AS `id`,
        `k`.`keyword` AS `keyword`
    from
        (`synergy`.`case_has_keyword` `ck`
    join `synergy`.`keyword` `k` on
        ((`ck`.`keyword_id` = `k`.`id`)))) `t` on
    ((`c`.`id` = `t`.`case_id`)))
group by `c`.`id`;

我try 用视图的源代码替换视图并删除group by,得到了预期的结果:

select
    `c`.`id` AS `case_id`,t.id,t.keyword
from
    (`synergy`.`case` `c`
left join (
    select
        `ck`.`case_id` AS `case_id`,
        `k`.`id` AS `id`,
        `k`.`keyword` AS `keyword`
    from
        (`synergy`.`case_has_keyword` `ck`
    join `synergy`.`keyword` `k` on
        ((`ck`.`keyword_id` = `k`.`id`)))) `t` on
    ((`c`.`id` = `t`.`case_id`)))
where c.id = 10732 or c.id = 10733 or c.id = 10740;
case_id id keyword
10732 1 sanity
10732 52 automated
10732 118 alm
10733
10740 118 alm

但在加回group by后,仍然存在错误的值:

select
    `c`.`id` AS `case_id`,
    coalesce(group_concat(`t`.`id` separator ','), '') AS `keyword_ids`,
    coalesce(group_concat(`t`.`keyword` separator ','), '') AS `keywords`
from
    (`synergy`.`case` `c`
left join (
    select
        `ck`.`case_id` AS `case_id`,
        `k`.`id` AS `id`,
        `k`.`keyword` AS `keyword`
    from
        (`synergy`.`case_has_keyword` `ck`
    join `synergy`.`keyword` `k` on
        ((`ck`.`keyword_id` = `k`.`id`)))) `t` on
    ((`c`.`id` = `t`.`case_id`)))
where c.id = 10732 or c.id = 10733 or c.id = 10740
group by `c`.`id`;
case_id keywords_ids keywords
10732 1,52,118 sanity,automated,alm
10733
10740 1 sanity

有没有办法防止视图和where子句之间的交互?或者可以修改group_concat调用以获得预期结果?

谢谢

编辑:可使用DBFIDLE复制:https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=20822073b30d962065bcd7b8294824fe

推荐答案

解决方案是:

使用Mysql 8.0.29+

将"按c.id分组"更改为"按c.id分组,t.case\u id"

谢谢@wchiquito

Mysql相关问答推荐

在MySQL中存储一条帖子的点赞数量

如何计算超过特定数字的所有不同ID组,然后返回这些ID?

SQL不断返回查询失败,并且不知道从这里到哪里go

当日期附加到MySQL后,如何按日期说明排序?

比较2个mysql json数据类型列

返回包含某一列的分组最大值的行,说明列中的重复值

为大表优化 Django 模型

时间戳上滚动窗口的 SQL 计数不同

如何本地化 MySQL 表中的实体?

当用它的别名替换 (MAX(s.salary) - MIN(s.salary) 它将不起作用.. 为什么?

SQL / MySQL:如何在WHERE IN中判断类似于OR的AND逻辑

INNER JOIN 问题(MySQL 错误代码:1054)

PHP PDO 与普通 mysql_connect

从终端访问 MAMP 的 MySQL

MySQL - 如何 Select 值在数组中的行?

将 Django DB 从 SQLite 迁移到 MySQL 的最佳方法是什么?

MySQL 整数与日期时间索引

在 MySQL 中找不到 outfile 创建的文件

mysql 数据类型仅存储月份和年份

与 MySql 的连接正在自动中止.如何正确配置Connector/J?