我有一张调查问卷的回复表。我希望能够将我目前拥有的两个查询放在一个查询中。我已经尝试了几天了,并在这个网站上搜索了很多。
以下是我目前所拥有的:
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确实解决了问题。我从来没有让相关的查询工作。
再次感谢。
您需要使用相关的子查询:
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;