mysql - 请任何人帮助我的 mysql 查询案例

Question

我有这些数据..

+------+--------------+------------+
+  id  +  position_id + name       +
+------+--------------+------------+
+  1   +  1           + name_1     +
+  2   +  5           + name_2     +
+  3   +  2           + name_3     +
+  4   +  2           + name_4     +
+  5   +  2           + name_5     +
+  6   +  3           + name_6     +
+  7   +  4           + name_7     +
+  8   +  3           + name_8     +
+  9   +  2           + name_9     +
+------+--------------+------------+

..然后我想要的结果是这样的

+--------------+-----------+----------+----------+-----------+
+  position_id +  result1  + result2  +  result3 +  result4  +
+--------------+-----------+----------+----------+-----------+
+  1           +  name_1   +  @       +  @       +  @        +
+  2           +  name_3   +  name_4  +  name_5  +  name_9   +
+  3           +  name_6   +  name_8  +  @       +  @        +
+  4           +  name_7   +  @       +  @       +  @        +
+  5           +  name_2   +  @       +  @       +  @        +
+--------------+-----------+----------+----------+-----------+

我有一些结果数据的案例，该数据用于我的学校报告。数据结果必须是动态的，跟随主要位置，如果结果为空，则播种@。

有关更多数据或信息，您可以使用以下命令询问

Answer 1

如果列数固定，那么您可以执行以下操作:-

SELECT a.position_id, 
        COALESCE(MIN(a.name), '@') AS result1,
        COALESCE(MIN(b.name), '@') AS result2,
        COALESCE(MIN(c.name), '@') AS result3,
        COALESCE(MIN(d.name), '@') AS result4
FROM some_table a
LEFT OUTER JOIN some_table b ON a.position_id = b.position_id AND a.id < b.id
LEFT OUTER JOIN some_table c ON a.position_id = c.position_id AND b.id < c.id
LEFT OUTER JOIN some_table d ON a.position_id = d.position_id AND c.id < d.id
GROUP BY a.position_id

对于可变数量的列，如果不根据列数动态创建 SQL，或者使用 GROUP_CONCAT 做一些令人讨厌的事情，实际上是不可能的。

但这不太可能有效。

最好进行查询以获得第一个结果，然后在调用脚本中整理格式。

编辑

是时候编写一些令人讨厌的代码了，我仍然需要改进!

第一位是一个存储过程。这会获取最大列数(有点错误，但应该很容易通过一些努力修复，并且现在可以工作)并动态构建 SQL 以创建具有此列数的临时表，然后填充它。

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN

    DECLARE sql1 TEXT;
    DECLARE sql2 TEXT;
    DECLARE sql3 TEXT;
    SET @@group_concat_max_len = 32000;

    SELECT 
            GROUP_CONCAT(CONCAT('MIN(a', (1 + units.iCnt + 10 * tens.iCnt), '.name) AS result', (1 + units.iCnt + 10 * tens.iCnt)) ORDER BY (1 + units.iCnt + 10 * tens.iCnt)),
            GROUP_CONCAT(CONCAT('LEFT OUTER JOIN some_table a', (1 + units.iCnt + 10 * tens.iCnt), ' ON  a', (units.iCnt + 10 * tens.iCnt), '.position_id =  a', (1 + units.iCnt + 10 * tens.iCnt), '.position_id AND a', (units.iCnt + 10 * tens.iCnt), '.id <  a', (1 + units.iCnt + 10 * tens.iCnt), '.id') ORDER BY (1 + units.iCnt + 10 * tens.iCnt) SEPARATOR ' '),
            GROUP_CONCAT(CONCAT('result',(1 + units.iCnt + 10 * tens.iCnt), ' VARCHAR(255)') ORDER BY (1 + units.iCnt + 10 * tens.iCnt))
    INTO sql1, sql2, sql3
    FROM
        (
        SELECT MAX(count_name) as max_count_name
        FROM
        (
            SELECT COUNT(name) as count_name
            FROM some_table
            GROUP BY position_id
        ) sub0
    ) sub1,
    (SELECT 1 iCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) units,
    (SELECT 1 iCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) tens
    WHERE max_count_name >= (units.iCnt + 10 * tens.iCnt);

    DROP TEMPORARY TABLE IF EXISTS temp1;

    SET @sqlmain1 = CONCAT('CREATE TEMPORARY TABLE temp1(position_id INT, result0 VARCHAR(255), ', sql3, ')');
    PREPARE stmt FROM @sqlmain1;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;    

    SET @sqlmain2 = CONCAT('INSERT INTO temp1 SELECT a0.position_id, MIN(a0.name) AS result0,', sql1, ' FROM some_table a0 ', sql2, ' GROUP BY a0.position_id ');

    PREPARE stmt FROM @sqlmain2;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;    
END;;
DELIMITER ;

然后您可以执行此操作，然后从生成的临时表中进行选择。请注意，这两个语句必须在同一个 SQL session 中完成，否则在您执行选择时临时表将消失:-

CALL stored_procedure_name();
SELECT * FROM temp1

希望您能将这些都传递给 Jasper。