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