事例一：排序
ORDER BY子句可包括未出现在此选择列表的项目.然而,如果指定SELECT DISTINCT,则排序列必定出现在选择列表中.
错误的语句:SELECT DISTINCT 书名 FROM tb_BookStore ORDER BY 现存数量
正确的语句:SELECT DISTINCT (书名),现存数量 FROM tb_BookStore ORDER BY 现存数量
(2)
gold是等级 expire_date是到期时间
根据前面的查询是按等级后再按到期时间,这样的话高等级的过期的人就在低等级没过期的人的前面.
现在要做成 过期的都要到后面显示 无论等级是多少 而没过期的还是正常按等级及到期时间排列
Create Table #TEST
(ID Int,
gold Int,
expire_date DateTime)
Insert #TEST Select 1,2,'2006-06-30'
Union All Select 2,3,'2006-06-30'
Union All Select 3,5,'2006-05-30'
Union All Select 4,6,'2006-06-12'
Union All Select 5,10,'2006-07-30'
Union All Select 6,1,'2006-08-30'
Union All Select 6,1,'2008-08-30'
Union All Select 6,1,'2009-08-30'
select top 100 * from [#TEST] order by (Case When expire_date>=GetDate() Then 0 Else 1 End), gold desc,expire_date desc
事例2：charindex
B=(1,11,2,22,3)
select * from table where
charindex(',1,',','+B+',')>0 or charindex(',2,',','+B+',')>0
or charindex(',3,',','+B+',')>0
（2）
INSERT @t SELECT '消费电子'
UNION ALL SELECT '消费电子 '
UNION ALL SELECT ',消费电子,'
select * from @t where nkey='消费电子'
/*-- 结果
id nkey
----------- ----------
1 消费电子
2 消费电子
--*/
select *, ','+'消费电子'+',' from @t where charindex(','+'消费电子'+',',','+nkey+',')>0
/*-- 结果
id nkey
----------- ---------- ----------
1 消费电子 ,消费电子,
3 ,消费电子, ,消费电子,
(2 行受影响)
--*/
select * from @t where ','+'消费电子'+','=','+nkey+','
/*-- 结果
id nkey
----------- ----------
1 消费电子
(1 行受影响)
--*/
charindex(','+'消费电子'+',',','+nkey+',')>0
这个是包含关系
','+'消费电子'+','=','+nkey+','
这个是完全匹配关系.
事例3:replace
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
示例
下例用 xxx 替换 abcdefghi 中的字符串 cde。
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
下面是结果集：
------------
abxxxfghixxx
(1 row(s) affected)
事例4：case when
CASE 具有两种格式：
(1)简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。
(2)CASE 搜索函数计算一组布尔表达式以确定结果。两种格式都支持可选的 ELSE 参数。
语法
简单 CASE 函数：
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
CASE 搜索函数：
select case StatusValue
when '0' then (select top 1 字段名 from work)
when '1' then (select top 1 字段名 from Notice)
end
from CommonStatusDict
create table tb(id int ,class varchar)--class种类就只有三种，如果不固定就需要存储过程来实现
insert tb
select 1,'a' union all
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'b' union all
select 2,'b'
select * from tb
想查找出按id分组得到的 a ,b ,c 的数量
如下
id a b c
1 2 1 1
2 1 2 0
select
id,
a=sum(case class when 'a' then 1 else 0 end),
b=sum(case class when 'b' then 1 else 0 end),
c=sum(case class when 'c' then 1 else 0 end)
from
tb
group by
id
事例5：datediff
要查询9 月份的数据中的任意时间段,可能是一个月的,也可能是1日到15日的
select * from tabname where datediff(month,colname,'2006-09-01')=0
事例6：convert
SQL中CONVERT转化函数的用法
SQL中CONVERT转化函数的用法
CONVERT的使用方法:
格式:
CONVERT(data_type,expression[,style])
说明:
此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)
相互转换的时候才用到.
例子:
SELECT CONVERT(varchar(30),getdate(),101) now
结果为
now
---------------------------------------
09/15/2001
/
style数字在转换时间时的含义如下
-------------------------------------------------------------------------------------------------
Style(2位表示年份) | Style(4位表示年份) | 输入输出格式
-------------------------------------------------------------------------------------------------
- | 0 or 100 | mon dd yyyy hh:miAM(或PM)
-------------------------------------------------------------------------------------------------
1 | 101 | mm/dd/yy
-------------------------------------------------------------------------------------------------
2 | 102 | yy-mm-dd
-------------------------------------------------------------------------------------------------
3 | 103 | dd/mm/yy
-------------------------------------------------------------------------------------------------
4 | 104 | dd-mm-yy
-------------------------------------------------------------------------------------------------
5 | 105 | dd-mm-yy
-------------------------------------------------------------------------------------------------
6 | 106 | dd mon yy
-------------------------------------------------------------------------------------------------
7 | 107 | mon dd,yy
-------------------------------------------------------------------------------------------------
8 | 108 | hh:mm:ss
-------------------------------------------------------------------------------------------------
- | 9 or 109 | mon dd yyyy hh:mi:ss:mmmmAM(或PM)
-------------------------------------------------------------------------------------------------
10 | 110 | mm-dd-yy
-------------------------------------------------------------------------------------------------
11 | 111 | yy/mm/dd
-------------------------------------------------------------------------------------------------
12 | 112 | yymmdd
-------------------------------------------------------------------------------------------------
- | 13 or 113 | dd mon yyyy hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
14 | 114 | hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
- | 20 or 120 | yyyy-mm-dd hh:mi:ss(24小时制)
-------------------------------------------------------------------------------------------------
- | 21 or 121 | yyyy-mm-dd hh:mi:ss:mmm(24小时制)
例如当前时间为：2005-9-12 13:20:00
我要取其中的：2005-9-12
select convert(char(10),'2005-9-12 13:20:00',120)
事例7：区分大小写
create table tab(idd int,address varchar(20) COLLATE Chinese_PRC_CS_AS)
insert tab values(1,'aaaa')
insert tab values(2,'AAAA')
insert tab values(3,'aaaa')
insert tab values(4,'aAAa')
insert tab values(5,'aAAA')
select * from tab where address = 'aaaa' COLLATE Chinese_PRC_CS_AS
select * from tab where address = 'aAAa' COLLATE Chinese_PRC_CS_AS
drop table tab
事例8：取n到m条记录的语句
1.
select top m * from tablename where id not in (select top n * from tablename)
2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列，那么：
select identity(int) id0,* into #temp from tablename
取n到m条的语句为：
select * from #temp where id0 >=n and id0 <= m
如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
5.如果表里有identity属性，那么简单：
select * from tablename where identitycol between n and m
事例9：stuff
sql替换表中某些字.
比如 titles表中有id,name ,age3个字段
我想根据name字段来修改id字段,并且在id字段前面添加'ABC'
update 表名 set id=stuff(id,1,3,'abc') where name='名称'
事例10；连接字段输出
select Name,Description from Students,现在我想把Name,Description作为一个字段Detail输出，请问如何写？
select Name+Description as Detail from Students
事例11；拷贝表格
建立表格：
CREATE TABLE tb(ID int, 名称 NVARCHAR(30), 备注 NVARCHAR(1000))
INSERT tb SELECT 1,'DDD',1
UNION ALL SELECT 1,'5100','D'
UNION ALL SELECT 1,'5200','E'
也可以这样:
CREATE TABLE tb1(ID int, 名称 NVARCHAR(30), 备注 NVARCHAR(1000))
INSERT TB1 (ID,名称,备注)VALUES(1,'DDD',1)
INSERT TB1 (ID,名称,备注)VALUES(1,'5100','D')
INSERT TB1 (ID,名称,备注)VALUES(1,'5200','E')
（2）
table_new 原来已经存在且字段和顺序和table_old 完全一样
insert into table_new select * from table_old
如不一样修改对应字段即可，注意插入数据主键问题..
insert into table_new(column1,column2)
select column1,column2 from table_old
或者
（3）数据库中不存在table_new表 很简单
select * into table_new from table_old
事例12：union（all）
select * from A
union
select * from B
--不合并重复行
select * from A
union all
select * from B
--如果要对字段进行排序
select *
from (
select id,... from A
union all
select id,... from B
) t
order by ID
事例13：模糊查询
like '%[AB]%'
只要满足字符串中包含A或者B或者AB就选出，顺序不限，这样会选出很多
like '%[AB]%'这样会选出如下组合
AB
BA
ACCCC
BCCCCC
ACB
BCA
事例14：别名
1: select * from
2: (
3: select yw_shangsi, sum(case yw_del when 1 then 1 else 0 end) as num
4: from yewuyuan
5: group by yw_shangsi having sum(case yw_del when 1 then 1 else 0 end)>=3
6: )
服务器: 消息 170，级别 15，状态 1，行 6
第 6 行: ')' 附近有语法错误。
1: select * from
2: (
3: select yw_shangsi, sum(case yw_del when 1 then 1 else 0 end) as num
4: from yewuyuan
5: group by yw_shangsi having sum(case yw_del when 1 then 1 else 0 end)>=3
6: ) aa
这样就没问题了
内嵌视图，要定义别名才能在select 后作为表使用
事例15：动态sql语句
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行
eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
2:字段名，表名，数据库名之类作为变量时，必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格
当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错
declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确
3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中？
declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
事例16：子查询与内联查询
create table #123(id int,N1 int,N2 int)
insert into #123
select 1,10,20
union all select 2,5,5
union all select 3,5,5
create table #234(id int,pid int,N1 int,N2 int)
insert into #234
select 1,1,10,20
union all select 2,1,5,5
--union all select 2,1,5,5（如果加入这一行就会出错）
select id ,(select pid from #234 where aaa.id=id) as bbb
from #123 as aaa
group by id
1 1
2 1
3 NULL
select aaa.id,bbb.pid
from #123 as aaa ,#234 as bbb
where aaa.id=bbb.id
1 1
2 1
这两个语句没有可比性啊, 因为两个语句并不等效.
第1个查询得到的结果是left join的效果, 而且还要保证匹配的id唯一才不会导致查询出错
第2个语句是inner join的结果, 只有匹配的记录才会出现在结果集中, 而且对id是否唯一没有任何要求
（邹老大对此的解释）
事例17：最常用的还有几个函数（之前介绍过几个）
（2）
LTRIM ( character_expression )删除字符变量中的起始空格
SELECT x = SUBSTRING('abcdef', 2, 3)
x ---------- bcd
RIGHT ( character_expression , integer_expression )
UPPER ( character_expression )
返回将小写字符数据转换为大写的字符表达式
LOWER ( character_expression )
将大写字符数据转换为小写字符数据后返回字符表达式。
（6）
REVERSE ( character_expression )
返回字符表达式的反转。
（7）
CAST ( expression AS data_type )
将某种数据类型的表达式显式转换为另一种数据类型
declare @a int
cast(@a as varchar(10))--从而连接动态语句
（8）
ISNUMERIC ( expression )
事例18：
首页随机显示一条:
select top 1 * from qynews where stype='4' order by newid()
sum(1)=count(1)=count(*)
sum(1)有一条记录就会加1，最后得到的就是记录总数，如果分组就可以得到每组的记录数
order by 1
按照查出的第一列排序
order by 2
按照查出的第二列排序
-按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/
--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/
--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/
--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5
*/
--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name,a.val
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2
*/
--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name , a.val
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 1 a1--a的第一个值
a 3 a3:a的第三个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select * , px = identity(int,1,1) into tmp from tb
select m.name,m.val,m.memo from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)
drop table tb,tmp
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select m.name,m.val,m.memo from
(
select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)
drop table tb
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
取第三条记录
create table stu(name varchar(10),age int)
insert into stu
select 'jac ',20 union all
select 'tom ',30 union all
select 'irs ',25
--cost 42.51%
select top 1 * from stu where name not in (select top 2 name from stu)
/*
name age
---------- -----------
irs 25
*/
--cost 47.71%
select * from stu where not exists(select 1 from
(select top 2 name from stu) t where name=stu.name)
/*
name age
---------- -----------
irs 25
*/
drop table stu
--------------------------------------------
扫一扫
1024
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
