mysql-如何连接字符串并转换为日期字符串? [英] mysql - How to concatenate strings and convert to date the strings?
本文介绍了mysql-如何连接字符串并转换为日期字符串?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE DEFINER=`ninjaboy`@`%` PROCEDURE `getMonthlyTotalScore`(IN ninjaId int, IN month int, IN year int)
BEGIN
DECLARE startDate DATE;
DECLARE endDate DATE;
DECLARE maxDay INTEGER;
SELECT DAY(LAST_DAY(year + '-' + month + '-01')) INTO maxDay;
SET startDate = year + '-' + month + '-01';
SET endDate = year + '-' + month + '-' + maxDay;
SELECT SUM(SCORE) FROM NINJA_ACTIVITY WHERE NINJA_ID = ninjaId AND DATE BETWEEN startDate AND endDate ORDER BY DATE;
END
测试数据:
NINJA_ACTIVITY_ID | NINJA_ID | SCORE | DATE
1 1 24 2012-05-01
2 1 36 2012-05-06
3 1 29 2012-05-11
函数调用:call getTotalMonthlyScore (1, 5, 2012)
我正在尝试根据ninjaId
获得任何忍者的月度得分.
I'm trying to get the monthly score of any ninja based on the ninjaId
.
为什么不起作用?知道我哪里出问题了吗?
Why is not working? Any idea where I am getting wrong?
推荐答案
CONCAT()是关键.
mysql> CREATE PROCEDURE `getMonthlyTotalScore`(IN ninjaId int, IN month int, IN year int)
-> BEGIN
-> DECLARE startDate DATE;
-> DECLARE endDate DATE;
-> DECLARE maxDay INTEGER;
->
-> SELECT year + '-' + month + '-01'; #NOTE THIS
->
->
-> END;
-> |
Query OK, 0 rows affected (0.00 sec)
mysql> call getMonthlyTotalScore(1,5,2012);
-> |
+----------------------------+
| year + '-' + month + '-01' |
+----------------------------+
| 2016 |
+----------------------------+
1 row in set (0.00 sec)
之后:
mysql> CREATE PROCEDURE `getMonthlyTotalScore`(IN ninjaId int, IN month int, IN year int)
-> BEGIN
-> DECLARE startDate DATE;
-> DECLARE endDate DATE;
-> DECLARE maxDay INTEGER;
->
-> SELECT CONCAT(year,'-',month,'-01'); # NOTE THIS
->
->
-> END; |
Query OK, 0 rows affected (0.00 sec)
mysql> call getMonthlyTotalScore(1,5,2012);
-> |
+------------------------------+
| CONCAT(year,'-',month,'-01') |
+------------------------------+
| 2012-5-01 |
+------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
这篇关于mysql-如何连接字符串并转换为日期字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文