常见SQL细节

最新推荐文章于 2024-10-23 14:38:18 发布

minna_D

最新推荐文章于 2024-10-23 14:38:18 发布

阅读量704

点赞数

CC 4.0 BY-SA版权

分类专栏： PG数据库

本文链接：https://blog.youkuaiyun.com/minna_D/article/details/43027759

PG数据库专栏收录该内容

2 篇文章

订阅专栏

本文详细解析了SQL中count和sum函数的区别与使用场景，包括它们如何处理null值，以及如何利用coalesce函数进行默认值设定。同时展示了如何在查询结果中设置默认值，并讨论了字符串拼接和保留小数的操作。最后，提供了将时间戳转换为可读时间的实例。

博客主页：http://blog.youkuaiyun.com/minna_d

1. 关于count及sum

count结果用不null,sum可能会出现null的情况
hotel=> select count(0), count(1), count(*) , count(id), sum(1), sum(id) from info_op_log where id = -1;
count | count | count | count | sum | sum
-------+-------+-------+-------+-----+-----
0 | 0 | 0 | 0 | |
(1 row)

count结果与count里面的内容并没有关系
hotel=> select count(0), count(1), count(*) , count(id), sum(1), sum(id) from info_op_log where id = 21494408;
count | count | count | count | sum | sum
-------+-------+-------+-------+-----+----------
1 | 1 | 1 | 1 | 1 | 21494408
(1 row)

对于jdbctemplate使用的情况,count(1)与sum(1)也没有抛出异常，这与jdbctemplate的版本使用有关

  public  static  void testCountAndSum(){

        try {
            int sum = jdbcTemplate.queryForInt("select sum(1) from hotel_info where id = -1");
            System.out.println("sum(1)未见异常 ：" + sum);
        }catch (Exception e){
            e.printStackTrace();
        }

        try {
            int count = jdbcTemplate.queryForInt("select count(1) from hotel_info where id = -1");
            System.out.println("count(1)未见异常 ：" + count);
        }catch (Exception e){
            e.printStackTrace();
        }

    }

输出结果:

sum(1)未见异常 ：0
count(1)未见异常 ：0

在JdbcTemplate的底层实现中：

最终会调用DataAccessUtils.requiredSingleResult(results);

而DataAccessUtils中requiredSingleResult实现为,所以不会抛NPE

public static <T> T requiredSingleResult(Collection<T> results) throws IncorrectResultSizeDataAccessException {
		int size = (results != null ? results.size() : 0);
		if (size == 0) {
			throw new EmptyResultDataAccessException(1);
		}
		if (results.size() > 1) {
			throw new IncorrectResultSizeDataAccessException(1, size);
		}
		return results.iterator().next();
	}

2.为查询结果设置默认值
hotel=> select coalesce(count(0),10), coalesce(sum(1),10), coalesce(sum(id),10) from info_op_log where id = -1;
coalesce | coalesce | coalesce
----------+----------+----------
0 | 10 | 10

3.字符串的拼接

实验数据
hotel=> select id, grade, name from hotel_info where grade is null limit 2;
id | grade | name
--------+-------+----------------------
250496 | | Hacienda Hotel Uxmal
245879 | | Hotel La Cresta Inn

hotel=> select id, grade, name from hotel_info where grade is not null limit 2;
id | grade | name
--------+-------+---------------------
904252 | 1 | 速8奥尼尔东北酒店
786205 | 3 | The White House B&B
(2 rows)

字符串拼接时有一个为null,拼接结果会返回blank字符串
hotel=> select id, grade||name as grade_name, name||grade as name_grade from hotel_info where id in (904252, 786205, 250496, 245879);
id | grade_name | name_grade
--------+----------------------+----------------------
904252 | 1速8奥尼尔东北酒店 | 速8奥尼尔东北酒店1
786205 | 3The White House B&B | The White House B&B3
250496 | |
245879 | |

(4 rows)

4.保留小数，及将至1970-1-1 0:0:0 毫秒时间转化为可视时间

hotel=> select *, round(created*1.0/1000,3) , to_timestamp(round(created*1.0/1000,3)) from hotel_mapping limit 10;
id | from_seq | to_seq | created | round | to_timestamp
----+---------------+--------------------------+---------------+----------------+----------------------------
1 | aabenraa_dk_2 | aabenraa_municipality_13 | 1390287539382 | 1390287539.382 | 2014-01-21 14:58:59.382+08
2 | aabenraa_dk_3 | aabenraa_municipality_12 | 1390287539382 | 1390287539.382 | 2014-01-21 14:58:59.382+08
3 | aachen_14 | aachen_ac_20 | 1390287539382 | 1390287539.382 | 2014-01-21 14:58:59.382+08
4 | aachen_16 | aachen_ac_47 | 1392797903788 | 1392797903.788 | 2014-02-19 16:18:23.788+08
5 | aachen_18 | aachen_ac_37 | 1381464234180 | 1381464234.180 | 2013-10-11 12:03:54.18+08
6 | aachen_2524 | aachen_ac_37 | 1390287539382 | 1390287539.382 | 2014-01-21 14:58:59.382+08
7 | aachen_2533 | aachen_ac_6 | 1379065296340 | 1379065296.340 | 2013-09-13 17:41:36.34+08
8 | aachen_2538 | aachen_ac_7 | 1379065295328 | 1379065295.328 | 2013-09-13 17:41:35.328+08
9 | aachen_2542 | aachen_ac_38 | 1390287539382 | 1390287539.382 | 2014-01-21 14:58:59.382+08
19 | aachen_2561 | aachen_28 | 1381394951136 | 1381394951.136 | 2013-10-10 16:49:11.136+08
(10 rows)