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',''