列转行SELECT flag,substring_index(substring_index(t.context,‘,‘, b.help_topic_id +1), ‘,‘, -1) as result FROM (select‘aa‘as flag,‘1,2,3,4,5,6,7‘as context unionallselect‘bb‘as flag,‘1,2,3,4,5,6‘as context unionallselect‘cc‘as flag,‘1,2,3,4,5‘as context unionallselect‘dd‘as flag,‘1,2,3,4‘as context unionallselect‘ee‘as flag,‘1,2,3‘as context ) t join (select0as help_topic_...
原始数据如下图所示：（商品的销售明细）date=业务日期；Item=商品名称；saleqty=销售数量-- 建立测试数据（表）create table test (Date varchar(10), item char(10),saleqty int);insert test values(‘2010-01-01‘,‘AAA‘,8);insert test values(‘2010-01-02‘,‘AAA‘,4);insert test values(‘2010-01-03‘,‘AAA‘,5);insert test values(‘2010-01-01‘,‘BBB‘,1);insert test values(‘2010-01-02‘,‘CCC‘,2);insert...
一对多没有建立中间表的时候经常会采用分隔符的形式将“多”存储在“一”的一个字段里，这样做的代价是无法向一对多的时候那样直接关联查询，一般采用在程序中分割后分别查询的办法。如下图：如何才能直接用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...