sql语句

最新推荐文章于 2023-12-11 16:46:29 发布

ldcaws

最新推荐文章于 2023-12-11 16:46:29 发布

阅读量338

点赞数

CC 4.0 BY-SA版权

分类专栏： Oacle

本文链接：https://blog.youkuaiyun.com/leijie0322/article/details/72833115

Oacle 专栏收录该内容

9 篇文章

订阅专栏

本文深入讲解SQL查询中的级联合成新表、空值处理、取整及字符串转换等技巧，并对比on与where条件的不同效果，适合希望提升SQL技能的读者。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、级联合成新表：on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后

一般来说，预判一下过滤条件的范围。由于数据库是从后向前解析SQL语句的，通常建议把能过滤最多结果的条件放在后面

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2

id	size
1	10
2	20
3	30

表2：tab2

size	name
10	AAA
20	BBB
20	CCC

两条SQL:

1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’

2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL的过程：

1、中间表 on条件: tab1.size = tab2.size

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	20	BBB
2	20	20	CCC
3	30	(null)	(null)

2、再对中间表过滤 where 条件： tab2.name=’AAA’

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA

第二条SQL的过程：

1、中间表 on条件: tab1.size = tab2.size and tab2.name=’AAA’ (条件不为真也会返回左表中的记录)

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	(null)	(null)
3	30	(null)	(null)

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

二、空值转换函数：nvl(表达式1，表达式2) 若表达式1为空值，nvl返回值为表达式2的值，否则返回表达式1的值。其表达式的值
可以是数字型、字符型和日期型，但表达式1和2的数据类型必须为同一类型。
例如：nvl(table2.notcount,0) 若第一个字段为空则取值为0。

三、取整函数：ROUND ( numeric_expression , length [ , function ] ) 表达式1为被操作值，表达式2为四舍五入的精度
即指定的小数位数，表达式3为非0值就执行截断操作

四、判断函数：decode(inParam,'beConparedParam','值1','值2') 若第一个参数==第二个参数，则返回值1，否则返回值2。

五、字符串转换函数：to_char() 将日期或数字转换为字符串
日期转换函数：to_date() 将字符串转换为日期
数字转换函数：to_number() 将字符转换为数字
或者 to_char(字段,'yyyy-mm-dd') 或者 to_date(字段,'yyyy-mm-dd')
如to_date(guacontract.guastartdate, 'yyyy-mm-dd hh24:mi:ss')

六、字符串拼接函数：||

七、between查询日期：短日期类型默认Time为00:00:00，所以当使用between作限制条件时，就相当于
between '2009-1-22 00:00:00' and '2009-1-22 00:00:00',因此就查不出数据。要想实现功能，那就使用连接字串的形式，
在短日期后面把时间补全，那样就能实现功能；且包含边界值
select to_char(sysdate,'yyyymmdd')　from　dual;
select to_date('2003-12-12','yyyy-mm-dd')　from　dual;

八、时间类型可以比较大小，但是日期格式需要转成字符串，或者字符串转成日期来比较，挨个字符比较它的ACSII码

select to_char(to_date((CASE  WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN  to_number(substr('2017-05-11', 1, 4))
select to_char(to_date((CASE  WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN  to_number(substr('2017-05-11', 1, 4))
                                   WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN   to_number(substr('2017-05-11', 1, 4)) + 1   END)
                             || 
                            (case  when ((CASE   WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN   to_number(substr('2017-05-11', 6, 2)) + 1
                                                 WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN   to_number('01')   END)) < 10 
                                   then
                                        ('0' || to_char(CASE  WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN   to_number(substr('2017-05-11', 6, 2)) + 1
                                                              WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN  to_number('01')  END))
                                   when (CASE  WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN   to_number(substr('2017-05-11', 6, 2)) + 1
                                               WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN   to_number('01')   END) > 9 
                                   then
                                        to_char(CASE  WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN  to_number(substr('2017-05-11', 6, 2)) + 1
                                                      WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN   to_number('01')  END)
                              end)
                              || 
                              '06 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
    from dual
    
    where fa.creationtime not between to_date(
       substr(fa.creationtime, 1, 4) || substr(fa.begin_date, 6, 2) || '01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
    and to_date(
       substr(fa.creationtime, 1, 4) || substr(fa.begin_date, 6, 2) + 1 || '05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
       
       select 2017 || 06 || '05 00:00:00' from dual where 2017 ||  '0' || 11 || '05 00:00:00' < '20170611'


                                   WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN   to_number(substr('2017-05-11', 1, 4)) + 1   END)
                             || 
                            (case  when ((CASE   WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN   to_number(substr('2017-05-11', 6, 2)) + 1
                                                 WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN   to_number('01')   END)) < 10 
                                   then
                                        ('0' || to_char(CASE  WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN   to_number(substr('2017-05-11', 6, 2)) + 1
                                                              WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN  to_number('01')  END))
                                   when (CASE  WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN   to_number(substr('2017-05-11', 6, 2)) + 1
                                               WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN   to_number('01')   END) > 9 
                                   then
                                        to_char(CASE  WHEN to_number(substr('2017-05-11', 6, 2)) < 12 THEN  to_number(substr('2017-05-11', 6, 2)) + 1
                                                      WHEN to_number(substr('2017-05-11', 6, 2)) = 12 THEN   to_number('01')  END)
                              end)
                              || 
                              '06 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
    from dual
    
    where fa.creationtime not between to_date(
       substr(fa.creationtime, 1, 4) || substr(fa.begin_date, 6, 2) || '01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
    and to_date(
       substr(fa.creationtime, 1, 4) || substr(fa.begin_date, 6, 2) + 1 || '05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
       
       select 2017 || 06 || '05 00:00:00' from dual where 2017 ||  '0' || 11 || '05 00:00:00' < '20170611'

九、分组统计：count() 和 group by 一起使用