mysql查询优化和字段值的操作

Question

mysql> select * from dts;
+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1  | pr2  | pr3  | pr4  | pr5  |
+----+------+------+--------+------+------+------+------+------+
|  1 |    1 |    1 |      1 |    0 |    0 |    1 |    0 |    2 |
|  2 |    1 |    1 |      2 |    0 |    0 |    0 |    0 |    0 |
|  3 |    1 |    1 |      3 |    0 |    0 |    0 |    1 |    0 |
|  4 |    1 |    1 |      4 |    1 |    0 |    1 |    1 |    3 |
|  5 |    1 |    2 |      5 |    0 |    0 |    0 |    2 |    5 |
|  6 |    1 |    2 |      6 |    0 |    0 |    0 |    0 |    1 |
|  7 |    1 |    2 |      7 |    0 |    1 |    0 |    0 |    0 |
|  8 |    2 |    2 |      1 |    1 |    1 |    1 |    1 |    2 |
|  9 |    2 |    2 |      2 |    0 |    0 |    0 |    0 |    0 |
| 10 |    3 |    2 |      3 |    0 |    0 |    0 |    0 |    0 |
| 11 |    3 |    3 |      1 |    1 |    1 |    0 |    0 |    1 |
| 12 |    3 |    3 |      5 |    0 |    0 |    1 |    1 |    0 |
+----+------+------+--------+------+------+------+------+------+
12 rows in set (0.00 sec)

我想在这里实现的逻辑如下所示：

1. 检查表dts的字段(PR1-PR5)中是否有非零数据？
2. 如果找到带有逗号的concat字段名，假设所有字段都为非零，只需连接字段并停止读取相同键的记录(结合key1、key2以节省执行时间，在上面的表中查看第8行，因为key1=2和key2=2都是非零的，所以停止用相同的键读取下一条记录)，转到next key1，key2

不起作用的如下所示：

目前我使用的代码正在工作，但是当找到足够的字段时，它不会跳过读取行，正如您在当前示例中所看到的，它创建了12行X5 Col = 60行(如果您执行内部select语句，它返回60行)，然后按key1和key2对其进行分组，它能以任何简单的方式完成吗?这种方法也是有效的，因此拥有2-3百万条记录的表将更快。

预期输出

+------+------+---------------------+
| key1 | key2 | prs                 |
+------+------+---------------------+
|    1 |    1 | pr1,pr3,pr4,pr5     |
|    1 |    2 | pr2,pr4,pr5         |
|    2 |    2 | pr1,pr2,pr3,pr4,pr5 |
|    3 |    2 | NULL                |
|    3 |    3 | pr1,pr2,pr3,pr4,pr5 |
+------+------+---------------------+

，因此如何简化它以提高大表的性能，正如您在上面看到的，我的意图很简单，我只想看看key1、key2的每个组合中有多少字段(pr1-pr5)具有非零值。

代码使用

SELECT 
    key1,
    key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM (
    SELECT 
        d.key1,
            d.key2,
            t.pr,
            CASE t.pr
                WHEN 'pr1' THEN pr1
                WHEN 'pr2' THEN pr2
                WHEN 'pr3' THEN pr3
                WHEN 'pr4' THEN pr4
                WHEN 'pr5' THEN pr5
            END val
    FROM
        dts d
    CROSS JOIN (
        SELECT 'pr1' pr UNION ALL 
        SELECT 'pr2' UNION ALL 
        SELECT 'pr3' UNION ALL 
        SELECT 'pr4' UNION ALL 
        SELECT 'pr5'
    ) t
) r
GROUP BY key1 , key2;

结构

DROP TABLE IF EXISTS `dts`;
CREATE TABLE `dts` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` int(11) DEFAULT '-99',
  `key2` int(11) DEFAULT '-99',
  `serial` int(11) DEFAULT '-99',
  `pr1` int(11) DEFAULT '-99',
  `pr2` int(11) DEFAULT '-99',
  `pr3` int(11) DEFAULT '-99',
  `pr4` int(11) DEFAULT '-99',
  `pr5` int(11) DEFAULT '-99',
  PRIMARY KEY (`Id`),
  KEY `main` (`key1`,`key2`,`serial`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;


LOCK TABLES `dts` WRITE;
INSERT INTO `dts` VALUES (1,1,1,1,0,0,1,0,2),(2,1,1,2,0,0,0,0,0),(3,1,1,3,0,0,0,1,0),(4,1,1,4,1,0,1,1,3),(5,1,2,5,0,0,0,2,5),(6,1,2,6,0,0,0,0,1),(7,1,2,7,0,1,0,0,0),(8,2,2,1,1,1,1,1,2),(9,2,2,2,0,0,0,0,0),(10,3,2,3,0,0,0,0,0),(11,3,3,1,1,1,0,0,1),(12,3,3,5,0,0,1,1,0);
UNLOCK TABLES;

区块报价

Answer 1

SELECT key1, key2,
       CONCAT_WS(',',
         IF(pr1=0, NULL, 'pr1'),
         IF(pr2=0, NULL, 'pr2'),
         IF(pr3=0, NULL, 'pr3'),
         IF(pr4=0, NULL, 'pr4'),
         IF(pr5=0, NULL, 'pr5') ) AS prs
    FROM (
           SELECT key1, key2,
               SUM(pr1) AS pr1,
               SUM(pr2) AS pr2,
               SUM(pr3) AS pr3,
               SUM(pr4) AS pr4,
               SUM(pr5) AS pr5
            FROM dts
            GROUP BY key1, key2
         ) AS sums;

如果在3,2行中需要"NULL“，可以添加一个IFNULL来修复它。