mysql中over partition by的具體使用
前言
開發(fā)中遇到了這樣一個需求:統(tǒng)計商品庫存,產(chǎn)品ID + 子產(chǎn)品名稱都相同時,可以確定是同一款商品。當商品來自不同的渠道時,我們要統(tǒng)計每個渠道中最大的那一個。如果在Oracle中可以通過分析函數(shù) OVER(PARTITION BY… ORDER BY…)來實現(xiàn)。在MySQL中應該怎么來實現(xiàn)呢?,F(xiàn)在通過兩種簡單的方式來實現(xiàn)這一需求。
數(shù)據(jù)準備
/*Table structure for table `product_stock` */ CREATE TABLE `product_stock` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `product_id` varchar(10) DEFAULT NULL COMMENT '產(chǎn)品ID', `channel_type` int(11) DEFAULT NULL COMMENT '渠道類型', `branch` varchar(10) DEFAULT NULL COMMENT '子產(chǎn)品', `stock` int(11) DEFAULT NULL COMMENT '庫存', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8; /*Data for the table `product_stock` */ insert into `product_stock` (`id`,`product_id`,`channel_type`,`branch`,`stock`) values (1,'P002',1,'豪華房',23), (2,'P001',1,'高級標間',45), (3,'P003',1,'高級標間',33), (4,'P004',1,'經(jīng)典房',65), (5,'P003',1,'小型套房',45), (6,'P002',2,'高級標間',331), (7,'P005',2,'小型套房',223), (8,'P001',1,'豪華房',99), (9,'P002',3,'高級標間',65), (10,'P003',2,'經(jīng)典房',45), (11,'P004',3,'標準雙床房',67), (12,'P005',2,'小型套房',34), (13,'P001',1,'高級標間',43), (14,'P002',3,'豪華房',56), (15,'P001',3,'高級標間',77), (16,'P005',2,'經(jīng)典房',67), (17,'P003',2,'高級標間',98), (18,'P002',3,'經(jīng)典房',23), (19,'P004',2,'經(jīng)典房',76), (20,'P002',1,'小型套房',123);
通過分組聚合GROUP_CONCAT實現(xiàn)
SELECT product_id, branch, GROUP_CONCAT(t.stock ORDER BY t.stock DESC ) stocks FROM (SELECT * FROM product_stock) t GROUP BY product_id,branch
查詢結(jié)果:
product_id | branch | stocks |
---|---|---|
P001 | 豪華房 | 99 |
P001 | 高級標間 | 77,45,43 |
P002 | 小型套房 | 123 |
P002 | 經(jīng)典房 | 23 |
P002 | 豪華房 | 56,23 |
P002 | 高級標間 | 331,65 |
P003 | 小型套房 | 45 |
P003 | 經(jīng)典房 | 45 |
P003 | 高級標間 | 98,33 |
P004 | 標準雙床房 | 67 |
P004 | 經(jīng)典房 | 76,65 |
P005 | 小型套房 | 223,34 |
P005 | 經(jīng)典房 | 67 |
這也許并不是我們想要的結(jié)果,我們只要stocks中的最大值就可以,那么我們只要用SUBSTRING_INDEX函數(shù)截取一下就可以:
SELECT product_id, branch, SUBSTRING_INDEX(GROUP_CONCAT(t.stock ORDER BY t.stock DESC ),',',1) stock FROM (SELECT * FROM product_stock) t GROUP BY product_id,branch
查詢結(jié)果:
product_id | branch | stock |
---|---|---|
P001 | 豪華房 | 99 |
P001 | 高級標間 | 77 |
P002 | 小型套房 | 123 |
P002 | 經(jīng)典房 | 23 |
P002 | 豪華房 | 56 |
P002 | 高級標間 | 331 |
P003 | 小型套房 | 45 |
P003 | 經(jīng)典房 | 45 |
P003 | 高級標間 | 98 |
P004 | 標準雙床房 | 67 |
P004 | 經(jīng)典房 | 76 |
P005 | 小型套房 | 223 |
P005 | 經(jīng)典房 | 67 |
通過關(guān)聯(lián)查詢及COUNT函數(shù)實現(xiàn)
SELECT * FROM (SELECT t.product_id, t.branch, t.stock, COUNT(*) AS rank FROM product_stock t LEFT JOIN product_stock r ON t.product_id = r.product_id AND t.branch = r.branch AND t.stock <= r.stock GROUP BY t.id) s WHERE s.rank = 1
查詢結(jié)果:
product_id | branch | stock | rank |
---|---|---|---|
P003 | 小型套房 | 45 | 1 |
P002 | 高級標間 | 331 | 1 |
P005 | 小型套房 | 223 | 1 |
P001 | 豪華房 | 99 | 1 |
P003 | 經(jīng)典房 | 45 | 1 |
P004 | 標準雙床房 | 67 | 1 |
P002 | 豪華房 | 56 | 1 |
P001 | 高級標間 | 77 | 1 |
P005 | 經(jīng)典房 | 67 | 1 |
P003 | 高級標間 | 98 | 1 |
P002 | 經(jīng)典房 | 23 | 1 |
P004 | 經(jīng)典房 | 76 | 1 |
P002 | 小型套房 | 123 | 1 |
通過關(guān)聯(lián)表本身,聯(lián)接條件中:t.stock <= r.stock,當t.stock = r.stock時,COUNT出來的數(shù)量是1,當t.stock < r.stock時,COUNT出來的數(shù)量2,3,4…由此可以給所有的數(shù)據(jù)根據(jù)stock字段做一個排序,而這個排序中所有為1的,就是我們所需求的數(shù)據(jù),然后通過按id分組,得到結(jié)果。通過這種方式,也可以實現(xiàn)上面的需求。
到此這篇關(guān)于mysql中over partition by的具體使用的文章就介紹到這了,更多相關(guān)mysql over partition by內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Windows實現(xiàn)通過cmd命令行啟動mysql
介紹了通過Windows命令行啟動MySQL的詳細步驟,包括普通啟動和使用管理員權(quán)限的方法,以及如何登錄和查詢數(shù)據(jù),主要步驟包括修改環(huán)境變量、使用net start命令、確保以管理員身份運行CMD,以及使用MySQL命令行工具進行數(shù)據(jù)庫操作2024-10-10MySQL count(1)、count(*)、count(字段)的區(qū)別
COUNT在數(shù)據(jù)庫行數(shù)統(tǒng)計中被廣泛使用,那么你知道MySQL count(1)、count(*)、count(字段)的區(qū)別嗎,本文就想的介紹一下,感興趣的可以了解一下2021-12-12Mysql?InnoDB引擎中的數(shù)據(jù)頁結(jié)構(gòu)詳解
這篇文章主要為大家介紹了Mysql?InnoDB引擎中的數(shù)據(jù)頁結(jié)構(gòu)詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2022-05-05CentOS系統(tǒng)下如何設(shè)置mysql每天自動備份
備份是容災的基礎(chǔ),是指為防止系統(tǒng)出現(xiàn)操作失誤或系統(tǒng)故障導致數(shù)據(jù)丟失,而將全部或部分數(shù)據(jù)集合從應用主機的硬盤或陣列復制到其它的存儲介質(zhì)的過程。本文將詳細介紹在CentOS系統(tǒng)下如何設(shè)置mysql每天自動備份,有需要的朋友們下面來一起看看吧。2016-10-10JDBC鏈接mysql插入數(shù)據(jù)后顯示問號的原因及解決辦法
這篇文章主要介紹了JDBC鏈接mysql插入數(shù)據(jù)后顯示問號的原因及解決辦法的相關(guān)資料,需要的朋友可以參考下2016-04-04