PHP SQL SELECT sum(value) 其中 x 像 '%$month%' (foreach)

Question

我有一个表格：

user_id |       month       | value
1   | firstname_1_2017  |   5 
1   | secondname_1_2017 |   4
1   | firstname_2_2017  |   7 
1   | secondname_2_2017 |   8
1   | firstname_3_2017  |   5 
1   | secondname_3_2017 |   4
1   | firstname_4_2017  |   7 
1   | secondname_4_2017 |   8

我需要每个月的总和"价值"和一个foreach。

firstname_1_2017 + secondname_1_2017 = 9

firstname_2_2017 + secondname_2_2017 = 15

我有一个几个月的数组：

$array_months = array( '1','2','3','4','5','6','7','8','9','10','11','12' );

所以我写了(用 php(：

SELECT sum(value)
FROM table
WHERE user_id = $user_id
AND month LIKE '%_$array_months_2017%'

当我写一个foreach时，结果是每个月重复的所有值的总和相同(例如：1月48分，2月48分，3月48分......

我想要这个结果：

您在一月份有"9"分

您在 2 月份有"15"分

等。。。

Answer 1

您应该考虑更改数据库设计以将月份和年份与用户名分开。

这不是一个好方法，但解决方案是从字符串中提取月份年份month并将其分组以计算每个月的总和。

SELECT SUBSTR(month, LOCATE('_', month) + 1) AS month_year, SUM(value)
FROM users
WHERE user_id = 1
GROUP BY month_year;

工作小提琴

Answer 2

您可以使用这样的SUBSTRING_INDEX()函数从month列值中提取数字，从而给出3_2017。

SUBSTRING_INDEX('firstname_3_2017', '_', -2)

然后你可以用CONCAT()将其转换为日期字符串，产生1_3_2017。

CONCAT('1_',SUBSTRING_INDEX('firstname_3_2017', '_', -2))

然后，您可以使用STR_TO_DATE()将其转换为实际的DATE值。

STR_TO_DATE(CONCAT('1_',SUBSTRING_INDEX('firstname_3_2017', '_', -2)),'%d_%m_%Y')

很酷，现在你有一个可以实际使用的数据类型。现在，您可以使用聚合查询来获取好东西。利用萨米尔的小提琴：http://sqlfiddle.com/#!9/8aabd/6/0

SELECT 
SUM(value) value,
user_id,
STR_TO_DATE(CONCAT('1_',SUBSTRING_INDEX(month, '_', -2)),'%d_%m_%Y') month_begin
FROM table
GROUP BY user_id, STR_TO_DATE(CONCAT('1_',SUBSTRING_INDEX(month, '_', -2)),'%d_%m_%Y')

摘要：month列的简单转换可以为您提供有用的信息。

专业提示：在设计表时，尽量不要将具有不同含义的值填充到单个列中。阅读有关规范化的信息。

专业提示2：尽可能使用实际DATE、TIMESTAMP或DATETIME值来存储基于时间的信息。日期算术和基于日期的索引是 DBMS 服务器的强大部分。