数据库SQL复杂介绍

1.1.1 数据库复杂SQL介绍

该文档中，未涉及函数的部分，DB2数据库可以通用。

1.1.1.1 行转列，并汇总求和

Oracle数据库中，行转列可以使用到decode函数进行处理。

例如表t1有以下数据

合同号(hth)	科目(kmbh)	金额(je)	发生日期
00001	01	20	2015-01-02
00001	02	30	2015-01-02
00002	01	25	2015-01-02
00001	01	15	2015-01-03
00002	02	40	2015-01-03
00001	03	50	2015-01-04

 

现需要将以上数据汇总，合同下相同科目的金额汇总到一起，并将科目按列展示

合同号	科目01	科目02	科目03
00001	35	20	50
00002	25	40	0

可使用以下SQL实现：

select hth,sum(decode(kmbh,’01’,je,0))科目01, sum(decode(kmbh,’02’,je,0))科目02, sum(decode(kmbh,’03’,je,0))科目03 from t1 group by hth

1.1.1.2  将查询结果插入表中

使用以下语句将一个查询结果插入到指定的表中

 

1.1.1.3 将查询结果合并到表中

在进行SQL语句编写时，经常会遇到大量的同时进行Insert/Update的语句 ，简单说即当存在记录时，就更新(Update)，不存在数据时，就插入(Insert)，可以使用merge命令通过一条语句完成从一个或者多个表中查询数据对目标表的更新和插入数据。

create table PRODUCTS
(
	PRODUCT_ID INTEGER,
	PRODUCT_NAME VARCHAR2(60),
	CATEGORY VARCHAR2(60)
);
  
insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');

create table NEWPRODUCTS
(
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');

1、使用表newproducts中的product_name和category字段来更新表products中相同product_id的product_name和category.

MERGE INTO products p 
	USING newproducts np 
	ON (p.product_id = np.product_id) 
	WHEN MATCHED THEN
	UPDATE
	SET p.product_name = np.product_name, 
	p.category = np.category;

2、当条件不满足的时候把newproducts表中的数据INSERT到表products中

MERGE INTO products p   
    USING newproducts np   
     ON (p.product_id = np.product_id)   
     WHEN NOT MATCHED THEN  
     INSERT  
     VALUES (np.product_id, np.product_name,   
     np.category);

     

3、insert 和update　都带有where字句

MERGE INTO products p   
 USING newproducts np   
    ON (p.product_id = np.product_id)   
    WHEN MATCHED THEN  
    UPDATE  
     SET p.product_name = np.product_name,   
     p.category = np.category   
    WHERE p.category = 'DVD'  
    WHEN NOT MATCHED THEN  
     INSERT  
     VALUES (np.product_id, np.product_name, np.category)   
    WHERE np.category != 'BOOKS'

1.1.1.4 子查询介绍

子查询分为嵌套子查询、关联子查询两种，下面举例介绍：

1.1.1.4.1 非关联子查询

有项目信息表laproject及客户信息表party_customer,项目信息表的tenantid对应客户信息表中的客户号initid，现需要查询出companyScale为01的客户下的所有项目。可以使用关联查询：

 

也可以使用非关联子查询：

 

嵌套子查询是指可以单独执行的子查询，嵌套子查询是先执行子查询再执行主查询，这个容易理解；

1.1.1.4.2 关联子查询

party_customer中，同一个客户有多个版本，即会有多条记录， party_customer表中version属性最大的为最新版本客户,现需要查询出所有客户最新的版本，则可以使用关联子查询：

 

关联子查询需要子查询中的某个字段与主查询的字段做关联，是与主查询关联在一起的，无法单独执行。

关联子查询是先执行主查询再逐条执行子查询，上面例子的执行过程为：先执行主查询查询到每一个客户版本，然后逐条执行子查询，即获取这个客户对应的所以版本中最大的version，然后比较当前这一条的version是否与子查询返回的version相同，不相同则过滤掉。

1.1.1.4.3 子查询作为数据来源

继续上面的例子，同一个客户的companyScale属性在不同的版本可能值会不一样，因此使用下面关联语句则查询结果会不准确。

 

现以最新版本的companyScale为准，将party_customer b替换为子查询：

 

可以看到整合后的查询既有非关联查询，又用关联查询。

1.1.1.4.4 使用子查询进行数据更新

下面是从系统中抽取的一条SQL语句，是典型的使用子查询进行更新的例子，其中还用到了case when then end语法。

 

该语句的含义为使用表laproject的字段leasingType更新表rppaybaseinfo中projectid相同的记录的leasingType字段，其中还对laproject的leasingType进行了加工，如果为01则更新01，02则更新02，其他的更新为03。

另外，这个语句不是很严谨，因为最外层的where条件只是针对contendstate字段进行了过滤，因此如果符合条件的记录（例如contentstate=’0’）,其projectid在laproject表中并不存在，即子查询返回的结果为空，rp表的leasingstyle字段会被更新为null。严谨的写法是：

 

增加exists子查询后，只有匹配的才会更新。当然在实际使用中，可能laproject中肯定会有所有的projectid，此时则可以不需要exists子查询。

1.1.1.4.5 左连接

Left out

 

1.1.1.4.6 子查询作为查询条件

上面介绍关联查询的例子即是将子查询作为了查询条件。参见关联子查询

1.1.1.5 常用函数

1.1.1.5.1 数值函数

Ø abs:求绝对值函数

如:abs(−5) 结果为5

Ø round按指定精度对十进制数四舍五入

round(45.923, 1),结果为45.9

round(45.923, 0),结果为46

round(45.923, −1),结果为50

Ø trunc:按指定精度截断十进制数

trunc(45.923, 1),结果为45.9

trunc(45.923),结果为45

trunc(45.923, −1),结果为 40

1.1.1.5.2 字符函数

Ø lower:将字符串转换成小写

如:lower ('SQL Course')结果为sql course

Ø upper:将字符串转换成大写

如:upper('SQL Course')结果为SQL COURSE

Ø concat:连接两个字符串

concat('SQL', ' Course') 结果为SQL Course也可以使用||,例如’SQL’ || ‘Course’

Ø substr:给出起始位置和长度，返回子字符串

如:substr('String',1,3)结果为Str，第一个字符的起始位置为1

Ø length:求字符串的长度

length('Wellcom') 结果为7

Ø lpad:用字符填充字符串左侧到指定长度

如:lpad('Hi',10,'-')结果为--------Hi

Ø rpad:用字符填充字符串右侧到指定长度

如:rpad('Hi',10,'-')结果为Hi--------

Ø trim:在一个字符串中去除另一个字符串

如:trim('S' FROM 'SSMITH')结果为MITH

Ø replace:用一个字符串替换另一个字符串中的子字符串

如:replace('ABC', 'B', 'D')结果为ADC

1.1.1.5.3 日期函数

日期函数主要为日期的转换，to_char和to_date 。

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') 日期到字符

to_date('2015-02-28', 'yyyy-mm-dd')

1.1.1.5.4 数值间字符转换

Ø to_char:转换成字符串类型

to_char(1234.5, '$9999.9') 数值到字符

Ø to_number:转换成数值类型

如:to_number('1234.5'),结果:1234.5

1.1.1.5.5 逻辑判断函数

Ø coalesce

coalesce用法为coalesce(expr1,expr2……exprn)，其作用是在expr1,expr2……exprn这列表达式中查找第一个不为null的值且返回该值。如果都为null，则返回null。

Ø nvl

写法为nvl(expr1,expr2)，如果expr1为null则返回expr2，否则返回expr1

Ø nvl2

nvl2用法为nvl2(expr1,expr2,expr3)，其作用是判断expr1是否为null，若不为null，返回expr2，为空返回expr3。

Ø nullif

nullif用法为nullif(expr1,expr2)，其作用是判断expr1与expr2是否相等，若相等则返回null，否则返回expr1。

 

 

收集sql问题