Table of Contents
原理分析:
总结:
Home Database Mysql Tutorial MySQL逗号分割字段的行列转换测试改进

MySQL逗号分割字段的行列转换测试改进

Jun 07, 2016 pm 02:51 PM
mysql Split Field Improve test Convert comma

p由于很多业务表因为历史原因或者性能原因,都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值(具体结构见下表)。/pp这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果。/p span class=cnblogs_code_copy/spanp style


<p>由于很多业务表因为历史原因或者性能原因,都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值(具体结构见下表)。</p><p>这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果。</p>
Copy after login
<span class="cnblogs_code_copy"></span><p style="margin: 10px auto; line-height: 19px; font-family: verdana, sans-serif; font-size: 13px;">表数据:</p>
Copy after login
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>);
Copy after login
复制代码

 

复制代码
<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>
Copy after login
Copy after login
Copy after login

原理分析:

这个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>
Copy after login
复制代码

原理分析:

这个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>
Copy after login
复制代码 测试实例:

Copy after login
Copy after login
Copy after login
-- 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.如果多个导购同1张单的先分解
-- 加时间段
select a.DJBH,a.je,substring_index(substring_index(a.dgy_list_id,&#39;,&#39;,b.help_topic_id+1),&#39;,&#39;,-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,&#39;,&#39;,&#39;&#39;))+1) and a.djbh=&#39;BP0102_qtsy000070&#39;
order by a.DJBH;

-- 2.取平均值

-- SELECT  help_topic_id  FROM mysql.help_topic
-- 1.如果多个导购同1张单的先分解
-- @zddm
-- @ rq
select a.DJBH,
substring_index(substring_index(a.dgy_list_id,&#39;,&#39;,b.help_topic_id+1),&#39;,&#39;,-1) AS FJID,
substring_index(substring_index(a.dgy_list_mc,&#39;,&#39;,b.help_topic_id+1),&#39;,&#39;,-1) AS FJMC,
FORMAT(a.je/(length(a.dgy_list_id) - length(replace(a.dgy_list_id,&#39;,&#39;,&#39;&#39;))+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,&#39;,&#39;,&#39;&#39;))+1) 
and a.rq BETWEEN UNIX_TIMESTAMP(&#39;2016-04-01&#39;) and UNIX_TIMESTAMP(&#39;2016-05-01&#39;)
and a.djbh=&#39;gd_151125000001&#39;
order by a.DJBH;

-- gd_151125000001 
--3.分解后的指标
-- SELECT  help_topic_id  FROM mysql.help_topic
-- 1.如果多个导购同1张单的先分解
-- @khdm_change 终端代码
-- @start_time 开始时间
-- @end_time  结束时间
-- SELECT * FROM ipos_qtlsd WHERE djbh=&#39;gd_151125000001&#39;
set @khdm_change =&#39;BP0102&#39;;
set @start_time=UNIX_TIMESTAMP(&#39;2016-04-01&#39;);
set @end_time=UNIX_TIMESTAMP(&#39;2016-05-01&#39;);
SELECT FJID,FJMC,SUM(FJJE) 
FROM(
select a.zddm,a.zdmc,a.DJBH,
 substring_index(substring_index(a.dgy_list_id,&#39;,&#39;,b.help_topic_id+1),&#39;,&#39;,-1) AS FJID,
 substring_index(substring_index(a.dgy_list_mc,&#39;,&#39;,b.help_topic_id+1),&#39;,&#39;,-1) AS FJMC,
 FORMAT(a.je/(length(a.dgy_list_id) - length(replace(a.dgy_list_id,&#39;,&#39;,&#39;&#39;))+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,&#39;,&#39;,&#39;&#39;))+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=&#39;gd_151125000001&#39;
-- order by a.DJBH;
Copy after login


Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Will R.E.P.O. Have Crossplay?
1 months ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

MySQL: Simple Concepts for Easy Learning MySQL: Simple Concepts for Easy Learning Apr 10, 2025 am 09:29 AM

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

How to open phpmyadmin How to open phpmyadmin Apr 10, 2025 pm 10:51 PM

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

MySQL: An Introduction to the World's Most Popular Database MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

Why Use MySQL? Benefits and Advantages Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

How to use single threaded redis How to use single threaded redis Apr 10, 2025 pm 07:12 PM

Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads.

MySQL's Place: Databases and Programming MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

MySQL and SQL: Essential Skills for Developers MySQL and SQL: Essential Skills for Developers Apr 10, 2025 am 09:30 AM

MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.

How to build a SQL database How to build a SQL database Apr 09, 2025 pm 04:24 PM

Building an SQL database involves 10 steps: selecting DBMS; installing DBMS; creating a database; creating a table; inserting data; retrieving data; updating data; deleting data; managing users; backing up the database.

See all articles