以下演示將在下表數(shù)據(jù)中進(jìn)行：

其中：id為主鍵用于表的連接；value1為需要統(tǒng)計(jì)的主體，如用戶(hù)等；date為記錄日期。

先說(shuō)結(jié)論

SELECT
    tmp.value1 AS `value1`,
    MONTH(SUBSTRING_INDEX(tmp.ct, ',', 1)) AS `month`,
    LENGTH(tmp.ct) - LENGTH(
REPLACE
    (tmp.ct, ',', '')
) + 1 AS `cnt`
FROM
    (
    SELECT
        id,
        value1,
        GROUP_CONCAT(date_value) AS ct
    FROM
        test_year_record
    GROUP BY
        value1,
        INTERVAL(
            date_value,
            DATE(CONCAT('2022', '-01-01')),
            DATE(CONCAT('2022', '-02-01')),
            DATE(CONCAT('2022', '-03-01')),
            DATE(CONCAT('2022', '-04-01')),
            DATE(CONCAT('2022', '-05-01')),
            DATE(CONCAT('2022', '-06-01')),
            DATE(CONCAT('2022', '-07-01')),
            DATE(CONCAT('2022', '-08-01')),
            DATE(CONCAT('2022', '-09-01')),
            DATE(CONCAT('2022', '-10-01')),
            DATE(CONCAT('2022', '-11-01')),
            DATE(CONCAT('2022', '-12-01')),
            DATE(CONCAT('2023', '-01-01'))
        )
) AS tmp
JOIN test_year_record AS ot
ON
    ot.id = tmp.id
WHERE
    ot.value1 = 1 AND YEAR(SUBSTRING_INDEX(tmp.ct, ',', 1)) = '2022'

注：以’2022’為例，上面結(jié)論中使用了CONCAT方法進(jìn)行字符串拼接，方便了年份替換，可以直接替換置對(duì)應(yīng)的ORM的參數(shù)等。

查詢(xún)結(jié)果

思路及SQL解釋

這個(gè)問(wèn)題可以劃分為如下幾個(gè)子問(wèn)題，我們可以挨個(gè)分析解決：

1. 如何以月份劃分

對(duì)于一個(gè)月份的數(shù)據(jù)可以如下判斷：

date_value >= DATE_ADD(date_value, INTERVAL - DAY(date_value) + 1 DAY)
 AND
data_value <= LAST_DAY(data_value)

解釋一下：

DATE_ADD(date_value, INTERVAL - DAY(date_value) + 1 DAY)：data_value所在月的第一天，原理為在data_value的基礎(chǔ)上加上-DAY(data_value)天數(shù)再+1，當(dāng)然也可以使用DATE_SUB或者去YEAR和MONTH信息再進(jìn)行拼接；

LAST_DAY(date_value)：data_value所在月的最后一天。

一個(gè)月的解決了，那么多個(gè)月的無(wú)非就手寫(xiě)幾個(gè)范圍就可以了（x

當(dāng)然不能手寫(xiě)這些范圍，一方面是很麻煩而且不好看，另一方面是會(huì)給mysql帶來(lái)過(guò)多的計(jì)算量。

那么如何給12月進(jìn)行劃分呢：

INTERVAL() 函數(shù)可以解決我們的問(wèn)題：

INTERVAL( N , n 1 , n 2 , ?   , n 3 N,n_1,n_2,\cdots,n_3 N,n1?,n2?,?,n3?)，其中 N N N為帶判斷是數(shù)據(jù)，后面的 n 1 ∼ n n n_1 \sim n_n n1?∼nn?分別為各個(gè)間斷點(diǎn)，這個(gè)函數(shù)的返回值如下，當(dāng) N < n 1 N < n1 N<n1返回0，當(dāng) n 1 ≤ N < n 2 n_1 \leq N < n_2 n1?≤N<n2?時(shí)返回1，當(dāng) n 2 ≤ N < n 3 n_2 \leq N < n_3 n2?≤N<n3?時(shí)返回2，…，以此類(lèi)推。

據(jù)此，我們可以給一年做一個(gè)分段：

 INTERVAL(
            date_value,
            DATE(CONCAT('2022', '-01-01')), # 一月
            DATE(CONCAT('2022', '-02-01')), # 二月
            DATE(CONCAT('2022', '-03-01')), # 三月
            DATE(CONCAT('2022', '-04-01')), # 四月
            DATE(CONCAT('2022', '-05-01')), # 五月
            DATE(CONCAT('2022', '-06-01')), # 六月
            DATE(CONCAT('2022', '-07-01')), # 七月
            DATE(CONCAT('2022', '-08-01')), # 八月
            DATE(CONCAT('2022', '-09-01')), # 九月
            DATE(CONCAT('2022', '-10-01')), # 十月
            DATE(CONCAT('2022', '-11-01')), # 十一月
            DATE(CONCAT('2022', '-12-01')), # 十二月
            DATE(CONCAT('2023', '-01-01')) # 次年一月，防止次年的數(shù)據(jù)記錄進(jìn)當(dāng)年12月中
        )

注： 這里其實(shí)還有個(gè)問(wèn)題，就是結(jié)果會(huì)返回去年的數(shù)據(jù)（0），可以像我一樣在外查詢(xún)里面進(jìn)行一個(gè)年份判斷，也可以交給java等檢測(cè)。

2.獲取每月數(shù)據(jù)

可以使用GROUP BY子句，以INTERVAL的值進(jìn)行分組（為了保證屬于同一個(gè)value1的數(shù)據(jù)，還需要以value1進(jìn)行分組）。

注：GROUP BY 子句中含有多個(gè)參數(shù)時(shí)，將會(huì)是多條這些數(shù)據(jù)都一樣的記錄分為一組。

僅僅是做了分組是不夠的，我們還需要GROUP_CONCAT()函數(shù)來(lái)獲取一個(gè)分組中的數(shù)據(jù)集。

執(zhí)行完當(dāng)前這步，可以獲取的結(jié)果如下：

3.統(tǒng)計(jì)每月數(shù)據(jù)

在ct這一列中，我們獲取的數(shù)據(jù)是有規(guī)律的，比如一個(gè)日期中會(huì)有兩個(gè)"-"、兩個(gè)日期之間以",“分隔。

這里我們選擇以”,"為標(biāo)志，統(tǒng)計(jì)出有多少個(gè)分隔符，再+1就得到了數(shù)據(jù)的數(shù)量。

至于實(shí)現(xiàn)方式，可以使用如下方式：

即

LENGTH(tmp.ct) - LENGTH(REPLACE(tmp.ct, ',', '')) + 1

4.統(tǒng)計(jì)值與月份相對(duì)應(yīng)

取得GROUP_CONCAT獲取的第一個(gè)日期即可代表這一整個(gè)數(shù)據(jù)所在的月份。

可以使用SUBSTRING_INDEX()函數(shù)，它有三個(gè)參數(shù)，第一個(gè)參數(shù)為待片取的字符串、第二個(gè)參數(shù)為分隔符、第三個(gè)參數(shù)為第幾個(gè)截取到第幾個(gè)分隔符。

如此一來(lái)：

SUBSTRING_INDEX(tmp.ct, ',', 1)

便可以取到該日期，再使用MONTH函數(shù)即可獲取對(duì)應(yīng)的月份。

5.總體整合

我這里是使用了一次子查詢(xún)，子查詢(xún)獲取對(duì)應(yīng)的分組及GROUP_CONCAT數(shù)據(jù)，再交由外查詢(xún)進(jìn)行處理。

結(jié)語(yǔ)

這里給出的方案僅僅是一種方案，也許存在著其他更快更好的解決方案但我沒(méi)有想到，在復(fù)雜問(wèn)題面前一步一步獲取小數(shù)據(jù)是我習(xí)慣，這也就使得很可能出現(xiàn)多個(gè)嵌套著的子查詢(xún)。

到此這篇關(guān)于如何使用MySQL查詢(xún)一年中每月的記錄數(shù)的文章就介紹到這了,更多相關(guān)MySQL查詢(xún)每月記錄數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論