MySQL中查询的动态SQL版本

Question

我有以下MySQL查询，它捕获给定日期相关标准的患者。我想运行这个查询并捕获上个季度每个月的结果。

有没有一种方法可以动态地更改上季度每个月的BETWEEN命令中的日期？我正在寻找优化SQL的方法，而不是复制粘贴查询来更改日期和联合。

SELECT 
m.user_id,
DATE(STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d')) AS DateEXP,
IFNULL(um5.meta_value, 0) AS vID
FROM
wp_usermeta m
    INNER JOIN wp_usermeta um1 ON m.user_id = um1.user_id AND um1.meta_key = 'year'
    INNER JOIN wp_usermeta um2 ON m.user_id = um2.user_id AND um2.meta_key = 'month'
    INNER JOIN wp_usermeta um3 ON m.user_id = um3.user_id AND um3.meta_key = 'day'
    LEFT JOIN wp_usermeta um5 ON m.user_id = um5.user_id AND um5.meta_key = 'vID'
WHERE
STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value),'%Y-%m-%d') BETWEEN '2017-07-01' AND '2017-07-31'
GROUP BY m.user_id

谢谢。

Answer 1

我不确定为什么查询需要GROUP BY，因为您没有聚合。你可以通过加入几个月来做你想做的事情。如下所示：

SELECT m.user_id, mons.mon_start,
       COALESCE(um5.meta_value, 0) AS vID
FROM wp_usermeta m INNER JOIN
     wp_usermeta um1
     ON m.user_id = um1.user_id AND um1.meta_key = 'year' INNER JOIN 
     wp_usermeta um2
     ON m.user_id = um2.user_id AND um2.meta_key = 'month' INNER JOIN
     wp_usermeta um3 
     ON m.user_id = um3.user_id AND um3.meta_key = 'day' LEFT JOIN
     wp_usermeta um5
     ON m.user_id = um5.user_id AND um5.meta_key = 'vID' CROSS JOIN
     (SELECT date('2017-07-01') as mon_start UNION ALL
      SELECT date('2017-08-01') as mon_start UNION ALL
      SELECT date('2017-09-01') as mon_start 
     ) mons
WHERE STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d') >= mons.mon_start AND
     STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d') < mons.mon_start + interval 1 month;