sql-大杂烩

select column_1 as a from table_1 as b;
as别名
SELECT 'abc' as type from table
type列的内容为abc

Select A.Amount * AE.ExchangeRate AS Amount
From LoanContract A
Inner Join ExchangeRate AE ON A.Currency = AE.Currency

// 注意：以下sql执行会报错，找不到这个a
select column_1 as a from table_1 where a='jk';
应该改为子查询：
select * from(select column_1 as a from table_1) where a='jk';

LISTAGG 多行数据的同一列进行拼接
SELECT LISTAGG(ContractId, ',') within group(order by ContractId)

Select case 
when OperateStatus='01'
then 'OPEN'
when OperateStatus='02'
then 'CLOSE'
else 'NA' // else不一定需要
end as OperateStatus 
From NPL.RiskCustomerInfo

Cast(Asset as Char(2))
// 将空值替换成其他值
Coalesce(Sum(BIZBALANCE * ExchangeRate), 0)

case when (Sum(BIZBALANCE * ExchangeRate) is not null) 
then Sum(BIZBALANCE * ExchangeRate)
ELSE 0 END

聚合函数与分组学习（group by）

// 注意：所有select的字段，除聚合函数中的字段外，都必须在group by中出现
group by 
SELECT H.CltNO, Sum(A.BIZBALANCE * D.ExchangeRate) as balanceTotal,C.MortgagedId
FROM CVM.MortSnapShot H
GROUP BY H.CltNO,C.MortgagedId with ur

select distinct vend_id from products

// 对于完全重复的记录，直接使用distinct 即可（distinct作用在id，name，age3个字段）
select 
distinct id, name, age
from  
table1

限制结果
mysql: 
limit5返回不多于5行
limit1,2 返回从行1开始的2行
db2: 
fetch first 1 rows only返回第一条记录
Select * from (select 列名1 , 列名2  , row_number() over() as rn  from 表名) as 表别名
where rn>=10 and rn<=20;返回第10条到20条记录

排序
SELECT prod_id, prod_price,prod_name
FROM products
ORDER BY prod_price, prod_name
先对prod_price排序，再对prod_name排序

SELECT prod_id, prod_price,prod_name
FROM products
where prod_price between 1 and 5

空值检查
SELECT prod_id, prod_price,prod_name
FROM products
where prod_price is not null

下划线_通配符，只匹配单个字符

avg()只用于单列

count(*)计数结果包括NUll
count(1)计数结果包括NUll
count(column)忽略Null

select每个列必须在group by子句中给出（聚集计算语句除外）

select order_num,SUM(quantity*item_price) as ordertotal
from orderitems
where item_price > 10
group by order_num
having SUM(quantity*item_price) > 50
order by ordertotal
group by必须在where后面，order by之前（先分组再排序）

where,,,having...
where过滤行having过滤分组

一般子查询都可以用自联结代替，查询速度更快

对组合查询Union结果排序，group by必须出现在最外层的select语句之后。从而对整个结果集进行排序。

DB2删除新增字段
--ALTER TABLE PACKETASSESSRECORD DROP COLUMN POCKETAVEDEBTRATE
--ALTER TABLE PACKETASSESSRECORD ADD COLUMN PACKETAVEDEBTRATE DECIMAL(24,2)
--
--CALL SYSPROC.ADMIN_CMD('reorg table PACKETASSESSRECORD')
--CALL SYSPROC.ADMIN_CMD('runstats on table PACKETASSESSRECORD')

Merge Into Mine A
Using (Select MineSerialId From MineProd Where MineSerialId = @MineSerialId With Ur) B
On A.SerialId = B.MineSerialId
When Matched Then
Update Set A.DiscTotalSaleRevenue = B.DiscTotalSaleRevenue, Step = @Step
WHEN NOT MATCHED THEN
INSERT (ReportDateNum) VALUES (@ReportDateNum);

Merge Into Mine as A
Using (
  values(cast(@MineSerialId1  as char(15)))，
            (cast(@MineSerialId2  as char(15)))，
) as B(MineSerialId)
On A.SerialId = B.MineSerialId
When Matched And A.ReqId=@ReqId Then
Update Set A.DiscTotalSaleRevenue = B.DiscTotalSaleRevenue, Step = @Step
WHEN NOT MATCHED THEN
INSERT (ReportDateNum) VALUES (@ReportDateNum);

注意：
插入时，on的条件要保住唯一。
更新时，on的条件不需要保证唯一，可以更新多条。但如果匹配出来是多对多的关系。会报SQLERROR CODE = -788的错误。由于on 条件中 连接条件是 多对多的关系，导致在更新时db2无法判断是更新那一条，所以会出现此异常。

Select Sum(Amount) From ABC;

如果无记录。返回一行Null记录。

select row_number() over(partition by A order by B Desc Nulls Last) rn from Table_A

A ：为分组字段。
B：为分组后的排序字段。
Nulls Last：把空值放到最后面在对非空值排序。
此条sql语句，多用于对数据进行分组排序，并对每个组中的数据分别进行编号，编号从1开始递增，每个组内的编号不会重复；

DB2: 字段IsIntroduce可以存'Y'，'N'。还可以为空。IsIntroduce!='Y'，不会查出空的结果，需要配合Or IsIntroduce is null

拼接sql时注意1=1 and ......
1=1 or ......
1=2 and ......
1=2 or ......
的使用场景

union all只是合并查询结果。
union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
在没有去重的前提下，使用union all的执行效率要比union高。

B 树与 B+ 树的最大区别就是：
B 树可以在非叶结点中存储数据，但是 B+ 树的所有数据其实都存储在叶子节点中

exists : 强调的是是否返回结果集，不要求知道返回什么。
in：引导的子句只能返回一个字段
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)
先求出A的结果集，再用Exist中的条件匹配

insert into adjustsalary
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="eid != null" >
        eid,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="eid != null" >
        #{eid,jdbcType=INTEGER},
      </if>
    </trim>

EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

左连接就是在内连接的基础上加上左边表未出现的数据行

WITH AS短语，也叫做子查询部分。
With temp (CustomerId, BranchId) AS (Select CustomerId, BranchId From AA)
Select * From temp;

行号作为id

SELECT ROW_NUMBER() OVER ( ORDER BY a ) AS id ,a ,b FROM A

SQL语句中的AND/OR和括号的执行顺序问题

value(a,b)函数的用法
在第一个参数为null时,返回第二个参数的值

执行计划：Nest Loop是最简单的一种连接方式，数据库会根据表中的记录数选择内表及外表，在定义内外表后，首先会对外表进行全表扫描，然后重复扫描内表并与外表中的每一条记录进行匹配，最终返回程序所需的结果集。
NLJOIN的总成本大约为外表扫描的成本+外表返回的行数×内表扫描的成本。

select stu_type,sum(score) from students group by id,stu_type order by id;
order by 中列，应该出现在group by 子句中。这是一个很显然的道理。

mapper.xml中select #{} as Id from A会报错，需要改成'${}'