笔记337 SQL表达式的灵活使用 2013-8-3

笔记337 SQL表达式的灵活使用   2013-8-3

  1 --SQL表达式的灵活使用   2013-8-3
  2 
  3 --什么是SQL表达式?在SQL语句中,表达式可以是函数,也可以是列和列之间的混合运算。
  4 --很多时候,对于表达式的使用,可以比单独操作表上的列,带来更多方便。
  5 --
  6 --一. 在HAVING中使用表达式
  7 USE tempdb
  8 GO
  9 --drop table t
 10 create table t(c1 int,c2 int)
 11 
 12 insert into t
 13 select 1,100 union all
 14 select 1,200 union all
 15 select 2,100 union all
 16 select 2,200 union all
 17 select 2,300 union all
 18 select 3,50 union all
 19 select 3,200 union all
 20 select 4,50 union all
 21 select 4,200 union all
 22 select 4,300
 23 
 24 SELECT * FROM t
 25 
 26 --返回c1,满足:有3个且都大于等于100 的c2 (学校的考试题中很多见)。
 27 
 28 select c1 from t
 29 group by c1
 30 having min(c2)>=100 and count(1)=3
 31 -- 同样,表达式也可以用于group by 子句。
 32 
 33 
 34 --二. 在ORDER BY中使用表达式
 35 USE tempdb
 36 GO
 37 --drop table t_orderby
 38 create table t_orderby
 39 (
 40 c1 int null,
 41 c2 varchar(10) null,
 42 c3 varchar(10) null
 43 )
 44 
 45 insert into t_orderby
 46 select 1,'2','a1' union all
 47 select 1,'1','a2' union all
 48 select 3,'1','ab' union all
 49 select 1,'4','b1'
 50 
 51 
 52 
 53 --1. c2列的数据按'4','1','2'的指定顺序排序
 54 
 55 --(1) 使用union
 56 
 57 
 58 select * from t_orderby
 59 where c2='4'
 60 union all
 61 select * from t_orderby
 62 where c2='1'
 63 union all
 64 select * from t_orderby
 65 where c2='2'
 66 
 67 --(2) 使用表达式方法1
 68 
 69 select * from t_orderby
 70 order by charindex(c2,'4,1,2')
 71 --(3) 使用表达式方法2,再加个按照c1倒序
 72 
 73 select * from t_orderby
 74 order by case
 75 when c2='4' then 1
 76 when c2='1' then 2
 77 when c2='2' then 3
 78 end,c1 desc
 79 
 80 
 81 --2. 随机排序
 82 
 83 --(1) 要求c2='4'排第一行,其他的行随机排序
 84 
 85 select * from t_orderby
 86 order by case
 87 when c2='4' then 1
 88 else 1+rand()
 89 end
 90 --(2) 所有行随机排序
 91 
 92 select * from t_orderby
 93 order by newid()
 94 --(3) 随机取出第一行
 95 
 96 select top 1 * from t_orderby
 97 order by newid()
 98 
 99 
100 --3. 要求列c3中数据,先按第一个字符排序,再按第二个字符排序
101 
102 select * from t_orderby
103 order by left(c3,1),ASCII(substring(c3,2,1))
104 
105 --三. 在COUNT中使用表达式
106 USE tempdb
107 GO
108 
109 --drop table t_count
110 create table t_count
111 (
112 c1 varchar(10) null,
113 c2 varchar(10) null
114 )
115 
116 insert into t_count values(null,null)
117 insert into t_count values('a','b')
118 insert into t_count values('a','b')
119 insert into t_count values('c','d')
120 
121 SELECT * FROM t_count
122 
123 
124 --1. 使用常量表达式避免忽略NULL值
125 
126 select COUNT(c1) from t_count --3
127 select COUNT(distinct c1) from t_count --2
128 -- 聚合函数中, SUM/AVG/COUNT中的NULL会被忽略,比如:这里的count(c1)忽略了null
129 
130 select COUNT(*) from t_count --4
131 select COUNT(1) from t_count --4
132 select COUNT(1000) from t_count --4
133 --用count(*)不会忽略NULL,同样用count(1)也不会忽略NULL,这里的1就是一个常量表达式,换成其他常量表达式也可以,比如count(1000)。
134 
135 --另外,count(1)和order by 1,2那里的数字意思不一样,order by后面的序号表示列号。
136 
137 
138 ----2. 小心表达式值为NULL被忽略
139 
140 --正常
141 select count(*) from (select c1,c2 from t_count group by c1,c2) t --3
142 select count(*) from (select distinct c1,c2 from t_count) t --3
143 --有NULL参与了运算,所以表达式值为NULL
144 select count(distinct c1+c2) from t_count --2
145 
146 --四. 在JOIN中使用表达式
147 USE tempdb
148 GO
149 --drop table t1,t2
150 create table t1
151 (
152 url        varchar(1000)
153 )
154 
155 create table t2
156 (
157 code        varchar(1000)
158 )
159 
160 --insert
161 insert into t1
162 select 'http://www.baidu.com/test1' union all
163 select 'http://www.baidu.com/test2' union all
164 select 'http://www.baidu.com/test3' union all
165 select 'www.baidu.com/test1' union all
166 select 'www.baidu.com/test2' union all
167 select 'http://www.google.com/test1' union all
168 select 'http://www.google.com/test2' union all
169 select 'http://www.sogou.com/test3' union all
170 select 'http://www.sogou.com/test4'
171 
172 insert into t2
173 select 'baidu.com' union all
174 select 'sogou.com'
175 
176 SELECT * FROM t1
177 SELECT * FROM t2
178 
179 
180 --要求t1,t2表的两个列之间做匹配,t2的列值包含在t1的列值里。
181 --
182 --事实上,在join或者where条件中,
183 --只要能构造出比较运算表达式(返回boolean值),就可以用作判断条件。
184 select t2.code,t1.url from t1
185 inner join t2
186 on CHARINDEX(t2.code,t1.url) > 0
187 
188 --结果如下
189 /*
190 baidu.com    http://www.baidu.com/test1
191 baidu.com    http://www.baidu.com/test2
192 baidu.com    http://www.baidu.com/test3
193 baidu.com    www.baidu.com/test1
194 baidu.com    www.baidu.com/test2
195 sogou.com    http://www.sogou.com/test3
196 sogou.com    http://www.sogou.com/test4
197 */

 

posted @ 2013-08-04 22:08 桦仔 阅读(...) 评论(...) 编辑 收藏