SQL一些常用列子

---=========================================================================
1:SQLServer2005 Pivot 转置使用动态列  *****
--==========================================================================

if exists (select 1

            from  sysobjects

           where  id = object_id('Insurances')

            and   type = 'U')

   drop table Insurances

go
create table Insurances (

   RefID                uniqueidentifier     not null,
   HRMS                 nvarchar(20)         null,
   Name                 nvarchar(20)         null,
   InsuranceMoney       money                null,

   InsuranceName        nvarchar(100)        not null,

   constraint PK_INSURANCES primary key (RefID)

)
go

　
select  * from  Insurances

insert into Insurances values (newid(),1,'张三',200,'养老保险')

insert into Insurances values (newid(),1,'张三',300,'医疗保险')

insert into Insurances values (newid(),2,'李四',250,'养老保险')

insert into Insurances values (newid(),2,'李四',350,'医疗保险')

insert into Insurances values (newid(),3,'王二',150,'养老保险')

insert into Insurances values (newid(),3,'王二',300,'医疗保险')

select * from

(

select HRMS,Name,InsuranceMoney,InsuranceName from Insurances
) p

Pivot (

sum(InsuranceMoney)

FOR InsuranceName IN

( [医疗保险], [养老保险]))

as pvt

create procedure InsurancePivot
as
Begin
    DECLARE @ColumnNames VARCHAR(3000)
    SET @ColumnNames=''

    SELECT @ColumnNames = @ColumnNames + '[' + InsuranceName + '],'
    FROM ( SELECT DISTINCT InsuranceName FROM Insurances  ) t

    SET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1)

    DECLARE @selectSQL NVARCHAR(3000)
    SET @selectSQL=

    'SELECT HRMS,Name,{0} FROM
       (
       SELECT HRMS,Name,InsuranceMoney,InsuranceName FROM Insurances
       ) p
     Pivot( Max(InsuranceMoney)  For InsuranceName in ({0})) AS pvt
       ORDER BY HRMS'
    SET @selectSQL= REPLACE(@selectSQL,'{0}',@ColumnNames)
    exec sp_executesql @selectSQL
end

======================================================================
2:ROW_NUMBER() OVER函数的基本用法用法

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee
================================================================
3:SQL Server 中的IndexOf--PATINDEX
类似SQL Server中的CharIndex 和 C#中的IndexOf函数
CharIndex是得到一个Char的位置，而PATINDEX 可以得到一段字符串的位置
DECLARE @s VARCHAR(100)
SET @s = 'FileGuid=ed47252c-2d34-42d7-9faf-e5c32de82a5f'
SELECT PATINDEX('%FileGuid=%',@s) 值为 1

================================================
4：SQL查询出重复出现的数据
查询不重复的数据很简单

select distinct name from user

查询重复的name就要做一下处理了

select distinct name from user group by name having count(name)>1

sql中having用法:

这个是用在聚合函数的用法。当我们在用聚合函数的时候，一般都要用到GROUP BY 先进行分组，然后再进行聚合函数的运算。运算完后就要用到HAVING 的用法了，就是进行判断了，例如说判断聚合函数的值是否大于某一个值等等。
select customer_name,sum(balance)
from balance
group by customer_name
having balance>200;
===========================================================================
5：SQL 中的indexof函数CHARINDEX

DECLARE @a nvarchar(20)
SET @a = '10,234.00'

SELECT CHARINDEX('.',@a)
SELECT CHARINDEX('?',@a)
=================================================================================
6：SQL求百分比
select cast(cast(分子*1.0*100/分母 as decimal(10,2)) as varchar(50)) +'%'
=============================================================================
7：SQL当前日期获取技巧
===============================================
一个月第一天的SQL 脚本：
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
当天的半夜
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)
本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
===========================================================================================
8：SQL Server中Rollup关键字使用技巧

create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7)

insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
情况一：只有一个分类汇总列时，只需要一个合计。只需要增加with rollup即可。

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup
情况二:有多个分类汇总列，只需要一个合计.增加rollup之后，需要增加判断。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
    b,
sum(c),sum(d),sum(e) from #t
group by a,b with rollup
having grouping(b)=0 or grouping(a)=1select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
    b,
    c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(c)=0 or grouping(a)=1
情况三：有多个分类汇总列，需要全部的小计和合计。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
    case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b,
    case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
另外一种显示小计的方式

select case when grouping(a)=1 then '合计'
    when grouping(b)=1 then cast(a as varchar)+'小计'
    else cast(a as varchar) end a,
    case when grouping(b)=0 and grouping(c)=1
    then cast(b as varchar)+'小计' else cast(b as varchar) end b,
    case when grouping(c)=1 and grouping(b)=0
    then '' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup

情况四：有多个分类汇总列，需要部分的小计和合计

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
    b,
    case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=0
 

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
    case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b,
    c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=1 or grouping(c)=0
0 0 0

======================================================================================
9 sql存储过程 游标 循环表

--游标实例  利用游标循环表 根据userid赋值  
alter PROCEDURE CURSOR_EG1  
AS  
BEGIN  
declare @a int,@error int   
declare @temp varchar(50)--临时变量，用来保存游标值  
set @a=1 set @error=0  
BEGIN TRAN --申明事务  
--申明游标 为userid  
declare order_cursor CURSOR FOR select userid from usertable  
--打开游标  
open order_cursor  
 WHILE @@FETCH_STATUS = 0 --返回被 FETCH  语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。  
  begin  
   --开始循环游标变量  
   FETCH NEXT FROM order_cursor INTO @temp  
   --执行sql操作  
   update usertable set userpwd=username ,chinesename=@a where userid=@temp  
   set @a=@a+1  
   set @error=@error+@@error --记录每次运行sql后 是否正确  0正确  
  end  
if @error=0--没有错误 统一提交事务  
 begin  
  commit tran--提交  
 end  
else 
 begin  
     rollback tran--回滚  
 end  
CLOSE order_cursor--关闭游标  
DEALLOCATE order_cursor--释放游标  
END  
=================================================================================
 存储过程中表变量和临时表的建立【code】，和区别【理论】
、表变量缺省放在内存，速度快，所以在触发器，存储过程里如果数据量不大，应该用表变量。 
2、临时表缺省使用硬盘，一般来说速度比较慢，那是不是就不用临时表呢？也不是，在数据量比较大的时候，如果使用表变量，会把内存耗尽，然后使用TEMPDB的空间，这样主要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间，然后使用临时表。

 

declare  @tb table(id int identity(1,1) not null,name varchar(20))  --声明表变量
declare @RowCount int
    set @RowCount=5
    while @RowCount<>0
        begin 
            insert @tb select @RowCount
            set @RowCount=@RowCount-1
        end
select * from @tb

if object_ID(N'tempdb..#tb') is null     --object_ID 对象标识列
    begin   
        create table #tb(id int identity(11,1) not null,name varchar(20))  --创建临时表
        insert #tb select name from @tb
    end
else
    begin
        drop table #tb
    end
select * from #tb
drop table #tb
==================================================================================
简单事务应用
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Op_InsertProduct_Order]

AS
BEGIN
declare @error_1 int,@error_2 int,@tempIdentity int
 begin tran
 insert into OP_ProductTable (ProductName) values ('111')
 set @tempIdentity=@@identity
 set @error_1=@@error
 
 insert into OP_OrderTable (ProductID) values (@tempIdentity)
 set @error_2=@@error
 if(@error_1=0 and @error_2=0)
  begin
   commit tran
  end
 else
  begin
   rollback tran
  end

END

===================================================================================
DECLARE @dt datetime
SET @dt=GETDATE()

DECLARE @number int
SET @number=3

--1．指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'

--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'

--2．指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
    CONVERT(char(8),
        DATEADD(Month,
            DATEPART(Quarter,@dt)*3-Month(@dt)-2,
            @dt),
        120)+'1')

--B. 季度的最后一天（CASE判断法）
SELECT CONVERT(datetime,
    CONVERT(char(8),
        DATEADD(Month,
            DATEPART(Quarter,@dt)*3-Month(@dt),
            @dt),
        120)
    +CASE WHEN DATEPART(Quarter,@dt) in(1,4)
        THEN '31'ELSE '30' END)

--C. 季度的最后一天（直接推算法）
SELECT DATEADD(Day,-1,
    CONVERT(char(8),
        DATEADD(Month,
            1+DATEPART(Quarter,@dt)*3-Month(@dt),
            @dt),
        120)+'1')

--3．指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')

--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')

--C. 月的最后一天（容易使用的错误方法）
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))

--4．指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)

--5．指定日期所在周的任意星期几
--A.  星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

--B.  星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)

==================================================================================