请任何人帮助我的case在mysql查询 [英] Please anyone help about my case in mysql query
问题描述
我有这些数据。
---- + -------------- + ------------ +
+ 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 + @ + @ + @ +
+ -------------- + ----------- + ---------- + ---------- + ----------- +
我有一些结果数据的情况，这些数据是我的学校报告。数据结果必须在位置之后为动态，如果结果为空，将播种@ 。
您可以使用以下命令提出更多数据或信息
如果固定数量的列可以这样做： -
SELECT a.position_id，
COALESCE（MIN（a.name），'@'）AS result1，
COALESCE（MIN（b.name），'@'）AS result2，
COALESCE（MIN（c.name），'@'）AS result3，
COALESCE 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
t可能真的没有动态创建基于列数的SQL，或使用GROUP_CONCAT做一些讨厌的事情。
但这不可能是有效的。
这可能会更好的做一个查询获得第一个结果，然后整理出调用脚本中的格式。
EDIT
时间对于一些讨厌的代码，我仍然需要抛光！
是一个存储过程。这获得了最大数目的列（获得它有点错误，但应该很容易修复与努力，现在工作）和动态建立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 + 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 * ord.iCnt），'ON a'，（units.iCnt + 10 * tens.iCnt），'.position_id = a'，（1 + units.iCnt + 10 * tens.iCnt），'.position_id AND a' ，（unit.iCnt + 10 * tens.iCnt），'.id
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 6 UNION SELECT 6 UNION SELECT 8 UNION SELECT 6 UNION SELECT 0 UNION SELECT 6 UNION SELECT 0 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会话中完成，否则临时表将在执行select时消失： -
CALL stored_procedure_name（）;希望你可以将这两个传递给Jasper。
I have this data..
+------+--------------+------------+
+ 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 +
+------+--------------+------------+
..then I want the the results is like
+--------------+-----------+----------+----------+-----------+
+ 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 + @ + @ + @ +
+--------------+-----------+----------+----------+-----------+
I have some case for resulting data, this data is for my school reports. The data results must be dynamic following primary of position and if the result is empty will sowing @.
For more data or information you can ask with the following command
解决方案 If a fixed number of columns then you could do something like this:-
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
With a variable number of columns it isn't going to be possible really without dynamically creating the SQL based on the number of columns, or doing something nasty with GROUP_CONCAT.
But this isn't likely to be efficient.
It would probably be better to do a query to get the first results and then sort out the formatting in the calling script.
EDIT
Time for some nasty code, and i still needs polishing!
First bit is a stored procedure. This gets the max number of columns (gets it slightly wrong, but should be easy to fix with a bit of effort, and works for now) and dynamically builds up the SQL to create a temp table with this number of columns, and then populates it.
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 ;
You can then execute this and then select from the resulting temp table. Note that both statements must be done in the same SQL session, otherwise the temp table will have disappeared by the time you do the select:-
CALL stored_procedure_name();
SELECT * FROM temp1
Hopefully you can pass these both to Jasper.
这篇关于请任何人帮助我的case在mysql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
