Mysql Oracle按组合并，行列转换SQL

最新推荐文章于 2021-02-08 04:56:58 发布
最新推荐文章于 2021-02-08 04:56:58 发布
阅读量95 收藏
点赞数
文章标签： python 数据库
原文链接：https://my.oschina.net/laigous/blog/210321
版权

2019独角兽企业重金招聘Python工程师标准>>> hot3.png


public class SqlUtil {

	/**
	 * 按组分类再拼接同类后行列转换sql        <br/>
	 * CREATE	TABLE		TEST_TABLE		<br/>	
	 * ID		NUMBER		PK              <br/>
	 * NAME     VARCHAR		姓名(FK)			<br/>
	 * TYPE		VARCHAR		科目			    <br/>
	 * VALUE	NUMBER		分数                                 <br/>
	 * TERM     VARCHAR     学期                                 <br/>
	 * ID       NAME        TYPE    VALUE  TERM  <br/>
	 * 1		小明(001)	英语		90     2010  <br/>
	 * 2		小明(001)	英语		89     2011  <br/>
	 * 3		小明	(001)	数学		50     2010  <br/>
	 * 4		小明	(001)	数学		81     2011  <br/>
	 * 5		小明	(001)	语文		78     2010  <br/>
	 * 6		小张(002)	数学		89     2010  <br/>
	 * 7		小张(002)	数学		90     2010  <br/>
	 * 8		小张	(002)	英语		59     2010  <br/>
	 * 9		小张	(002)	语文		99     2011  <br/>
	 * 希望得到所有学生各学期的成绩                                         <br/>
	 * classifyMap = {"英语=E","数学=M","语文=C"} <br/>
	 * classifyBaseGroupSql("TYPE",classifyMap,"NAME",",","TEST_TABLE","VALUE","VALUE>80") <br/>
	 * return                                    <br/>
	 * E        M          C        NAME         <br/>
	 * 90,89    81                                               小明(001)     <br/>
	 *          89,90      99                  小张(002)     <br/>
	 * 适用于单表根据字段分组后有多种类型的数据并且该类型有多条记录,
	 * 而又希望分组后只产生一条数据
	 * @param classifyColums (not-null)
	 * 分类的字段,必须为tabel的字段
	 * @param classifyMap (not-null)
	 * 分类的数组Map值,key:classifyColums的值，value:得到的列名
	 * @param classifyGroup (not-null)
	 * 分类的组,必须为tabel的字段
	 * @param groupSeparate (not-null)
	 * info以classifyGroup分组的分隔符
	 * @param tabel (not-null)
	 * 数据库表名 不能为空
	 * @param info (not-null)
	 * 需要的信息 为tabel表字段或字段拼接
	 * @param condition 
	 * 得到信息的条件如果没有则为null，必须为tabel的字段
	 * @return sql字符串
	 */
	public static String classifyBaseGroupSql(String classifyColums,Map<String,String> classifyMap,String classifyGroup,
			String groupSeparate,String tabel,String info,String condition){
		if(CommUtils.isNull(classifyGroup) || CommUtils.isNull(info)||CommUtils.isNull(groupSeparate)
				|| CommUtils.isNull(tabel) || CommUtils.isNull(classifyColums)
				||classifyMap==null||classifyMap.size()==0){
			throw new NullPointerException();
		}
		if(StringUtils.isBlank(condition)){
			condition = " 1=1";
		}
		StringBuffer sql = new StringBuffer("SELECT ");
		/** oracle版
		 //行列转换
		for(String type:classifyMap.keySet()){
			sql.append(" max(decode("+classifyColums+", '"+type+"', str)) "+classifyMap.get(type)+",");
		}
		sql.append(" max(decode("+classifyGroup+", "+classifyGroup+", "+classifyGroup+")) "+classifyGroup
				//按info值拼接
				+" from (select "+classifyGroup+","+classifyColums+",ltrim(max(sys_connect_by_path(val, '"+groupSeparate+"'))) str" +
				 //所有数据分行
				" from (select "+classifyGroup+","+classifyColums+",val," +
						" row_number() over(partition by "+classifyColums+", "+classifyGroup+" order by val) n" +
						//得到符合条件的所有数据
						" from (select "+classifyGroup+","+classifyColums+",("+info+") val" +
								" from "+tabel+" where "+condition+"))t"+
					" start with n = 1 connect by n = prior n + 1" +//拼接条件
					" and "+classifyColums+" = prior "+classifyColums+" and "+classifyGroup+" = prior "+classifyGroup+
					" group by "+classifyColums+", "+classifyGroup+")" +
					" group by "+classifyGroup);**/
		//mysql版
		//行列转换
		for(String type:classifyMap.keySet()){
			sql.append(" MAX(IF("+classifyColums+"='"+type+"', str,'')) "+classifyMap.get(type)+",");
		}
		sql.append("MAX(IF("+classifyGroup+"="+classifyGroup+","+classifyGroup+",''))"+classifyGroup+
				//按info值拼接
				" FROM ( SELECT "+classifyGroup+","+classifyColums+",GROUP_CONCAT(val SEPARATOR '"+groupSeparate+"')str "+
				" FROM (SELECT "+classifyGroup+","+classifyColums+",("+info+") val" +
								" FROM "+tabel+" WHERE "+condition+")t" +
										" GROUP BY "+classifyColums+", "+classifyGroup+")t" +
												" group by "+classifyGroup);
		
		
		return sql.toString();
	}
	
	/**
	 * 按组分类后行列转换sql      例：                   <br/>
	 * CREATE	TABLE		TEST_TABLE		<br/>	
	 * ID		NUMBER		PK              <br/>
	 * NAME     VARCHAR		姓名(FK)			<br/>
	 * TYPE		VARCHAR		科目			    <br/>
	 * VALUE	NUMBER		分数                                 <br/>
	 * ID       NAME        TYPE    VALUE   <br/>
	 * 1		小明(001)	英语		90      <br/>
	 * 2		小明	(001)	数学		50      <br/>
	 * 3		小明	(001)	语文		78      <br/>
	 * 4		小张(002)	数学		89      <br/>
	 * 5		小张	(002)	英语		59      <br/>
	 * 6		小张	(002)	语文		99      <br/>
	 * classifyMap = {"英语=E","数学=M","语文=C"} <br/>
	 * changeLineToRowSql("TYPE",classifyMap,"NAME","TEST_TABLE","VALUE","VALUE>80") <br/>
	 * return                               <br/>
	 * E        M          C        NAME    <br/>
	 * 90                                                                       小明(001)<br/>
	 *          89         99                  小张(002)<br/>
	 * 适用于单表根据字段分组后有多种类型的数据并且该类型只有一条记录,
	 * 而又希望分组后只产生一条数据
	 * @param classifyColums (not-null)
	 * 分类的字段,必须为tabel的字段
	 * @param classifyMap  (not-null)
	 * 分类的数组Map值,key:classifyColums的值，value:得到的列名
	 * @param classifyGroup (not-null)
	 * 分类的组,必须为tabel的字段
	 * @param tabel  (not-null)
	 * 数据库表名
	 * @param info (not-null)
	 * 需要的信息 为tabel表字段或字段拼接
	 * @param condition 
	 * 得到信息的条件如果没有则为null，必须为tabel的字段
	 * @return sql字符串
	 */
	public static String changeLineToRowSql(String classifyColums,Map<String,String> classifyMap,String classifyGroup
			,String tabel,String info,String condition){
		if(CommUtils.isNull(classifyGroup) || CommUtils.isNull(info)
				|| CommUtils.isNull(tabel) || CommUtils.isNull(classifyColums)
				||classifyMap==null||classifyMap.size()==0){
			throw new NullPointerException();
		}
		if(StringUtils.isBlank(condition)){
			condition = " 1=1";
		}
		StringBuffer sql = new StringBuffer("SELECT ");
		/**oracle版
		 for(String type:classifyMap.keySet()){
			sql.append(" max(decode("+classifyColums+", '"+type+"', val)) "+classifyMap.get(type)+",");
		}
		sql.append(" max(decode("+classifyGroup+", "+classifyGroup+", "+classifyGroup+")) "+classifyGroup
				+" from (select "+classifyGroup+","+classifyColums+",("+info+") val" +
								" from "+tabel+" where "+condition+")" +
					" group by "+classifyGroup);**/
		//mysql版
		for(String type:classifyMap.keySet()){
			sql.append(" MAX(IF("+classifyColums+"='"+type+"', val,'')) "+classifyMap.get(type)+",");
		}
		sql.append("MAX(IF("+classifyGroup+"="+classifyGroup+","+classifyGroup+",''))"+classifyGroup+
				" FROM (SELECT "+classifyGroup+","+classifyColums+",("+info+") val" +
				" FROM "+tabel+" WHERE "+condition+")t" +
				" GROUP BY "+classifyGroup);
		
		return sql.toString();
	}
	
	/**
	 * 按组合并  
	 * (只针对单表 进行多个单表字段数据合并)
	 * @EX groupLinkValue("name",",","TEST_TABLE","type || ':' || value"," value > 80 and value < 100 ")
	 * @param classifyGroup (not-null)
	 * 分类的组 (PK，唯一 ，关联表字段)
	 * @param groupSeparate (not-null)
	 * 多行数据合并分隔符
	 * @param tabel (not-null)
	 * 数据库表名
	 * @param info (not-null)
	 * 合并的数据,（单表字段or单表多字段）返回字段别名为val
	 * @param condition  SQL条件语句 
	 * 多个条件用 and 连接
	 * @return 返回NULL 表示参数异常 
	 * @author CHENJIANIAN
	 * @data 2013-5-22
	 */
	public static String groupLinkValue(String classifyGroup,
			String groupSeparate,String tabel,String info,String condition){
		if(CommUtils.isNull(classifyGroup) || CommUtils.isNull(info)
				|| CommUtils.isNull(tabel) || CommUtils.isNull(groupSeparate)){
			return null;
		}
		if(StringUtils.isBlank(condition)){
			condition = " 1=1";
		}
		
		StringBuffer sql = new StringBuffer();
		/**oracle版
		sql.append(" select "+classifyGroup+",max(sys_connect_by_path(val, '"+groupSeparate+"')) val" +
				 //所有数据分行
				" from (select "+classifyGroup+",val," +
						" row_number() over(partition by "+classifyGroup+" order by val) n" +
						//得到符合条件的所有数据
						" from (select "+classifyGroup+",("+info+") val" +
								" from "+tabel+" where "+condition+"))t"+
					" start with n = 1 connect by n = prior n + 1" +//拼接条件
					" and "+classifyGroup+" = prior "+classifyGroup+
					" group by "+classifyGroup);**/
		
		//mysql版
		sql.append("SELECT "+classifyGroup+",GROUP_CONCAT("+info+" SEPARATOR '"+groupSeparate+"') val " +
						" FROM "+tabel+" WHERE "+condition+
						" GROUP BY "+classifyGroup);
		return sql.toString();
	}
	
	public static void main(String args[]){
		Map<String,String> map = new HashMap<String, String>();
		map.put("1", "in_ip");	
		map.put("2", "ip");
		map.put("3", "control_ip");
		System.out.println(SqlUtil.changeLineToRowSql("ip_type", map, 
				"server_id", "host_server_ip", "ip", "1=1 order by ip_type"));
		System.out.println(SqlUtil.groupLinkValue("server_id", ",",  "host_server_ip", "ip", "1=1 order by ip_type"));
		/*
		 * CREATE	TABLE		TEST_TABLE			
		 * ID		NUMBER		PK
		 * NAME     VARCHAR		姓名(FK)			
		 * TYPE		VARCHAR		科目			
		 * VALUE	NUMBER		分数
		 * 
		 * 1		小明(001)	英语		90
		 * 2		小明	(001)	数学		50
		 * 3		小明	(001)	语文		78
		 * 4		小张(002)	数学		89
		 * 5		小张	(002)	英语		59 
		 * 6		小张	(002)	语文		99 
		 * 
		 * 
		 */
		
		groupLinkValue("name",",","TEST_TABLE","type || ':' || value"," value < 60 ");
		/* RETURN
		 * 小明		,数学:50
		 * 小张		,英语:59
		 */
		
		groupLinkValue("name",",","TEST_TABLE","type || ':' || value"," value > 80 and value < 100 ");
		/* RETURN
		 * 小明		,英语:90
		 * 小张		,数学:89,语文:99 
		 */
	}
}




转载于:https://my.oschina.net/laigous/blog/210321

weixin_34378045
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle中SQL语句行列之间相互转换
07-23
本人在工作中遇到了涉及到数据库行列之间相互转换的问题，在网上搜索了很久也没有一个比较完整的解释，通过本人自己的摸索测试，整理出来了Oracle中SQL语句行列之间相互转换的资料，大家可以下载学习。
MySql和Oracle中的行列转换笔记
qq_35341771的博客
08-13 294
建表先： DROP TABLE IF EXISTS `temp`; CREATE TABLE `temp` ( `year` varchar(10) DEFAULT NULL, `month` varchar(10) DEFAULT NULL, `amount` float(6,2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8...
Mysql行列转换sql语句
学亮编程手记
08-23 451
@Select("<script>" + "select d.id driverId,d.name driverName,d.Org_Id orgId,d.cooperation_company_id cooperationCompanyId,\n" + "sum(CASE r.record_type WHEN 1 THEN r.reco...
mysql和oracle转换_oracle和mysql的行列转换
weixin_35988065的博客
02-08 140
CREATE TABLE t(class1 VARCHAR2(2 BYTE),calldate DATE,callcount INTEGER);INSERT INTO t(class1, calldate, callcount)VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);INSERT INTO t(class1, calldate,...
oracle和mysql的行列转换
cl2689163935的博客
07-02 114
CREATE TABLE t( class1 VARCHAR2(2 BYTE), calldate DATE, callcount INTEGER ); INSERT INTO t(clas...
数据库实现行列转换(mysql示例)
09-09
在MySQL中，虽然没有像Oracle那样的Pivot函数，但我们可以通过`CASE`语句结合聚合函数或使用子查询来实现行列转换。这两种方法各有优缺点，具体选择哪种取决于实际需求，例如数据量大小、性能要求和结果的复杂性等。...
mysql 查询行列转换
07-29
本篇文章将深入探讨“MySQL 查询行列转换”的概念及其实际应用，这在数据分析和报表展示时尤其重要。 行转列是数据处理中的常见需求，尤其是在数据透视或汇总分析时。在 MySQL 中，我们可以使用几种方法实现这一...
sql.rar_SQL 脚本
09-20
在SQL编程中，数据操作和分析常常涉及到对数据的行列转换。这是一项关键技能，尤其在处理复杂报表或数据分析任务时。"sql.rar_SQL 脚本"这个压缩包文件显然是一个关于SQL脚本的资源，其中包含了"sql.txt"这样一个...
实际工作中常用sql积累及总结
04-22
无论是关系型数据库如MySQL、Oracle、SQL Server还是NoSQL数据库，SQL都扮演着至关重要的角色。本篇文章将针对"实际工作中常用sql积累及总结"进行深入探讨，涵盖事务处理、游标、存储过程、行列转换、递归查询、...
经典SQL脚本大全
12-24
│ SQL Server到Oracle连接服务器的实现.sql │ SQL Server到SQLBASE连接服务器的实现.sql │ SQL Server到SYBASE连接服务器的实现.sql │ sql导出mysql.sql │ textcopy实现文件存取.sql │ Vb程序实现文件存取.sql...
Mysql 行转列，列转行 SQL语句和示例表结构SQL
04-09
Mysql 行转列，列转行 SQL语句和示例表结构SQL Mysql 行转列，列转行 SQL语句和示例表结构SQL
SQL实现行列转换(MySQL)
Chrispink
07-02 5029
示例数据: (tablename为col_index) 实现行列转换的统计结果: SQL语句: SELECT c2, SUM(CASE when c3 = '正式' then 1 else 0 END) as &quot;正式&quot;, SUM(CASE when c3 = '临时' then 1 else 0 END) as &quot;临时&quot; FROM col_index GROUP BY c2 虽然...
mysql中实现行列转换
王大不明白总有一天会明白
03-07 425
数据库中的表tb为：姓名 课程 分数张三 语文74张三 数学83张三 物理 93李四语文74李四数学84李四物理 94想显示为：姓名语文数学物理李四 748494张三748393select 姓名 as 姓名 ,  max(case 课程 when '语文' then 分数 else 0 end) 语文,  max(case 课程 when '数学' then 分数 else 0 end) 数学,...
Mysql与Oracle关于行转列，列转行问题
执笔编写人生
12-13 724
Mysql实现 1、Mysql行转列 在接下来的例子中，我新建了一个学生成绩表，表SQL如下： DROP TABLE IF EXISTS `stu`; CREATE TABLE `stu` ( `id` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `course` varchar(50) D...
简单的行转列，成绩统计方式，oracle和mysql通用方式
豆芽饭团的博客
05-17 647
在看行转列的时候，看到网上很多资料都写的太复杂，我记录一个比较简单的方式，作为自己行转列的初步学习可以看看，解决比较复杂的业务问题可能帮不上忙，勿喷。 主要实现思路就是，先将行转成列，但是这个时候，会出现： 执行语句： SELECT NAME 姓名, CASE SUBJECT WHEN '语文' THEN score END 语文, CASE SU...
mysql去重合并字符串_MySQL、DM 行转列及字段去重（Group_Concat()）
weixin_27427397的博客
01-26 767
最近在使用数据库迁移适配，由MySQL 库迁移到达梦数据库，其中进行行转列时，MySQL转换达梦sql语法有些问题，特记录。在MySQL 下有Group_Concat(expr) ，在达梦及神通数据库下如何转换，有没有同样或者类似功能的函数呢？请看下面记录。MySQL中Group_Concat(expr) 函数支持 内部自定义连接分割符，使用时只需要 添加SEPARATOR ';'即可(表示...
oracle sql语句转化成mysql sql语句
最新发布
07-08
总之，将Oracle SQL语句转化为MySQL SQL语句需要注意这些差异，并根据具体情况进行适当调整和转换。实际操作中，可以参考MySQL的官方文档和手册，以便获得更详细和准确的转换规则和示例。 ### 回答2： 将Oracle ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值