首页 > 代码库 > 02. SQL表达式的灵活使用

02. SQL表达式的灵活使用

原文:02. SQL表达式的灵活使用

什么是SQL表达式?在SQL语句中,表达式可以是函数,也可以是列和列之间的混合运算。
很多时候,对于表达式的使用,可以比单独操作表上的列,带来更多方便。

一. 在HAVING中使用表达式

--drop table tcreate table t(c1 int,c2 int)insert into t select 1,100 union allselect 1,200 union allselect 2,100 union allselect 2,200 union allselect 2,300 union allselect 3,50 union allselect 3,200 union allselect 4,50 union allselect 4,200 union allselect 4,300

返回c1,满足:有3个且都大于等于100 的c2 (学校的考试题中很多见)。

select c1 from t group by c1 having min(c2)>=100 and count(1)=3

 同样,表达式也可以用于group by 子句。


二. 在ORDER BY中使用表达式

--drop table t_orderbycreate table t_orderby( c1 int null, c2 varchar(10) null,c3 varchar(10) null)insert into t_orderby select 1,2,a1 union all select 1,1,a2 union all select 3,1,ab union all select 1,4,b1

 

1. c2列的数据按‘4‘,‘1‘,‘2‘的指定顺序排序

(1) 使用union

select * from t_orderby where c2=4 union all select * from t_orderby where c2=1 union all select * from t_orderby where c2=2

 (2) 使用表达式方法1

select * from t_orderby order by charindex(c2,4,1,2) 

 (3) 使用表达式方法2,再加个按照c1倒序

select * from t_orderby order by case          when c2=4 then 1          when c2=1 then 2          when c2=2 then 3          end,c1 desc

 

2. 随机排序

(1) 要求c2=‘4‘排第一行,其他的行随机排序

select * from t_orderby order by case          when c2=4 then 1          else 1+rand()          end

 (2) 所有行随机排序

select * from t_orderby order by newid()

 (3) 随机取出第一行

select top 1 * from t_orderby order by newid()

 

3. 要求列c3中数据,先按第一个字符排序,再按第二个字符排序

select * from t_orderby order by left(c3,1),ASCII(substring(c3,2,1))

 

三. 在COUNT中使用表达式

--drop table t_countcreate table t_count(c1 varchar(10) null,c2 varchar(10) null)insert into t_count values(null,null)insert into t_count values(a,b)insert into t_count values(a,b)insert into t_count values(c,d)

 

1. 使用常量表达式避免忽略NULL值

select COUNT(c1) from t_count --3select COUNT(distinct c1) from t_count --2

 聚合函数中, SUM/AVG/COUNT中的NULL会被忽略,比如:这里的count(c1)忽略了null

select COUNT(*) from t_count --4select COUNT(1) from t_count --4select COUNT(1000) from t_count --4

用count(*)不会忽略NULL,同样用count(1)也不会忽略NULL,这里的1就是一个常量表达式,换成其他常量表达式也可以,比如count(1000)。

另外,count(1)和order by 1,2那里的数字意思不一样,order by后面的序号表示列号。


2. 小心表达式值为NULL被忽略

--正常select count(*) from (select c1,c2 from t_count group by c1,c2) t --3select count(*) from (select distinct c1,c2 from t_count) t --3--有NULL参与了运算,所以表达式值为NULLselect count(distinct c1+c2) from t_count --2

 

四. 在JOIN中使用表达式

--drop table t1,t2 create table t1(url        varchar(1000))create table t2(code        varchar(1000))--insertinsert into t1select http://www.baidu.com/test1 union allselect http://www.baidu.com/test2 union allselect http://www.baidu.com/test3 union allselect www.baidu.com/test1 union allselect www.baidu.com/test2 union allselect http://www.google.com/test1 union allselect http://www.google.com/test2 union allselect http://www.sogou.com/test3 union allselect http://www.sogou.com/test4insert into t2select baidu.com union allselect sogou.com

要求t1,t2表的两个列之间做匹配,t2的列值包含在t1的列值里。

事实上,在join或者where条件中,只要能构造出比较运算表达式(返回boolean值),就可以用作判断条件。 

select t2.code,t1.url from t1 inner join t2on CHARINDEX(t2.code,t1.url) > 0--结果如下/*baidu.com    http://www.baidu.com/test1baidu.com    http://www.baidu.com/test2baidu.com    http://www.baidu.com/test3baidu.com    www.baidu.com/test1baidu.com    www.baidu.com/test2sogou.com    http://www.sogou.com/test3sogou.com    http://www.sogou.com/test4*/