Mysql Crosstab 验证存在性

Question

我是 stackoverflow 的新手，我在尝试做这个主题时遇到了一些问题。报告。

表格示例

Group  Client   AtributeGroup1 Client1  Atribute1Group1 Client1  Atribute1Group1 Client2  Atribute2Group2 Client3  Atribute1Group3 Client4  Atribute2Group3 Client5  Atribute2Group3 Client5  Atribute2Group3 Client6  Atribute3

Note: The client and Group column will be repetead as this example.

Needed Report

Group  Client Atribute1 Atribute2 Atribute3 Group1 Client1  Yes         no       no Group1 Client2  no          yes      no Group2 Client3  yes         no       no Group3 Client4  no          yes      no Group3 Client5  no          yes      no Group3 Client6  no          no       yes 

The major complication is that we need to validate customer by customer if haver or have not the diferent atributes.

I am stuck in the beginning, I thought something like this:

SELECT
 group,
 client,
 CASE WHEN EXISTS
 (SELECT client FROM table
  WHERE client IN ( SELECT client FROM table
  WHERE Atribute = 3 ) -- The problem is that since there is an attribute for one of the    clients the subquery result true and will go to the "THEN" dor all the clients.
 ) THEN 'Yes' ELSE 'No' END AS Atribute3,
FROM
 table
GROUP BY group, client

任何帮助将不胜感激。原谅我的英语!致以诚挚的问候。

Answer 1

你可以用动态 SQL 来做到这一点

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN atribute = ''',
                atribute,
                ''' THEN ''yes'' ELSE ''no'' END) ',
                atribute))
  INTO @sql
  FROM table1;

SET @sql = CONCAT('SELECT `group`, client, ', @sql, ' 
                   FROM table1 
                   GROUP BY `group`, client');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

输出:

|  GROUP |  CLIENT | ATRIBUTE1 | ATRIBUTE2 | ATRIBUTE3 ||--------|---------|-----------|-----------|-----------|| Group1 | Client1 |       yes |        no |        no || Group1 | Client2 |        no |       yes |        no || Group2 | Client3 |       yes |        no |        no || Group3 | Client4 |        no |       yes |        no || Group3 | Client5 |        no |       yes |        no || Group3 | Client6 |        no |        no |       yes |

Here is SQLFiddle demo

To simplify things on the calling end you can wrap it into a stored procedure

DELIMITER $$
CREATE PROCEDURE sp_report()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(DISTINCT
           CONCAT('MAX(CASE WHEN atribute = ''',
                  atribute,
                  ''' THEN ''yes'' ELSE ''no'' END) ',
                  atribute))
    INTO @sql
    FROM table1;

  SET @sql = CONCAT('SELECT `group`, client, ', @sql, ' 
                     FROM table1 
                     GROUP BY `group`, client');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

然后像这样使用它

CALL sp_report();

这里是SQLFiddle 演示