p由于很多业务表因为历史原因或者性能原因，都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值（具体结构见下表）。/pp这种模式下，应用常常需要将这个列依据分隔符进行分割，并得到列转行的结果。/p span class=cnblogs_code_copy/spanp style
<p>由于很多业务表因为历史原因或者性能原因，都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值（具体结构见下表）。</p><p>这种模式下，应用常常需要将这个列依据分隔符进行分割，并得到列转行的结果。</p>
<span class="cnblogs_code_copy"></span><p style="margin: 10px auto; line-height: 19px; font-family: verdana, sans-serif; font-size: 13px;">表数据：</p>
| ID | Value |
| 1 | tiny,small,big |
| 2 | small,medium |
| 3 | tiny,big |
期望得到结果：
| ID | Value |
| 1 | tiny |
| 1 | small |
| 1 | big |
| 2 | small |
| 2 | medium |
| 3 | tiny |
| 3 | big |
<span style="line-height: 1.5;">#需要处理的表 </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">create</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">table</span> tbl_name (ID <span style="color: rgb(0, 0, 255); line-height: 1.5;">int</span> ,mSize <span style="color: rgb(0, 0, 255); line-height: 1.5;">varchar</span>(<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">100</span><span style="line-height: 1.5;">)); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> tbl_name <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">1</span>,<span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">tiny,small,big</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> tbl_name <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">2</span>,<span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">small,medium</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> tbl_name <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">3</span>,<span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">tiny,big</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="line-height: 1.5;">); #用于循环的自增表 </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">create</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">table</span> incre_table (AutoIncreID <span style="color: rgb(0, 0, 255); line-height: 1.5;">int</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> incre_table <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">1</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> incre_table <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">2</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> incre_table <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">3</span>);
<span style="color:rgb(0,0,255); line-height:1.5">select</span> a.ID,substring_index(substring_index(a.mSize,<span style="color:rgb(255,0,0); line-height:1.5">'</span><span style="color:rgb(255,0,0); line-height:1.5">,</span><span style="color:rgb(255,0,0); line-height:1.5">'</span>,b.AutoIncreID),<span style="color:rgb(255,0,0); line-height:1.5">'</span><span style="color:rgb(255,0,0); line-height:1.5">,</span><span style="color:rgb(255,0,0); line-height:1.5">'</span>,<span style="color:rgb(128,128,128); line-height:1.5">-</span><span style="color:rgb(128,0,0); line-height:1.5; font-weight:bold">1</span><span style="line-height:1.5">) </span><span style="color:rgb(0,0,255); line-height:1.5">from</span><span style="line-height:1.5"> tbl_name a </span><span style="color:rgb(128,128,128); line-height:1.5">join</span><span style="line-height:1.5"> incre_table b </span><span style="color:rgb(0,0,255); line-height:1.5">on</span> b.AutoIncreID <span style="color:rgb(128,128,128); line-height:1.5"> (length(a.mSize) <span style="color:rgb(128,128,128); line-height:1.5">-</span> length(<span style="color:rgb(255,0,255); line-height:1.5">replace</span>(a.mSize,<span style="color:rgb(255,0,0); line-height:1.5">'</span><span style="color:rgb(255,0,0); line-height:1.5">,</span><span style="color:rgb(255,0,0); line-height:1.5">'</span>,<span style="color:rgb(255,0,0); line-height:1.5">''</span>))<span style="color:rgb(128,128,128); line-height:1.5">+</span><span style="color:rgb(128,0,0); line-height:1.5; font-weight:bold">1</span><span style="line-height:1.5">) </span><span style="color:rgb(0,0,255); line-height:1.5">order</span> <span style="color:rgb(0,0,255); line-height:1.5">by</span> a.ID;</span>
原理分析：
这个join最基本原理是笛卡尔积。通过这个方式来实现循环。
以下是具体问题分析：
length(a.Size) - length(replace(a.mSize,',',''))+1 表示了，按照逗号分割后，改列拥有的数值数量，下面简称n
<span style="color:#00ff">select</span> a.ID,substring_index(substring_index(a.mSize,<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,b.AutoIncreID),<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,<span style="color:#808080">-</span><span style="color:#8000; font-weight:bold">1</span><span style="color:#000000">) </span><span style="color:#00ff">from</span><span style="color:#000000"> tbl_name a </span><span style="color:#808080">join</span><span style="color:#000000"> incre_table b </span><span style="color:#00ff">on</span> b.AutoIncreID <span style="color:#808080"> (length(a.mSize) <span style="color:#808080">-</span> length(<span style="color:#ff0ff">replace</span>(a.mSize,<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,<span style="color:#ff00">''</span>))<span style="color:#808080">+</span><span style="color:#8000; font-weight:bold">1</span><span style="color:#000000">) </span><span style="color:#00ff">order</span> <span style="color:#00ff">by</span> a.ID;</span>
原理分析：
这个join最基本原理是笛卡尔积。通过这个方式来实现循环。
以下是具体问题分析：
length(a.Size) - length(replace(a.mSize,',',''))+1 表示了，按照逗号分割后，改列拥有的数值数量，下面简称n
join过程的伪代码：
根据ID进行循环
{
判断：i 是否
{
获取最靠近第 i 个逗号之前的数据， 即 substring_index(substring_index(a.mSize,',',b.ID),',',-1)
i = i +1
}
ID = ID +1
}
总结：
这种方法的缺点在于，我们需要一个拥有连续数列的独立表（这里是incre_table)。并且连续数列的最大值一定要大于符合分割的值的个数。
例如有一行的mSize 有100个逗号分割的值，那么我们的incre_table 就需要有至少100个连续行。
当然，mysql内部也有现成的连续数列表可用。如mysql.help_topic： help_topic_id 共有504个数值，一般能满足于大部分需求了。
改写后如下:
<span style="color:#00ff">select</span> a.ID,substring_index(substring_index(a.mSize,<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,b.help_topic_id<span style="color:#808080">+</span><span style="color:#8000; font-weight:bold">1</span>),<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,<span style="color:#808080">-</span><span style="color:#8000; font-weight:bold">1</span><span style="color:#000000">) </span><span style="color:#00ff">from</span><span style="color:#000000"> tbl_name a </span><span style="color:#808080">join</span><span style="color:#000000"> mysql.help_topic b </span><span style="color:#00ff">on</span> b.help_topic_id <span style="color:#808080"> (length(a.mSize) <span style="color:#808080">-</span> length(<span style="color:#ff0ff">replace</span>(a.mSize,<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,<span style="color:#ff00">''</span>))<span style="color:#808080">+</span><span style="color:#8000; font-weight:bold">1</span><span style="color:#000000">) </span><span style="color:#00ff">order</span> <span style="color:#00ff">by</span> a.ID;</span>
测试实例：
-- SELECT help_topic_id FROM mysql.help_topic -- eg.把一个字段用“，”分隔开组合 select group_concat(user_id ORDER BY user_id ASC) as nids from admin_user SELECT b.did,GROUP_CONCAT(b.sid ORDER BY adjustment DESC,similar DESC) FROM test b GROUP BY b.did -- 1.如果多个导购同１张单的先分解 -- 加时间段 select a.DJBH,a.je,substring_index(substring_index(a.dgy_list_id,',',b.help_topic_id+1),',',-1) from ipos_qtlsd a join mysql.help_topic b on b.help_topic_id < (length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1) and a.djbh='BP0102_qtsy000070' order by a.DJBH; -- 2.取平均值 -- SELECT help_topic_id FROM mysql.help_topic -- 1.如果多个导购同１张单的先分解 -- @zddm -- @ rq select a.DJBH, substring_index(substring_index(a.dgy_list_id,',',b.help_topic_id+1),',',-1) AS FJID, substring_index(substring_index(a.dgy_list_mc,',',b.help_topic_id+1),',',-1) AS FJMC, FORMAT(a.je/(length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1),2) AS FJJE, je from ipos_qtlsd a join mysql.help_topic b on b.help_topic_id < (length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1) and a.rq BETWEEN UNIX_TIMESTAMP('2016-04-01') and UNIX_TIMESTAMP('2016-05-01') and a.djbh='gd_151125000001' order by a.DJBH; -- gd_151125000001 --3.分解后的指标 -- SELECT help_topic_id FROM mysql.help_topic -- 1.如果多个导购同１张单的先分解 -- @khdm_change 终端代码 -- @start_time 开始时间 -- @end_time 结束时间 -- SELECT * FROM ipos_qtlsd WHERE djbh='gd_151125000001' set @khdm_change ='BP0102'; set @start_time=UNIX_TIMESTAMP('2016-04-01'); set @end_time=UNIX_TIMESTAMP('2016-05-01'); SELECT FJID,FJMC,SUM(FJJE) FROM( select a.zddm,a.zdmc,a.DJBH, substring_index(substring_index(a.dgy_list_id,',',b.help_topic_id+1),',',-1) AS FJID, substring_index(substring_index(a.dgy_list_mc,',',b.help_topic_id+1),',',-1) AS FJMC, FORMAT(a.je/(length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1),2) AS FJJE, je from ipos_qtlsd a join mysql.help_topic b on b.help_topic_id < (length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1) and a.rq BETWEEN @start_time and @end_time and a.zd_id=(SELECT id from com_base_kehu where khdm=@khdm_change) ) AA GROUP BY FJID,FJMC -- and a.djbh='gd_151125000001' -- order by a.DJBH;
陳述
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
MySQL索引基數如何影響查詢性能？Apr 14, 2025 am 12:18 AMMySQL索引基数对查询性能有显著影响：1.高基数索引能更有效地缩小数据范围，提高查询效率；2.低基数索引可能导致全表扫描，降低查询性能；3.在联合索引中，应将高基数列放在前面以优化查询。
MySQL：新用戶的資源和教程Apr 14, 2025 am 12:16 AMMySQL學習路徑包括基礎知識、核心概念、使用示例和優化技巧。 1)了解表、行、列、SQL查詢等基礎概念。 2)學習MySQL的定義、工作原理和優勢。 3)掌握基本CRUD操作和高級用法，如索引和存儲過程。 4)熟悉常見錯誤調試和性能優化建議，如合理使用索引和優化查詢。通過這些步驟，你將全面掌握MySQL的使用和優化。
現實世界Mysql：示例和用例Apr 14, 2025 am 12:15 AMMySQL在現實世界的應用包括基礎數據庫設計和復雜查詢優化。 1)基本用法：用於存儲和管理用戶數據，如插入、查詢、更新和刪除用戶信息。 2)高級用法：處理複雜業務邏輯，如電子商務平台的訂單和庫存管理。 3)性能優化：通過合理使用索引、分區表和查詢緩存來提升性能。
MySQL中的SQL命令：實踐示例Apr 14, 2025 am 12:09 AMMySQL中的SQL命令可以分為DDL、DML、DQL、DCL等類別，用於創建、修改、刪除數據庫和表，插入、更新、刪除數據，以及執行複雜的查詢操作。 1.基本用法包括CREATETABLE創建表、INSERTINTO插入數據和SELECT查詢數據。 2.高級用法涉及JOIN進行表聯接、子查詢和GROUPBY進行數據聚合。 3.常見錯誤如語法錯誤、數據類型不匹配和權限問題可以通過語法檢查、數據類型轉換和權限管理來調試。 4.性能優化建議包括使用索引、避免全表掃描、優化JOIN操作和使用事務來保證數據一致性
InnoDB如何處理酸合規性？Apr 14, 2025 am 12:03 AMInnoDB通過undolog實現原子性，通過鎖機制和MVCC實現一致性和隔離性，通過redolog實現持久性。 1）原子性：使用undolog記錄原始數據，確保事務可回滾。 2）一致性：通過行級鎖和MVCC確保數據一致。 3）隔離性：支持多種隔離級別，默認使用REPEATABLEREAD。 4）持久性：使用redolog記錄修改，確保數據持久保存。
MySQL的位置：數據庫和編程Apr 13, 2025 am 12:18 AMMySQL在數據庫和編程中的地位非常重要，它是一個開源的關係型數據庫管理系統，廣泛應用於各種應用場景。 1）MySQL提供高效的數據存儲、組織和檢索功能，支持Web、移動和企業級系統。 2）它使用客戶端-服務器架構，支持多種存儲引擎和索引優化。 3）基本用法包括創建表和插入數據，高級用法涉及多表JOIN和復雜查詢。 4）常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5）性能優化方法包括合理使用索引、優化查詢和使用緩存，最佳實踐包括使用事務和PreparedStatemen
MySQL：從小型企業到大型企業Apr 13, 2025 am 12:17 AMMySQL適合小型和大型企業。 1)小型企業可使用MySQL進行基本數據管理，如存儲客戶信息。 2)大型企業可利用MySQL處理海量數據和復雜業務邏輯，優化查詢性能和事務處理。
幻影是什麼讀取的，InnoDB如何阻止它們（下一個鍵鎖定）？Apr 13, 2025 am 12:16 AMInnoDB通過Next-KeyLocking機制有效防止幻讀。 1）Next-KeyLocking結合行鎖和間隙鎖，鎖定記錄及其間隙，防止新記錄插入。 2）在實際應用中，通過優化查詢和調整隔離級別，可以減少鎖競爭，提高並發性能。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
R.E.P.O.能量晶體解釋及其做什麼（黃色晶體）
3 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
3 週前By尊渡假赌尊渡假赌尊渡假赌
刺客信條陰影：貝殼謎語解決方案
2 週前ByDDD
R.E.P.O.如果您聽不到任何人，如何修復音頻
3 週前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25：如何解鎖Myrise中的所有內容
4 週前By尊渡假赌尊渡假赌尊渡假赌
熱工具
Safe Exam Browser
Safe Exam Browser是一個安全的瀏覽器環境，安全地進行線上考試。該軟體將任何電腦變成一個安全的工作站。它控制對任何實用工具的訪問，並防止學生使用未經授權的資源。
MinGW - Minimalist GNU for Windows
這個專案正在遷移到osdn.net/projects/mingw的過程中，你可以繼續在那裡關注我們。 MinGW：GNU編譯器集合（GCC）的本機Windows移植版本，可自由分發的導入函式庫和用於建置本機Windows應用程式的頭檔；包括對MSVC執行時間的擴展，以支援C99功能。 MinGW的所有軟體都可以在64位元Windows平台上運作。
SecLists
SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合，這些清單在安全評估過程中經常使用，而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表，幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上，他就可以存取所需的每種類型的清單。
WebStorm Mac版
好用的JavaScript開發工具
Dreamweaver CS6
視覺化網頁開發工具
