我有一个视图,但在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