事例一：排序
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
事例13：别名
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 后作为表使用
事例13：动态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