sql 行列转换

  行列转换实例
表ttt有三个字段
seq   --序列
jcxm --检查项目
zhi   --值

数据分别如下：
seq　　　jcxm　　      zhi
-------       --------           --------
11　　　　　1　　　　0.50
11　　　　　2　　　　0.21
11　　　　　3　　　　0.25
12　　　　　1　　　　0.24
12　　　　　2　　　　0.30
12　　　　　3　　　　0.22                             

实现功能
创建视图时移动行值为列值

create view v_view1
as
select seq,
        sum(decode(jcxm,1, zhi)) 检测项目1,
        sum(decode(jcxm,2, zhi)) 检测项目2,
        sum(decode(jcxm,3, zhi)) 检测项目3
from ttt
group by seq;

序号 检测项目1　　检测项目2　　检测项目3
11　　　　　0.50　　　　0.21　　　　　0.25
12　　　　　0.24　　　　0.30　　　　　0.22

技巧：
用THEN中的0和1来进行统计（SUM）

jcxm    zhi
----    ----
a            1
b            1
a            3
d            2
e            4
f            5
a            5
d            3
d            6
b            5
c            4
b            3
求他的zhi既是1，也是3，也是5的jcxm
方法一
select jcxm
from ttt
group by jcxm
having sum(decode(zhi,1,-1,3,-1,5,-1,0)) = -3
方法二
select jcxm from ttt
group by jcxm having (sign(sum(decode(zhi,1,-1,0)))+
sign(sum(decode(zhi,3,-1,0)))+sign(sum(decode(zhi,5,-1,0)))<=-3);

----------
a
b
说明：
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
所以可以用sign和decode来完成比较字段大小来区某个字段
select decode(sign(字段1-字段2),-1,字段3,字段4) from dual;

sign是一个对于写分析SQL有很强大的功能
下面我对sign进行一些总结：
但属性student取0和1以外的值，或者student取两个以上的标法值，问题就不会这么简单了
解决办法就是特征函数（abs(),sign()）

常用的特征算法
［A＝B］＝1－abs（sign（A－B））
［A！＝B］＝abs（sign（A－B））
［A<B］＝1－sign（1＋sign（A－B）） 不能用-sign（A－B）：因为如果不满足A<b则返回-1，而不是0，这样就不能用在字段选择上了
［A<＝B］＝sign(1－sign（A－B））
［A>B］＝1－sign（1－sign（A－B））
［A>＝B］＝sign(1＋sign（A－B））)
［NOTα］＝1－d ［α］
［αANDb ］＝d ［α］*d ［b ］ (6)
［αOR b ］＝sign（d ［α］＋d [b ]）

例如：
A<B                          Decode( Sign(A-B), -1, 1, 0 )         
A<=B                          Decode( Sign(A-B), 1, 0, 1 )         
A>B                          Decode( Sign(A-B), 1, 1, 0 )         
A>=B                          Decode( Sign(A-B), -1, 0, 1 )         
A=B                          Decode( A, B, 1, 0 )         
A between B and C       Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1 ))         
A is null                        Decode(A,null,1,0)         
A is not null                  Decode(A,null,0,1)          A in (B1,B2,...,Bn)   Decode(A,B1,1,B2,1,...,Bn,1,0)         
nor LogA                     Decode( LogA, 0, 1, 0 )               (1-Sign(LogA))
LogA and LogB             LogA * LogB
LogA or LogB               LogA + LogB
LogA xor LogB             Decode(Sign(LogA),Sign(LogB),0,1)    
Mod(Sign(LogA),Sign(LogB),2

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

另外一个关于成绩的分析例子

SELECT
SUM(CASE WHEN cj <60 THEN 1 ELSE 0 END) as "not passed",
SUM(CASE WHEN cj BETWEEN 60 AND 79 THEN 1 ELSE 0 END) as "passed",
SUM(CASE WHEN cj BETWEEN 80 AND 89 THEN 1 ELSE 0 END) as "good",
SUM(CASE WHEN cj >=90 THEN 1 ELSE 0 END) as "Excellent"
FROM cjtable;

decode用法2
表、视图结构转化
现有一个商品销售表sale，表结构为：
month　　　 char(6)　　　　　 --月份
sell　　　　number(10,2)　　　 --月销售金额

现有数据为：
200001　　1000
200002　　1100
200003　　1200
200004　　1300
200005　　1400
200006　　1500
200007　　1600
200101　　1100
200202　　1200
200301　　1300

想要转化为以下结构的数据：
year　　　char(4)　　　　　      --年份
------------    ---------------------          -------------------
month1　　number(10,2)　　　--1月销售金额
month2　　number(10,2)　　　--2月销售金额
month3　　number(10,2)　　　--3月销售金额
month4　　number(10,2)　　　--4月销售金额
month5　　number(10,2)　　　--5月销售金额
month6　　number(10,2)　　　--6月销售金额
month7　　number(10,2)　　　--7月销售金额
month8　　number(10,2)　　　--8月销售金额
month9　　number(10,2)　　　--9月销售金额
month10　 number(10,2)　　    --10月销售金额
month11　 number(10,2)　　    --11月销售金额
month12　 number(10,2)　　    --12月销售金额

结构转化的SQL语句为：
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
sum(decode(substrb(month,5,2),'05',sell,0)),
sum(decode(substrb(month,5,2),'06',sell,0)),
sum(decode(substrb(month,5,2),'07',sell,0)),
sum(decode(substrb(month,5,2),'08',sell,0)),
sum(decode(substrb(month,5,2),'09',sell,0)),
sum(decode(substrb(month,5,2),'10',sell,0)),
sum(decode(substrb(month,5,2),'11',sell,0)),
sum(decode(substrb(month,5,2),'12',sell,0))
from sale
group by substrb(month,1,4);

体会：要用decode /group by/ order by/sign/sum来实现不同报表的生成
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CASE应用

1         1         部门a         800         男
2         2         部门b         900         女
3         3         部门a         400         男
4         4         部门d         1400         女
5         5         部门e         1200         男
6         6         部门f         500         男
7         7         部门a         300         女
8         8         部门d         1000         男
9         9         部门d         1230         女
10         10         部门b         2000         女
11         11         部门c         2000         男
12         12         部门b         1200         男

   SELECT jcxm as 部门,COUNT(seq) as 人数,
     SUM(CASE SEX WHEN 1 THEN 1 ELSE 0 END) as 男,
           SUM(CASE SEX WHEN 2 THEN 1 ELSE 0 END) as 女,
     SUM(CASE SIGN(zhi-800) WHEN -1 THEN 1 ELSE 0 END) as 小于800元,
     SUM((CASE SIGN(zhi-800)*SIGN(zhi-1000)                     /*用*来实现<和>功能*/
          WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
          WHEN 800   THEN 1 ELSE 0 END)) as 从800至999,          /*注意别名不能以数字开头*/
     SUM((CASE SIGN(zhi-1000)*SIGN(zhi-1200)
          WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
          WHEN 1000 THEN 1 ELSE 0 END)) as 从1000元至1199元,
     SUM((CASE SIGN(zhi-1200) WHEN 1 THEN 1 ELSE 0 END)
     +(CASE zhi WHEN 1200 THEN 1 ELSE 0 END)) as 大于1200元
FroM ttt
GROUP BY jcxm

部门名 人数     男        女    小于800元 从800至999 从1000元至1199元    大于1200元
部门a         3         2         1         2         1            0                               0
部门b         3         1         2         0         1            0                               2
部门c         1         1         0         0         0            0                              1
部门d         3         1         2         0         0            1                              2
部门e         1         1         0         0         0              0                              1
部门f         1         1         0         1         0            0                              0

时间： 2025-01-20 11:46:53

sql 行列转换的相关文章

sql-求一个SQL行列转换的解决方案

问题描述 求一个SQL行列转换的解决方案 表存储结构如下定义 姓名 ----------科目 ----------分数 ----------预期分数 张三 ----------语文 ------------74 -------------------90 张三 ----------数学 ------------83 -------------------60 张三 ----------物理 ------------93 -------------------78 李四 ----------语文

一个简单的SQL 行列转换语句_MsSql

一个简单的SQL 行列转换 Author: eaglet 在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表 部门编号 部门名称 合计 正式员工 临时员工 辞退员工 1 A 30 20 10 1 这种问题咋一看摸不着头绪,不过把思路理顺后再看,本质就是一个行列转换的问题.下面我结合这个简单的例子来实现行列转换. 下面3张表 复制代码 代码如下: if exists ( select * from sysobjects where id =

【转】SQL行列转换

原文地址:http://blog.csdn.net/kiki113/article/details/4105929   行列转换等经典SQL语句 1.--行列转换 原表:   姓名     科目   成绩            张三     语文    80            张三     数学    90            张三     物理    85            李四     语文    85            李四     物理    82            李

textbox-sql 行列转换时，空值如何设置成0

问题描述 sql 行列转换时,空值如何设置成0 在用Sql静态方法进行行列转换时,数据库存在的空值如何处理?我想空值变为0. create table tb(姓名 varchar(10) , 学号 varchar(10), 课程 varchar(10) , 分数 int) insert into tb values('张三' ,'123456', '语文' , 74) insert into tb values('张三' ,'123456', '数学' , 83) insert into tb

Oracle实现行列转换的方法分析_oracle

本文实例讲述了Oracle实现行列转换的方法.分享给大家供大家参考,具体如下: 1.固定列数的行列转换 如: student subject grade --------- ---------- -------- student1 语文 80 student1 数学 70 student1 英语 60 student2 语文 90 student2 数学 80 student2 英语 100 -- 转换为: 语文 数学 英语 student1 80 70 60 student2 90 80 10

行列转换-sql 检索结果拼接成字符串。。

问题描述 sql 检索结果拼接成字符串.. 因项目中业务要求所致,写SQL的时候对检索结果的最后两列进行了行列转换.如下pivot (sum(sl) for community_code in ('0201' 社区1 '0202' 社区2'0203' 社区3))因为社区的个数不确定,就导致了输出的结果集中列的个数也不确定.所以用实体类进行接收结果集的时候就遇到了麻烦.对应上述问题,决定将结果集的每一行拼接成一个字符串.那么问题就来了,输出的数据和社区名称对应不起来了..不知道有没有什么好的方法解

[数据库] SQL查询语句表行列转换及一行数据转换成两列

本文主要讲述了SQL查询语句表之间的行列转换,同时也包括如何将一行数据转换成两列数据的方法.子查询的应用.decode函数的用法.希望文章对你有所帮助~ 1.创建数据库表及插入数据 2.子查询统计不同性质的学生总数 3.一行数据转换成两列数据 union all 4.表行列数据转换(表转置) 1.创建数据库表及插入数据 创建数据库.创建学生表并设置主键.插入数据代码如下: --创建数据库 create database StudentMS --使用数据库 use StudentMS --创建学生

SQL Server中行列转换 Pivot UnPivot

原文:SQL Server中行列转换 Pivot UnPivot PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (-) )AS P 完整语法: table_source PIVOT( 聚合函数(value_column) FOR pivot_column IN(<column_list>) )   UNPIVOT用于将列明转为列值(即列转行),在SQL Ser

每日学习心得：SQL查询表的行列转换/小计/统计（with rollup，with cube，pivot解析）

原文 http://www.cnblogs.com/Olive116/p/3271706.html 2013-8-20 1.    SQL查询表的行列转换/小计/统计(with  rollup,with cube,pivot解析) 在实际的项目开发中有很多项目都会有报表模块,今天就通过一个小的SQL查询统计来讲解一下实际开发中比较常用的行列转换/小计/统计等报表统计相关的常用知识点. 题目如下:  查询sales 和stores表,得出1993年每个store每季度销售数量及小计和总计,查询出的