引用https://blog.csdn.net/cs_hnu_scw/article/details/105397337

 

文章目錄

• 情景引入
• 基礎知識介紹
• 需求
• 預熱準備
• • 定義測試“書”表的DDL
  • 隨便插入幾條測試資料的DML
• 方案設計
• • 錯誤方案一
  • • 結果
    • 分析
  • 錯誤方案二
  • • 結果
    • 分析
  • 正確方案一
  • • 結果
    • 分析
  • 正確方案二
  • • 結果
    • 分析
  • 正確方案三
  • • 結果
    • 分析
• 總結
• 彩蛋

 

情景引入

1. 旁白：今天的天氣好晴朗，處處好風光！！好風光！！今天的我沒有睡懶覺，而是與小白手牽手在逛街！！！
2. 小白：你看你看，那個店子有好多的書呀！知識的海洋，我來了！
3. 我：翻了個白眼，知識的海洋，對你不應該是知識的地獄嗎？今天怎麼像變了一個人呢？
4. 旁白：我和小白走進了琳琅滿目的書店，各色各樣的書籍展示在眼前！
5. 小白：好奇怪，你快看，那邊有好多重複的書，書名和作者都是一樣的，可是卻有新有舊的。
6. 我：這不是很正常嗎？總不能同樣的書籍就那麼一本書吧。
7. 小白：可是，這相同的書名和編碼的書，我都只想看最新的一本，這個可怎麼辦呢？
8. 我：很簡單呀，你一一對比每種相同的書，然後拿最新的不就好了嗎？不就多花點時間而已嘛，用時間換複雜度也行啦！
9. 小白：那多難整啊。。。我就是要每種書的最新的那一本，你快幫我想更好的辦法；
10. 我：既然，你都說了，要每組書名和編碼一樣的最新的一本，那麼就認真聽我給你分析分析。

基礎知識介紹

這篇文章主要是講解在Mysql中常常遇到的一個需求問題，那麼會涉及到的一些知識點如下：
group by：
order by：
inner join：
left join：
學習連結： 菜鳥教程
PS：這些基礎的就不多說了，大家不懂的就往對應的連結裡面先學習學習再往下面的內容閱讀哦！

需求

求按一定欄位進行分組之後的每組最新或者版本最大的一條資料。
假設：
如書名和書編碼相同時，則獲取版本號最大（或建立時間最近）的一條資料；
PS：本文就以版本號最大為例子吧。實際依葫蘆畫瓢就明白其他的場景了。

預熱準備

定義測試“書”表的DDL

CREATE TABLE `book` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '書名',
  `code` varchar(64) DEFAULT NULL COMMENT '書編碼',
  `create_time` datetime DEFAULT NULL COMMENT '建立時間',
  `version` int(11) DEFAULT NULL COMMENT '版本號',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

•  

PS：簡簡單單的資料欄位，如有不嚴謹的地方請不要在意，這裡僅僅用於說明本文需要講解的場景哦！！！

隨便插入幾條測試資料的DML

INSERT INTO `book` VALUES ('1', '小白', '0001', '2020-04-07 21:07:44', '1');
INSERT INTO `book` VALUES ('2', '小白', '0001', '2020-04-08 21:07:59', '2');
INSERT INTO `book` VALUES ('3', '小黑', '0002', '2020-04-08 21:08:16', '1');
INSERT INTO `book` VALUES ('4', '小明', '0003', '2020-04-01 21:08:28', '2');
INSERT INTO `book` VALUES ('5', '小明', '0003', '2020-04-08 21:08:40', '3');

PS：瞎插幾條資料，以表心意！！！！

方案設計

根據上面的樣本資料再結合對應的需求，那麼，**在理想狀態下，**就是獲取得到如下情況的資料：

PS：為了讓各位看的清楚，就特意按照id從小到大的排序了哈！！

錯誤方案一

採取簡單直接的方式，即先分組和排序一起操作

select *
FROM 
book
GROUP BY name,code 
order by version desc, id asc

• 1
• 2
• 3
• 4
• 5

結果

結論：這樣的方案是錯誤的
對比，理想情況和實際情況，只能說，這種方案當然不行啦。那麼為何不行呢？請繼續看。。。。。

分析

1. 首先，確實是將相同的name和code的內容進行了分組，即同樣的name和code的資料只存在一條，說明group by沒有毛病，是分組了。
2. order by：其中id asc是升序，那麼結果確實也是升序這個沒問題；假設，覺得沒用，那麼大家可以試試沒有加id asc的情況就如下，說明id asc 是生效了。
   
3. order by：其中version desc是倒序，那麼再看下實際情況，確實也是按照version倒序進行排序，那麼說version desc也是有效果的。那麼到底哪裡有問題呢？

結論：這是因為當group by 和order by同時出現的時候，它是先執行group by 分組然後才對分組的結果進行的排序。因此，執行順序是有問題，這樣自然無法實現我們需要的取每組最大版本號的資料了。

錯誤方案二

經過上面錯誤方案的解析，那麼這一種肯定可以。。自信滿滿！！！

select * 
from 
(
select *
from 
book
ORDER BY version desc
)as t1
GROUP BY t1.name,t1.code 
order by id asc

結果

結論：這個方案結果還是不對！！！

分析

1. 通過實際結果觀察，id確實是升序，但是每條資料的version欄位卻不是最大的呀。
2. 通過第一種錯誤方案說到，要先根據version排序，然後再group by 分組。那看看SQL，確實是先排序，再分組了。可是為什麼不行？

結論：該方案還是由於排序和分組的效果被優化導致的。因為當外層存在group by語法時，會導致內層的order by 會失效，而mysql會預設採取“第一條”。關鍵來了，“第一條”，這個第一條並不是排序後的第一條，而是插入資料庫同等分組條件下的順序的第一條哦！
假設，我們把初始的資料變成如下，然後再執行該方案的SQL就發現結果與期望的一樣啦。其原因就是在於上面說的。

=======================================

好氣呀，這兩種方案都不行，那到底怎麼寫呢。
別慌，繼續往下面看

=======================================

正確方案一

select * 
from 
(
select *
from 
book
ORDER BY version desc
limit 100000
)as t1
GROUP BY t1.name,t1.code 
order by id asc

結果

結論：哇塞，實際和預期是一樣的結果了哦！！！

分析

1. 我們對比一下錯誤方案二和現在這種方案，發現，就是因為該mysql內層採取了limit關鍵字進行“分頁”處理。
2. why？why？why？

結論： 因為在內層子查詢中採取limit和order by同時作用的話，就會將子查詢的結果根據對應語法進行實際的先排序後分頁，而不會與外層的group by語法進行優化。因此，這樣的效果就可以實現獲取每組的版本號最大的資料了。
缺點： 很多朋友也應該想到了，那麼就是如果子查詢的結果集的條數是不知道的呢？那麼limit如果小了，就會導致一些資料丟失，自然這樣得到的結果就不準了。那麼如何解決呢？
優化： 既然無法確定實際的查詢資料條數，那麼就可以先查詢一下總的資料條數，然後limit就以該結果作為引數不就可以了嘛。對的，先查詢子查詢的總資料條數，然後limit該結果就可以了。切記，是分開兩個SQL語句了哦！！！！
注意點： 這裡要採取inner join而別採取left join或者right join（它們三者的區別就不多說了），除非你確定你的資料條件中，不存在null的情況，而都是一一對應而都存在，那麼就沒問題；

正確方案二

上面的方案存在一定的問題，那麼還可以怎麼做呢？

select t1.*
from 
book as t1
INNER JOIN
(
SELECT name,code,max(version) as version
from 
book
GROUP BY name,code
)as t2
on 
t1.code = t2.code 
and 
t1.name = t2.name
and 
t1.version = t2.version
order by id asc

結果

結論：實際和預期一樣！

分析

該SQL實現的方式主要是用到了max函式的作用。
（1）我們逐步的拆分來看：
首先，

SELECT name,code,max(version) as version
from 
book
GROUP BY name,code

我們先根據要分組的內容進行group by，注意這時候select的欄位也是要將分組的欄位進行獲取，其次，就是採取max函式獲取我們需要的version最大（同理，如果是建立時間也是一樣）。那麼這樣處理得到的結果是什麼呢？自然，這樣就可以獲取到每組中版本號最大的資料資訊。注意，此時並沒有達到我們想要的結果。因為如果我們還要獲取到版本號最大的其他的欄位的資訊，而此時只是獲取到版本號最大的，而其他欄位並非就是版本號最大對應的所有欄位資訊。
（2）再通過inner join的語法作用。我們在（1）中已經獲取到了每個分組條件以及需要版本號的最大的值，那麼，通過inner join的等值連線，這樣就可以根據“等值原理”獲取到其對應的所有欄位的資訊了呀。那麼就實現了我們的需求；
缺點： 可以發現在最裡層的子查詢中這樣的效率是不好的，是走的全表掃描（PS：如果存在其他的限制條件通過有索引可以實現一點優化），那麼全表掃描的效率就不夠好了。
優化： 在裡層的查詢中，增加一定的限制條件，並且限制條件採取能夠以索引處理；

正確方案三

那麼還有其他的處理方案嗎？

select t1.*
from 
bookas t1
,
(
SELECT name,code,max(version) as version
from 
book
GROUP BY name,code
)as t2
where 
t1.code = t2.code 
and 
t1.name = t2.name
and 
t1.version = t2.version
order by id asc

結果

結論：老鐵，實際和預期一樣，沒毛病！

分析

1. 該方案實際和正確方案二沒什麼特別大的差異；
   將方案二中的inner join等值連線而採取自然連線的方式。實際當加上where條件之後，效果是一致的了。

總結

1. 當然，上面的例子是非常簡單的，但是實際就是能夠表明這種相似場景會存在的問題，而在我們實際中，也不過是限制條件多了一點，然後表的欄位多了一些，而要關聯的表多一點而已。所以，還是希望各位能夠明白上述幾種正確和錯誤方案的原因和原理；
2. 分組取數是相對常見的需求，那麼，我們應該多注重這方面的隱藏問題，否則，只是簡單的想或者資料量少時，那幾種錯誤的方案可能就被誤以為是正確的了。所以，要多多的思考問題；
3. 建議大家可以看看Mysql的執行流程以及底層時，引擎到底是如何工作等等內容，比如，Mysql執行語句是一條鏈還是多分支？Mysql執行的時候是哪一層判斷語法是否正確或錯誤的呢？。所以，路漫漫兮，共同學習！

附加mysql函式SUBSTRING_INDEX(GROUP_CONCAT())

 

select project_id, project_name,
SUBSTRING_INDEX(GROUP_CONCAT(id order by update_time desc),',',1) as id 
      from table1 where  status=4
      
       and parent_project_id in()
      group by project_id