我有不同版本的一些错误的数据。我需要找出从旧版本传播的常见错误。在下面的数据示例中,我有兴趣了解此示例中的常见版本错误:"这是一个 8.x 旧错误",即数据中的最后 3 行
9.1.1.5 |xymodel1 |227501| This is a 9.x new bug
8.1.2.9 |xymodel1 |227501| This a 8.x new bug
8.1.1.5 |xymodel1 |227501| This a 8.x new1 bug
7.1.2.30 |xy6700 |287640| This is a 7.x new bug
7.1.2.30 |xy6700 |288185| This is a 7.x new1 bug
9.1.1.5 |xymodel1 |227501| This is a 9.x new bug
10.1.2.30 |xy6700 |288368| This is a 10.x new bug
10.1.1.6 |xymodel1 |227501| This is a 10.x new bug
8.1.1.5 |xymodel1 |227501| This is a 8.x old bug
9.1.1.5 |xymodel1 |227501| This is a 8.x old bug
10.1.1.5 |xymodel1 |227501| This is a 8.x old bug
我运行了以下查询,该查询应该为我提供了 8.x、9.x 和 10.x 中的常见错误,因为存在 AND 条件。 但是在我看到的结果中,其他伴随的行也很少,这在三个版本中并不常见。
select * from issue_table
where
bug_name in (select bug_name from issue_table where version like '8.%' and '9.%' and '10.%')
结果:结果中不应显示前两行,因为它们在各个版本中都不常见。
version model data bug_name
8.1.2.9 xymodel1 227501 This a 8.x new bug
8.1.1.5 xymodel1 227501 This a 8.x new1 bug
8.1.1.5 xymodel1 227501 This is a 8.x old bug
9.1.1.5 xymodel1 227501 This is a 8.x old bug
10.1.1.5 xymodel1 227501 This is a 8.x old bug
我期待:
version model data bug_name
8.1.1.5 xymodel1 227501 This is a 8.x old bug
9.1.1.5 xymodel1 227501 This is a 8.x old bug
10.1.1.5 xymodel1 227501 This is a 8.x old bug
SQL Fiddle链接可以在这里找到:https://www.db-fiddle.com/#&togetherjs=VTGbSRMgIO
你们能帮我编辑查询以选择所有三个版本中的常见错误吗?
这可能是您要查找的内容:
select *
from issue_table
where bug_name in (
select bug_name
from issue_table
where version like '8.%'
or version like '9.%'
or version like '10.%'
group by bug_name
having count(*) = 3)
子选择计算所有三个版本中存在的错误。
您也可以将其编写为连接:
select it.*
from (
select bug_name
from issue_table
where version like '8.%'
or version like '9.%'
or version like '10.%'
group by bug_name
having count(*) = 3
) tmp, issue_table it
where it.bug_name = tmp.bug_name
您可以为每个可能的主要版本使用exists
条件:
select i.*
from issue_table i
where
exists(select 1 from issue_table i1 where i1.bug_name = i.bug_name and i1.version like '8.%')
and exists(select 1 from issue_table i1 where i1.bug_name = i.bug_name and i1.version like '9.%')
and exists(select 1 from issue_table i1 where i1.bug_name = i.bug_name and i1.version like '10.%')
尽管这看起来有点冗长,但这应该是一个有效的选择,前提是您有一个索引(bug_name, version)
.
在您的数据库小提琴中,这将返回:
| version | model | data | bug_name |
| -------- | -------- | ------ | --------------------- |
| 8.1.1.5 | xymodel1 | 227501 | This is a 8.x old bug |
| 9.1.1.5 | xymodel1 | 227501 | This is a 8.x old bug |
| 10.1.1.5 | xymodel1 | 227501 | This is a 8.x old bug |