本文深入探讨了SQL语言的多种实用技巧，包括高级排序、条件筛选、字段操作、数据聚合等功能，帮助读者掌握复杂查询和数据处理的方法。
事例一：排序
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
事例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：最常用的还有几个函数（之前介绍过几个）
（1）ISNULL
使用指定的替换值替换 NULL。
语法
ISNULL ( check_expression , replacement_value )
参数
check_expression
将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。
replacement_value
在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。
返回类型
返回与 check_expression 相同的类型。
注释
如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value。
如果一个书名的价格是 NULL，那么在结果集中显示的价格为 0.00，而不是NULL的返回原值
ISNULL(price, 0.00)
（2）
LTRIM ( character_expression )删除字符变量中的起始空格
RTRIM ( character_expression ) 删除字符变量中的尾随空格
（3）
SUBSTRING ( expression , start , length )
参数
expression
是字符串、二进制字符串、text、image、列或包含列的表达式。不要使用包含聚合函数的表达式。
start
是一个整数，指定子串的开始位置。
length
是一个整数，指定子串的长度（要返回的字符数或字节数）。
SELECT x = SUBSTRING('abcdef', 2, 3)
下面是结果集：
x ---------- bcd
（4）
LEFT ( character_expression , integer_expression )
最左边的 5 个字符。
RIGHT ( character_expression , integer_expression )
参数
最右边的五个字符
（5）
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 )
当输入表达式得数为一个有效的整数、浮点数、money 或 decimal 类型，那么 ISNUMERIC 返回 1；否则返回 0。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。
注意判断后返回的是0和1
与它具有相同用法的就是ISDATE ( expression )
如：case when ISNUMERIC(字段)=1 then '数字' else '字符' end
事例18:
获得数据库中所有数据库的名字：select name From sysdatabases
获得某个数据库中所有表的名字：select name from sysobjects where type='U'
获得某个表中字段的名字：select name from syscolumns where id=object_id('表名')
use master
if exists(SELECT * From sysdatabases where name='test3')
drop database test3
create database test3
go
use test3
go
if exists (select * from sysobjects where type='U' and name='abc')
drop table abc
create table abc (
id int not null identity(20011001,1) primary key clustered,
name varchar(6) not null ,class int null , time1 datetime default getdate())
insert into abc
select 'a',1,1 union all
select 'b',2,2
insert into abc values('a',null,default)
嵌套游标的使用
declare cur1 cursor for
select ... from table_name1 where ...group by / order by ...
open cur1
FETCH NEXT FROM cur1 INTO ...
while (@@FETCH_STATUS = 0 )
begin
....
declare cur2 cursor for
select ... from table_name2 where ...group by / order by ...
set @sql='select ... from table_name2 where ...group by / order by ...'
exec @sql
open cur2
FETCH NEXT FROM cur2 INTO ...
while (@@FETCH_STATUS = 0 )
begin
....
FETCH NEXT FROM cur2 INTO ...
end
....
close cur2
DEALLOCATE cur2
FETCH NEXT FROM cur1 INTO ...
end
close cur1
deallocate cur1
要使用CUBE，首先要了解GROUP BY。
其实CUBE和ROLLUP区别不太大，只是在基于GROUP BY 子句创建和汇总分组的可能的组合上有一定差别，CUBE将返回的更多的可能组合。如果在GROUP BY子句中有N个列或者是有N个表达式的话，SQLSERVER在结果集上会返回2的N－1次幂个可能组合。
CUBE和ROLLUP之间的区别在于：
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合
ROLLUP就是将GROUP BY后面的第一列名称求总和，而其他列并不要求
而CUBE则会将每一个列名称都求总和
COPY了一个例子，首先用ROLLUP
查询语句：
Select cust_id,product_code,sum(qty) as quantity
From invoices Where cust_id IN (4,5)
Group By cust_id, product_code
WITH Rollup Order By cust_id
查询结果：
cust_id product_code quantity
------- -------------- -------
NULL NULL 10
4 5 3
4 6 3
4 NULL 6
5 5 4
5 NULL 4
第一行是4,5买的所有产品的数量(
第二行是4买5产品的数量
第三行是4买6产品的数量
第四行是4买所有产品的数量（按照cust_id=4总汇）
第五行是5买5产品的数量
第六行是5买所有产品的数量（按照cust_id=5总汇）
如果查询语句中的ROLLUP关键字更改为CUBE，就会多出有关产品的信息
查询结果：
cust_id product_code quantity
------- -------------- -------
NULL NULL 10
NULL 5 7
NULL 6 3
4 5 3
4 6 3
4 NULL 6
5 5 4
5 NULL 4
第2行是所有顾客买5产品的数量（product_code=5 总汇）
第3行是所有顾客买6产品的数量（product_code=6 总汇）
扫一扫
488
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
