mysql连接两个表，并获取具有非零值的匹配记录的字段名。

Question

我有两张桌子像下面

mysql> show tables;
+-------------------+
| Tables_in_testdbs |
+-------------------+
| dts               |
| ref               |
+-------------------+
2 rows in set (0.00 sec)

每个表的内容如下所示：

mysql> select * from ref;
+----+------+------+
| Id | key1 | key2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    2 |
|  3 |    2 |    2 |
|  4 |    3 |    1 |
|  5 |    3 |    2 |
|  6 |    3 |    3 |
+----+------+------+
6 rows in set (0.00 sec)

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)

，这就是我试图加入两个表的原因

mysql> select distinct
    ->        i.key1,
    ->        i.key2 
    -> from 
    ->        ref i, 
    ->        dts d 
    -> where 
    ->        i.key1=d.key1 and 
    ->        i.key2=d.key2 ;
+------+------+
| key1 | key2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    3 |    3 |
+------+------+
5 rows in set (0.00 sec)

--我期望低于o/p，真的不知道怎么得到它，

key1       key2 fields_non_zero
1           1   pr1,pr3,pr4,pr5
1           2   pr2,pr4,pr5
2           2   pr1,pr2,pr3,pr4,pr5
3           2
3           3   pr1,pr2,pr3,pr4,pr5

我希望使用下面的条件进行检查，例如，让我们使用匹配的两个表的key1=1和key2=1

1. 连接两张表
2. 检查是否有匹配的dts字段(pr1-pr5)中的非零数据。
3. 如果发现字段名带有逗号，
4. 假设所有字段都为非零，只需连接字段，并停止为同一key1、key2进一步连接，因为如果找到所有字段(保存执行时间)，则转到下一个key1，key2

+----+------+------+
| Id | key1 | key2 |
+----+------+------+
|  1 |    1 |    1 |              <- for ref table key1,key2 following rows matches 

| Id | key1 | key2 | serial | pr1  | pr2  | pr3  | pr4  | pr5  |  nonzero_fields 
+----+------+------+--------+------+------+------+------+------+
|  1 |    1 |    1 |      1 |    0 |    0 |    1 |    0 |    2 |  = pr3,pr5
|  2 |    1 |    1 |      2 |    0 |    0 |    0 |    0 |    0 |  =  
|  3 |    1 |    1 |      3 |    0 |    0 |    0 |    1 |    0 |  = pr4
|  4 |    1 |    1 |      4 |    1 |    0 |    1 |    1 |    3 |  = pr1,pr3,pr4,p45

  So distinct of below are

         = pr3,pr5
         = 
         = pr4
         = pr1,pr3,pr4,p45 

 key1   key2 fields_non_zero
 1       1   pr1,pr3,pr4,pr5

我不介意，至少如果我没有像下面这样的命令

 key1   key2 fields_non_zero
 1       1   pr3,pr5,pr4,pr1      

表的结构如下：

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;



DROP TABLE IF EXISTS `ref`;
CREATE TABLE `ref` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` int(11) DEFAULT '-99',
  `key2` int(11) DEFAULT '-99',
  PRIMARY KEY (`Id`),
  KEY `main` (`key1`,`key2`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;


LOCK TABLES `ref` WRITE;
INSERT INTO `ref` VALUES (1,1,1),(2,1,2),(3,2,2),(4,3,1),(5,3,2),(6,3,3);
UNLOCK TABLES;

Answer 1

您可以将数据从dts表中取出，然后在其上使用group_concat。

SELECT 
    r.key1,
    r.key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM
    ref r
        INNER JOIN
    (
    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
    ) d ON r.key1 = d.key1 AND r.key2 = d.key2
GROUP BY r.key1 , r.key2;

生产：

+------+------+---------------------+
| 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 |
+------+------+---------------------+
5 rows in set (0.00 sec)

编辑：

不使用ref表(因为ref表拥有所有的key1、key2和我们只是在内部加入它)：

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;

产生相同的输出：

+------+------+---------------------+
| 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 |
+------+------+---------------------+
5 rows in set (0.00 sec)

编辑2：

SELECT 
    r.key1,
    r.key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM (
    select key1, key2
    from ref
    order by id
    limit 0, 1000       -- Added limit to get only first 1000 key pairs based on id
) r INNER JOIN (
    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
    ) d ON r.key1 = d.key1 AND r.key2 = d.key2
GROUP BY r.key1 , r.key2;

对于头1000个唯一密钥对，请在上面的查询中使用下面的SQL：

(
    select key1, key2
    from ref
    group by key1, key2
    order by key1, key2
    limit 0, 1000       -- Added limit to get only first 1000 key pairs based on id
) r