一对多没有建立中间表的时候经常会采用分隔符的形式将“多”存储在“一”的一个字段里，这样做的代价是无法向一对多的时候那样直接关联查询，一般采用在程序中分割后分别查询的办法。如下图：如何才能直接用sql语句查询出下图的效果呢？可以借助一个序号表，该表中除了连续的id没有其它字段，id的值范围取决于"一"中存储的信息拆分后的数量。实现sql：SELECTNAME,REPLACE(SUBSTRING_INDEX(mobile, ,, a.id),CONCAT(SUBSTRING_INDEX...
-- ----------------------------
-- Table structure for `TabName`
-- ----------------------------
DROP TABLE IF EXISTS `TabName`;
CREATE TABLE `TabName` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(20) DEFAULT NULL,
`Date` date DEFAULT NULL,
`Scount` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
-- ----------------------------
...
shit from( select 要累加的字段, @tt:=CONCAT(@tt,‘,‘,a.要累加的字段) as shit from 某表 a,(select @tt:=‘‘) b ) c ORDER BY LENGTH(c.shit)desc limit 0,1
存储过程版本:BEGINDECLARE Is_End INT DEFAULT 0;DECLARE _str VARCHAR(50) DEFAULT ‘‘;DECLARE _id INT;DECLARE _answer VARCHAR(50);DECLARE cur CURSOR FOR SELECT id,answer FROM question_library where id > 100 ORDER BY RAND() LIMIT 10;DECLARE CONTI...
* from t_zg_asset_statistic_info a
where a.BATCH_NO=‘ss20170123181418‘
;这是原始数据：这是行转列后的样子：selecta.DEBT_TYPE,
sum(case WHEN DEBT_TYPE =‘0‘ THEN AMOUNT END) ‘初债‘,
sum(case WHEN DEBT_TYPE =‘1‘ THEN AMOUNT END) ‘二级债‘
,CONVERT(group_concat(DEBT_TYPE,‘总量:‘, AMOUNT) USING utf8) as ‘拼接合计‘,
sum(AMOUNT) as ‘合计‘from t_zg_asset_statistic_info a where a.BA...
CREATE TABLE digits (digit INT(1));INSERT INTO digitsVALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);CREATE TABLE sequence (seq INT(3));INSERT INTO sequence (SELECTD1.digit + D2.digit * 10FROMdigits D1CROSS JOIN digits D2);
配置表sequence的结果为0-99的一列数字：SQL：
SELECTSUBSTRING_INDEX(SUBSTRING_INDEX(JoinEventIds, ‘,‘, seq),‘,‘ ,- 1) JoinEventIds
FROMsequence
CROSS JOIN user
WHEREseq BETWEEN...
`name`,MAX(CASE WHEN course=‘语文‘ THENscoreEND) AS 语文,MAX(CASE WHEN course=‘数学‘ THENscoreEND) AS 数学, MAX(CASE WHEN course=‘英语‘ THENscoreEND) AS 英语
FROMstudent
GROUP BY `name`
;
合并字段显示:利用group_cancat(course,”:”,”score”)SELECT`name`,GROUP_CONCAT(course, ":", score) AS 成绩
FROMstudent
GROUP BY`name`;
-- -- -- -- -- -- -- --- - -- -- -- -- -- -- -- -- -- -- -- -- -- --...
最近遇到一需求原始数据如下：mysql> select id,sdkname,sid,date,total_count from u1ge_query_log;+------+----------------+------+------------+-------------+| id | sdkname | sid | date | total_count |+------+----------------+------+------------+-------------+| 521 | ICC_ICCGAME(3) | 11 | 2017-05-01 | 0 || 522 | ICC_ICCGAME(3) | 11 | 2017-05-02 | 0 || 523 | IC...
16) NOT NULL COMMENT ‘学号‘,`stunm` VARCHAR(20) NOT NULL COMMENT ‘学生姓名‘,PRIMARY KEY (`stuid`)
)
COLLATE=‘utf8_general_ci‘
ENGINE=InnoDB; -- 课程表 CREATE TABLE `courses` (`courseno` VARCHAR(20) NOT NULL,`coursenm` VARCHAR(100) NOT NULL,PRIMARY KEY (`courseno`)
)
COMMENT=‘课程表‘
COLLATE=‘utf8_general_ci‘
ENGINE=InnoDB;-- 成绩表
CREATE TABLE `score` (`stuid` VARCHAR(16) NOT NULL,`cour...
SET @EE=‘‘;SELECT @EE:=GROUP_CONCAT(@EE,‘sum(if(val=\‘‘,val,‘\‘‘,‘,id,0)) as ‘,‘‘‘‘,val,‘‘‘‘) from (select DISTINCT val from hl_test)a;SET @str=CONCAT(‘select ‘,@EE,‘ from hl_test group by id‘);PREPARE stmt from @str;EXECUTE stmt;mysql 行转列标签：mys distinct tin group exe mysq val mysql sel 本文系统来源：https://www.cnblogs.com/bignine/p/10090990.html
。具体sql如下：2、使用IF() 进行行转列：3、利用SUM(IF()) 生成列，直接生成结果不再利用子查询二、列转行
建表语句：
CREATE TABLE tb_score1(id INT(11) NOT NULL auto_increment,userid VARCHAR(20) NOT NULL COMMENT ‘用户id‘,cn_score DOUBLE COMMENT ‘语文成绩‘,math_score DOUBLE COMMENT ‘数学成绩‘,en_score DOUBLE COMMENT ‘英语成绩‘,po_score DOUBLE COMMENT ‘政治成绩‘,PRIMARY KEY(id)
)ENGINE = INNODB...
CREATE TABLE `TEST_TB_GRADE2` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`USER_NAME` varchar(20) DEFAULT NULL,
`CN_SCORE` float DEFAULT NULL,
`MATH_SCORE` float DEFAULT NULL,
`EN_SCORE` float DEFAULT ‘0‘,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;insert into TEST_TB_GRADE2(USER_NAME, CN_SCORE, MATH_SCORE, EN_SCORE) values
("张三", 34, 58, 58),
("李四", 45, 87, 45),
("...
CREATE TABLE students( id INT PRIMARY KEY, NAME VARCHAR(11));CREATE TABLE courses( id INT PRIMARY KEY, NAME VARCHAR(11));
CREATE TABLE scores( sid INT, cid INT, score INT, RIMARY KEY (sid,cid)); 3表关联查询：
SELECT st.id,st.name,c.NAME course,sc.score FROM students st LEFT JOIN scores sc ON st.id=sc.sid LEFT JOIN courses c ON sc.cid=c.id将课程分类转为列名，其列值...
Pivot Table Using MySQL - A Complete Guide | WebDevZoomhttp://webdevzoom.com/pivot-table-using-mysql/
Cross-Tabulation (Pivot Tables) with MySQL - CodeProjecthttps://www.codeproject.com/articles/363339/cross-tabulation-pivot-tables-with-mysql
Pivot Tables in MySQLhttps://codingsight.com/pivot-tables-in-mysql/
MySQL存储过程的动态行转列 - 七度尘 - SegmentFault 思否https://segmentfault.com/a/11900000...
username,
MAX(CASE course WHEN ‘chinese‘ THEN score ELSE 0 END) AS ‘语文‘,
MAX(CASE course WHEN ‘math‘ THEN score ELSE 0 END) AS ‘数学‘,
MAX(CASE course WHEN ‘english‘ THEN score ELSE 0 END) AS ‘英语‘
FROM score
GROUP BY username
MySQL行转列标签：png bsp cas rom weight style name ase user 本文系统来源：https://www.cnblogs.com/yangwanhao/p/13055245.html
1. 多列转成一列(列转行) -- 6列转成两列(列转行) 这就是最常见的列转行，主要原理是利用SQL里面的union with temp as (select a.iid_sn, a.product_name, a.sales_figures, a.selling_cost, a.pretax_profit, a.closing_inventory from is_import_detail a,1.多列转成一列(列转行)--6列转成两列(列转行)
这就是最常见的列转行，主要原理是利用SQL里面的union
with temp as(selecta.iid_sn,a.product_name,a.sales_figures,a.selli...
