sql取每組最新資料
引用https://blog.csdn.net/cs_hnu_scw/article/details/105397337
文章目錄
情景引入
- 旁白:今天的天氣好晴朗,處處好風光!!好風光!!今天的我沒有睡懶覺,而是與小白手牽手在逛街!!!
- 小白:你看你看,那個店子有好多的書呀!知識的海洋,我來了!
- 我:翻了個白眼,知識的海洋,對你不應該是知識的地獄嗎?今天怎麼像變了一個人呢?
- 旁白:我和小白走進了琳琅滿目的書店,各色各樣的書籍展示在眼前!
- 小白:好奇怪,你快看,那邊有好多重複的書,書名和作者都是一樣的,可是卻有新有舊的。
- 我:這不是很正常嗎?總不能同樣的書籍就那麼一本書吧。
- 小白:可是,這相同的書名和編碼的書,我都只想看最新的一本,這個可怎麼辦呢?
- 我:很簡單呀,你一一對比每種相同的書,然後拿最新的不就好了嗎?不就多花點時間而已嘛,用時間換複雜度也行啦!
- 小白:那多難整啊。。。我就是要每種書的最新的那一本,你快幫我想更好的辦法;
- 我:既然,你都說了,要每組書名和編碼一樣的最新的一本,那麼就認真聽我給你分析分析。
基礎知識介紹
這篇文章主要是講解在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
結果
結論:這樣的方案是錯誤的
對比,理想情況和實際情況,只能說,這種方案當然不行啦。那麼為何不行呢?請繼續看。。。。。
分析
- 首先,確實是將相同的name和code的內容進行了分組,即同樣的name和code的資料只存在一條,說明group by沒有毛病,是分組了。
- order by:其中id asc是升序,那麼結果確實也是升序這個沒問題;假設,覺得沒用,那麼大家可以試試沒有加id asc的情況就如下,說明id asc 是生效了。
- 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
結果
結論:這個方案結果還是不對!!!
分析
- 通過實際結果觀察,id確實是升序,但是每條資料的version欄位卻不是最大的呀。
- 通過第一種錯誤方案說到,要先根據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
結果
結論:哇塞,實際和預期是一樣的結果了哦!!!
分析
- 我們對比一下錯誤方案二和現在這種方案,發現,就是因為該mysql內層採取了limit關鍵字進行“分頁”處理。
- 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
結果
結論:老鐵,實際和預期一樣,沒毛病!
分析
- 該方案實際和正確方案二沒什麼特別大的差異;
將方案二中的inner join等值連線而採取自然連線的方式。實際當加上where條件之後,效果是一致的了。
總結
- 當然,上面的例子是非常簡單的,但是實際就是能夠表明這種相似場景會存在的問題,而在我們實際中,也不過是限制條件多了一點,然後表的欄位多了一些,而要關聯的表多一點而已。所以,還是希望各位能夠明白上述幾種正確和錯誤方案的原因和原理;
- 分組取數是相對常見的需求,那麼,我們應該多注重這方面的隱藏問題,否則,只是簡單的想或者資料量少時,那幾種錯誤的方案可能就被誤以為是正確的了。所以,要多多的思考問題;
- 建議大家可以看看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
相關文章
- sql 按著時間分組,每組取 20 條資料SQL
- sql 多組條資料取最新的一條資料SQL
- Logstash : 從 SQL Server 讀取資料SQLServer
- SQL:查詢每個類別最新的5條記錄SQL
- MySQL分組後,取出每組最新申請的一條記錄MySql
- Laravel 分組獲取最新記錄Laravel
- MySQL 分組後取最新記錄MySql
- mysql 分組取每個組的前幾名的問題MySql
- SQL 如何計算每個分組的中位數SQL
- Oracle:重複資料去重,只取最新的一條資料Oracle
- MYSQL---SQL語句的資料快取MySql快取
- SQL 如何查詢每個分組都出現的欄位值SQL
- 一句話從 Hive 取每組前三名Hive
- 一句話從 Oracle 取每組前三名Oracle
- 一句話從 Mysql 取每組前三名MySql
- python 獲取全國最新省市區資料,並存入表Python
- 如何建立SQL Server分析系統資料收集組BSSQLServer
- 最新!SQL Server 2019將結合Spark建立統一資料平臺!SQLServerSpark
- sql資料庫查詢某列中每種資訊出現次數並降序排列SQL資料庫
- 關於取每個使用者最新一條留言去重的問題
- 如何通過SQL隱碼攻擊盜取資料庫資訊SQL資料庫
- 在SQL隱碼攻擊中使用DNS獲取資料SQLDNS
- MySQL 多表分組後獲取每一組的時間最大的那條記錄MySql
- 最全最新??中國【省、市、區縣、鄉鎮街道】json,csv,sql資料JSONSQL
- 每一個異常資料
- python爬取股票最新資料並用excel繪製樹狀圖PythonExcel
- Citus 分散式 PostgreSQL 叢集 - SQL Reference(攝取、修改資料 DML)分散式SQL
- SQL資料庫SQL資料庫
- 【SQL】18 SQL NULL 函式、SQL 通用資料型別、SQL 用於各種資料庫的資料型別SQLNull函式資料型別資料庫
- MySQL分組查詢後獲取前N條資料MySql
- 最最最簡單從政府官方獲取最新省市縣聯動資料
- python爬取基金股票最新資料,並用excel繪製樹狀圖PythonExcel
- 達夢資料庫獲取SQL真實的執行計劃資料庫SQL
- SQL Server資料庫恢復,SQL Server資料恢復,SQL Server資料誤刪除恢復工具SQLRescueSQLServer資料庫資料恢復
- SQL 資料型別SQL資料型別
- 建立SQL資料表SQL
- postgresql 多條記錄合併一條,或取最新的一條資料SQL
- Caused by: java.sql.SQLException: 無法從套接字讀取更多的資料JavaSQLException