mysql - 连接来自旋转 mysql 的结果值
问题描述
我试图在旋转后获得一个连接的字符串值。
目前,只有一个值并且工作正常。
这是查询:
DROP PROCEDURE IF EXISTS getAllUserLunchReport;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUserLunchReport`(IN `start_date` DATETIME, IN `end_date` DATETIME)
BEGIN
SET GLOBAL group_concat_max_len=4294967295;
SET @SQL = NULL;
SET @start_date = DATE(start_date);
SET @end_date = DATE(end_date);
SELECT
COALESCE(GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN date = "',DATE(issuedDateTime),'" AND lunchStatus = 1 THEN (SELECT CONCAT((SELECT rate FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), "," ,(SELECT rate FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1))) ELSE 0 END) AS `',DATE(issuedDateTime),'`'
) ORDER BY issuedDateTime
), '0 as `NoMatchingRows`') INTO @SQL
FROM `lunch_status`
WHERE DATE(issuedDateTime) BETWEEN @start_date AND @end_date;
SET @SQL
= CONCAT
(
'
SELECT concat(u.firstname," ",u.lastname) as Employee, w.*
FROM users u
INNER JOIN
(SELECT userId, ', @SQL, '
FROM
(
SELECT userId, lunchStatus, DATE(issuedDateTime) as date
FROM `lunch_status`
WHERE DATE(issuedDateTime) BETWEEN "',@start_date,'" AND "',@end_date,'"
) as a
GROUP BY userId) w
ON u.id = w.userId
ORDER BY Employee;
'
);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
我只得到第一个值,没有我试图连接的其他值。
以下是我对结果的预期:
employee | 2019-1-15 | 2019-1-16
----------------------------------------
Jack | 30,140 | 30,140
但是,单独执行连接查询是可行的。
注意:在上面的存储过程中,如果删除分隔符,则会连接值。CONCAT((select ...), (select...))
给30140
.
可能是 的问题quotes
,但我已经尝试了各种方法。
仅供参考,这是@SQL
第一次查询后产生的结果:
SUM(CASE WHEN date = "2019-01-15" AND lunchStatus = 1 THEN (SELECT CONCAT((SELECT rate FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), ",", (SELECT rate FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1))) ELSE 0 END) AS `2019-01-15`,
SUM(CASE WHEN date = "2019-01-16" AND lunchStatus = 1 THEN (SELECT CONCAT((SELECT rate FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), ",", (SELECT rate FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1))) ELSE 0 END) AS `2019-01-16`
看起来有效。
编辑:它似乎与“整数”值一起工作正常。例如
CONCAT((SELECT rate FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), "111" ,(SELECT rate FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1))
给301140
.
但是对于其他字符,/./-/_
,只显示第一个参数:30
。
解决方案
,
当您在中包含时您的代码不起作用的原因CONCAT
是因为它CONCAT
在 a 内SUM
,并且它无法解释30,140
为数字,它可以这样做30140
(因此没有分隔符你会得到一个结果)。在不了解表数据和所需结果的更多详细信息的情况下,很难准确说明如何解决问题,但也许您想要这样的东西:
CASE WHEN date = "2019-01-15" AND lunchStatus = 1 THEN CONCAT((SELECT SUM(rate) FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), ",", (SELECT SUM(rate) FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1)) ELSE 0 END) AS `2019-01-15`
推荐阅读
- powershell - 使用 PowerShell 将大型 csv 上传到表存储(非顺序)
- sql - 如何从sql server的一个表中检索图像并将其保存到另一个表
- javascript - 在提交表单时填写优惠券代码,然后发送表单
- spring - 单独配置分层 Spring 应用程序上下文
- python - 找到小于 X 的立即数
- python - 更改 plt.plot() 中的 x 轴值
- javascript - 在 IF 语句中比较“==”不起作用 Google 表格脚本
- c++ - C++ 将随机值放在乘法图中(嵌套循环)
- python - 基于第三个值的 New_target 列
- javascript - ejs 错误提示“错误:找不到匹配的结束标记”<%="。