SQL技巧

1、UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
2、declare 可以新建参数表

DECLARE @TempTable table
(FIndex int identity,FName Varchar(50),FValue varchar(50))

INSERT INTO @TempTable 
(FName,FValue  )
SELECT 'A',1 UNION SELECT 'B',2 UNION SELECT 'C',3 

DECLARE @i int ,@Count int,@FName VARCHAR(50)

select @Count = count(1) FROM @TempTable 
set @i = 1

WHILE @i <= @Count 
begin
    SELECT @FName = FName from @TempTable where FIndex = @i
    SELECT * from @TempTable WHERE FName = @FName 
    set @i = @i +1
end

3、with 表名
使用with，表只能被使用一次
sql2000不支持 with这里写代码片

--  WITH t
--  AS (
--      SELECT @DateTimeA AS y, year(@DateTimeA) AS FYear,MONTH(@DateTimeA) AS FMonth
--      union all
--      select dateadd(m,1,y),year(dateadd(m,1,y)),MONTH(dateadd(m,1,y))
--      from t where y <@DateTimeB
--  )
--      SELECT * 
--  into #t 
--  from t
    create table #t
    (
        y datetime,
        FYear int,
        FMonth int
    )
    declare @FNow datetime
    set @FNow = @DateTimeA
    While @FNow <= @DateTimeB
    begin
        insert into #t
        values(dateadd(m,1,@FNow),year(dateadd(m,1,@FNow)),MONTH(dateadd(m,1,@FNow)))
        set @FNow = dateadd(m,1,@FNow)
    end
    --exec STKD_P_XHCWageSum0921 '2016-1-1','2016-9-1',''