如何除去每个月的周末数据，获取3个月的数据

Select temp, DATE_FORMAT(temp,'%b-%d') from test
where temp between
    concat(year(now()),'-',month(now()) -3,'-', DATE_FORMAT(now(), '%d'))
        and
    (curdate());

I'm using this query to fetch data for 3 months including 7 days a week but i'm not getting results as expected, how can i get data for 5 days a week by this query and ignoring weekend data from database.

我正在使用这个查询获取数据3个月，包括每周7天，但是我没有得到预期的结果，如何通过这个查询获得每周5天的数据，而忽略来自数据库的周末数据。

1 个解决方案

#1

You can use the DAYOFWEEK function, which returns values ranging from 1 (for Sunday) to 7 (for Saturday):

您可以使用DAYOFWEEK函数，它返回的值从1(周日)到7(周六):

SELECT temp, DATE_FORMAT(temp,'%b-%d') FROM test
WHERE
    DAYOFWEEK(temp) BETWEEN 2 AND 6
        AND
    temp BETWEEN
        CONCAT(YEAR(NOW()),'-',MONTH(NOW())-3,'-',DATE_FORMAT(NOW(), '%d'))
            AND
        (CURDATE());

Do note however that your conditions for selecting data for the last 3 months look a little suspicious. Probably simpler doing something like:

但是请注意，您在过去3个月中选择数据的条件看起来有点可疑。可能更简单一些:

SELECT temp, DATE_FORMAT(temp,'%b-%d') FROM test
WHERE
    DAYOFWEEK(temp) BETWEEN 2 AND 6
        AND
    temp >= NOW()-INTERVAL 3 MONTH;

#1