mysql的SUBSTRING_INDEX 和GROUP_CONCAT

今天在做項目中的一個小子產品，就是查詢所有項目的應收款。當我拿到這個子產品時，并不知道該怎麼做？因為剛畢業，沒有做過這樣的項目，但又不得不做。幸好出生在這個網絡資訊紛飛的年代，于是通過谷歌來查詢解決方法，最終得出了自己的方法。是以，當我們拿到自己沒有做過的項目，千萬不要說不會做，而是通過工具得出解決方案。

應收款放在變更表中，關聯到項目财務科目的id。項目财務表存儲的對應的是一期、二期、三期等應收款。每一期應收款的金額都會變更，因而，需要将某期最近的金額拿出來，然後再進行sum統計。但凡寫的不對，煩請提出。解決問題的思路：

• 按項目科目的id分組
• 取出每組當中最新的金額
• 再進行統計

  GROUP_CONCAT

  這個時候就用到了GROUP_CONCAT。GROUP是組，CONCAT合并多個字元串。顧名思義，它的功能是：将group by産生的同一個分組中的值連接配接起來，傳回一個字元串結果。

GROUP_CONCAT( [DISTINCT] 要連接配接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )

[]中的資料表示可用可不用

如果我們沒有指定分隔符的話，預設是“，”，比如：

-- 拼接的是變更表的id（用這個id來統計變更表的金額），按照時間的降序排列，以财務科目的id進行分組，
-- 取第一條資料，這是最新的資料，可以看下圖
SELECT
    GROUP_CONCAT(
        zmc1.id
        ORDER BY
            zmc1.create_datetime DESC
    ) AS 财務科目id對應變更表的id,
    zmc1.financial_subject_id AS 财務科目id
FROM
    zq_money_change zmc1
WHERE
    zmc1.is_deleted = 
GROUP BY
    zmc1.financial_subject_id;
           

統計結果：

假如，我們不用GROUP_CONCA進行分組，再看看我們的統計結果：

SELECT
    zmc1.id AS 财務科目id對應變更表的id,
    zmc1.financial_subject_id AS 财務科目id
FROM
    zq_money_change zmc1
WHERE
    zmc1.is_deleted = 
GROUP BY
    zmc1.financial_subject_id
ORDER BY
    zmc1.create_datetime ASC
           

項目财務科目對應的id有兩條，而此時隻使用了一條，且還是最舊的一條資料，因而，這不符合我們的需求，是以，需要用GROUP_CONCA分組後統計。

我們使用“<->”，來看看統計後的資料：

SELECT
    GROUP_CONCAT(
        zmc1.id
        ORDER BY
            zmc1.create_datetime DESC SEPARATOR '<->'
    ) AS 财務科目id對應變更表的id,
    zmc1.financial_subject_id AS 财務科目id
FROM
    zq_money_change zmc1
WHERE
    zmc1.is_deleted = 
GROUP BY
    zmc1.financial_subject_id;
           

SUBSTRING_INDEX

我們按照時間的降序排列，以财務科目的id進行分組，并且拼接好變更表的id，怎麼取出字元串中的第一個值？這個值正是我們想要的，比如變更表的中的72這個id。這時，我們就用到了SUBSTRING_INDEX，意思是按照下标截取字元串。

SUBSTRING_INDEX (str,delim,count)，str:要處理的字元串、delim:分隔符、count:計數

針對上文的查詢結果來看：

str：GROUP_CONCAT(zmc1.id ORDER BY zmc1.create_datetime DESC SEPARATOR ‘<->’)

delim：<-> 就是str中的分隔符

count:1，隻取一個資料，比如72

以下的代碼可為：

SELECT
    SUBSTRING_INDEX(GROUP_CONCAT(
        zmc1.id
        ORDER BY
            zmc1.create_datetime DESC SEPARATOR '<->'
    ),'<->',) AS 财務科目id對應變更表的id,
    zmc1.financial_subject_id AS 财務科目id
FROM
    zq_money_change zmc1
WHERE
    zmc1.is_deleted = 
GROUP BY
    zmc1.financial_subject_id;
           

如果，我們把條件改了，會怎麼樣呢？

如果把參數改了，會怎麼樣呢？

得出最終的結果

-- 應收款
-- 如果變更表的id在上文的臨時表中存在，我們就統計改id對應的金額
-- 這也是我為什麼拼接變跟表id的原因
-- 需要什麼，拼接什麼
SELECT
  zmc.new_project AS 每期應收款,
  zmc.financial_subject_id as 财務科目的id,
  zmc.id as 變更表的id
FROM
    zq_money_change zmc
WHERE
    zmc.id IN (
        SELECT
            SUBSTRING_INDEX(
                GROUP_CONCAT(
                    zmc1.id
                    ORDER BY
                        zmc1.create_datetime DESC SEPARATOR '<->'
                ),
                '<->',
                
            ) 
        FROM
            zq_money_change zmc1
        WHERE
            zmc1.is_deleted = 
        GROUP BY
            zmc1.financial_subject_id
    )
AND zmc.create_datetime
ORDER BY
    zmc.id ASC;  
           

這樣就好做了，我們可以得到就可以統計每期的金額：

-- 應收款
SELECT
    SUM(zmc.new_project) AS 應付款
FROM
    zq_money_change zmc
WHERE
    zmc.id IN (
        SELECT
            SUBSTRING_INDEX(
                GROUP_CONCAT(
                    zmc1.id
                    ORDER BY
                        zmc1.create_datetime DESC SEPARATOR '<->'
                ),
                '<->',
                
            ) 
        FROM
            zq_money_change zmc1
        WHERE
            zmc1.is_deleted = 
        GROUP BY
            zmc1.financial_subject_id
    )
AND zmc.create_datetime
ORDER BY
    zmc.id ASC;  
           

是以，當我們拿到新的業務時，不要過分的緊張，首先分析該業務涉及到哪些東西，然後再逐漸解決難題。

ps:越努力，越幸運