常见SQL细节

博客主页：http://blog.csdn.net/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)