php - Mysql子查询7天和30天的平均值

Question

我有一张调查问卷答复表。我希望能够将当前的 2 个查询合并到一个查询中。我已经尝试这样做几天了，并且在这个网站上搜索了很多。

这是我到目前为止所拥有的:

select
responses.gname,
responses.client_id,
responses.pri_cou_last,
responses.olm_team,
responses.work,
responses.resDate,
(SELECT round (avg(responses.response),2) FROM responses WHERE responses.formID='2'     AND responses.resDate<=curdate() AND responses.resDate>=DATE_SUB(NOW(), INTERVAL 30 DAY) AND responses.response>'0')
 AS avg30,
    (SELECT round(avg(responses.response),2) FROM responses WHERE responses.formID='2' AND responses.resDate<=curdate() AND responses.resDate>=DATE_SUB(NOW(), INTERVAL 7 DAY) AND    responses.response>'0')
 AS avg7
from responses
    GROUP BY responses.gname,responses.client_id
    ORDER BY responses.pri_cou_last;

这两个子查询没有执行我想要的操作，很可能是因为它们是错误的。他们为我提供了 7 天和 30 天间隔内所有回复的总平均值。

以下是适合我的 7 天查询代码:

SELECT
responses.gname,
responses.olm_team,
responses.work,
round(avgresponses.response),2),
responses.pri_cou_last,
responses.client_id,
visits.client_id,
visits.bed,
visits.depdate_ymd
FROM
responses
LEFT JOIN
visits ON responses.client_id=visits.client_id
WHERE
responses.formID='2'
AND responses.resDate<=curdate() 
 AND responses.resDate>=DATE_SUB(NOW(), INTERVAL 7 DAY) 
 AND responses.response>'0' 
 AND visits.bed>'0' 
 AND visits.depdate_ymd='0000-00-00'
 GROUP BY
 responses.gname,
 responses.client_id
 ORDER BY
 responses.pri_cou_last

提前谢谢您!

感谢您到目前为止的回复。

是的，有一个 formID 列，并且拼写相同。

这是我现在的代码:

 SELECT
   r1.gname,
   r1.client_id,
   r1.pri_cou_last,
   r1.olm_team,
   r1.work,
   r1.resDate,
   round(AVG(CASE WHEN formID = '2' AND response > '0' AND resDate BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND CURDATE() THEN response end),2) as a7,
   round(AVG(CASE WHEN formID = '2' AND response > '0' AND resDate BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND CURDATE() THEN response end),2) as a3,  
   visits.client_id,
   visits.bed,
   visits.depdate_ymd
    FROM
      responses as r1
 LEFT JOIN visits on r1.client_id=visits.client_id
    WHERE visits.bed>'0' 
     GROUP BY r1.client_id,r1.pri_cou_last
     ORDER BY r1.pri_cou_last;

问题是数据不是我想要的。某些行上的 a3 和 a7 返回 NULL 值。而且，行数不正确。

与客户端检查上述查询返回的数据后，我们发现该查询的数据是正确的。它提供了比原始查询更多的数据，并且客户端将数据输入程序的方式会导致该查询出现一些意外的响应。但他们是正确的......

我检查了下面的1个答案，因为使用CASE确实解决了问题。我永远无法让相关查询发挥作用。

再次感谢。

Answer 1

您需要使用相关子查询:

SELECT
    r1.gname,
    r1.client_id,
    r1.pri_cou_last,
    r1.olm_team,
    r1.work,
    r1.resDate,
    (SELECT round (avg(r2.response),2) 
        FROM responses AS r2
        WHERE r2.formID='2'     
            AND r2.resDate<=curdate() 
            AND r2.resDate>=DATE_SUB(NOW(), INTERVAL 30 DAY) 
            AND r2.response>'0'
            AND r2.gname = r1.gname AND r2.client_id = r1.client_id)
     AS avg30,
     (SELECT round(avg(r3.response),2)
        FROM responses AS r3
        WHERE r3.formID='2'
            AND r3.resDate<=curdate()
            AND r3.resDate>=DATE_SUB(NOW(), INTERVAL 7 DAY)
            AND r3.response>'0'
            AND r3.gname = r1.gname AND r3.client_id = r1.client_id)
     AS avg7
FROM responses AS r1
GROUP BY r1.gname,r1.client_id
ORDER BY r1.pri_cou_last;

您也可以不使用子查询来完成此操作:

SELECT
    r1.gname,
    r1.client_id,
    r1.pri_cou_last,
    r1.olm_team,
    r1.work,
    r1.resDate,
    ROUND(AVG(CASE WHEN formID = '2' 
                    AND response > 0 
                    AND resDate BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND CURDATE()
                   THEN response
              END) 2) AS avg30,
    ROUND(AVG(CASE WHEN formID = '2' 
                    AND response > 0 
                    AND resDate BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND CURDATE()
                   THEN response
              END) 2) AS avg7
FROM responses AS r1
GROUP BY r1.gname,r1.client_id
ORDER BY r1.pri_cou_last;