主页 / dba / 问题 / 47823
Accepted
Jacobm001
Asked: 2013-08-09 12:33:19 +0800 CST

使用通配符压缩查询结果

  • 772

我有以下查询：

select programVersion, count(programVersion) 
     from records R 
     inner join Programs P 
         on P.programID=R.usageProgramID 
     where programName="word" 
     group by programVersion;

结果：

+----------------+-----------------------+
| programVersion | count(programVersion) |
+----------------+-----------------------+
| 12.0.0         |                     1 |
| 12.2.0         |                 32190 |
| 12.2.3         |                    20 |
| 12.2.6         |                  3990 |
| 12.2.8         |                 13128 |
| 12.3.1         |                   114 |
| 12.3.2         |                     4 |
| 12.3.4         |                    20 |
| 14.0.0         |                    57 |
| 14.1.0         |                 86587 |
| 14.1.3         |                    44 |
| 14.1.4         |                   728 |
| 14.2.0         |                    42 |
| 14.2.2         |                   130 |
| 14.2.3         |                 88219 |
| 14.2.5         |                    66 |
| 14.3.0         |                   126 |
| 14.3.1         |                    36 |
| 14.3.2         |                    74 |
+----------------+-----------------------+

为此，我并不真正关心次要版本的增量。我真正感兴趣的是主要增量的差异。有没有办法可以将第一期之后的结果合并起来？

示例输出

+----------------+-----------------------+
| programVersion | count(programVersion) |
+----------------+-----------------------+
| 12             |         total of 12's |
| 14             |         total of 14's |
+----------------+-----------------------+
mysql performance

1 个回答

  1. Best Answer

    您可以使用该SUBSTRING_INDEX()函数获取第一个点之前的部分，然后进行分组：

    select SUBSTRING_INDEX(programVersion, '.', 1) AS programMajorVersion, 
       count(programVersion) AS totalIncrements 
     from records R 
     inner join Programs P 
         on P.programID=R.usageProgramID 
     where programName="word" 
     group by SUBSTRING_INDEX(programVersion, '.', 1) 
     order by CAST(programMajorVersion AS unsigned) ;

    上面的方法在大表上可能不是最有效的。如果您有您的版本使用且高效的索引，您可以将其用作派生表，然后使用子字符串函数进行聚合：

    select SUBSTRING_INDEX(programVersion, '.', 1) AS programMajorVersion, 
       SUM(cnt) AS totalIncrements 
     from  
     ( select programVersion, count(programVersion) AS cnt
         from records R 
         inner join Programs P 
             on P.programID=R.usageProgramID 
         where programName="word" 
         group by programVersion
     ) AS t
     group by SUBSTRING_INDEX(programVersion, '.', 1) 
     order by CAST(programMajorVersion AS unsigned) ;
    • 2

相关问题