iT邦幫忙

0

我可以在SQL下類似 IF THEN 的語法嗎??

declare @year int
declare @yearid varchar(200)

SELECT @year=Year,@yearid=FiscalYearId FROM FiscalYearMonth where {@年份}

declare @1begin date
declare @1end date
declare @2begin date
declare @2end date
declare @3begin date
declare @3end date
declare @4begin date
declare @4end date
declare @5begin date
declare @5end date
declare @6begin date
declare @6end date
declare @7begin date
declare @7end date
declare @8begin date
declare @8end date
declare @9begin date
declare @9end date
declare @10begin date
declare @10end date
declare @11begin date
declare @11end date
declare @12begin date
declare @12end date


Select @1begin=BeginDate,@1end=EndDate From SalaryMonth where SalaryYear=@year and Month='1'
Select @2begin=BeginDate,@2end=EndDate From SalaryMonth where SalaryYear=@year and Month='2'
Select @3begin=BeginDate,@3end=EndDate From SalaryMonth where SalaryYear=@year and Month='3'
Select @4begin=BeginDate,@4end=EndDate From SalaryMonth where SalaryYear=@year and Month='4'
Select @5begin=BeginDate,@5end=EndDate From SalaryMonth where SalaryYear=@year and Month='5'
Select @6begin=BeginDate,@6end=EndDate From SalaryMonth where SalaryYear=@year+1 and Month='6'
Select @7begin=BeginDate,@7end=EndDate From SalaryMonth where SalaryYear=@year and Month='7'
Select @8begin=BeginDate,@8end=EndDate From SalaryMonth where SalaryYear=@year and Month='8'
Select @9begin=BeginDate,@9end=EndDate From SalaryMonth where SalaryYear=@year and Month='9'
Select @10begin=BeginDate,@10end=EndDate From SalaryMonth where SalaryYear=@year and Month='10'
Select @11begin=BeginDate,@11end=EndDate From SalaryMonth where SalaryYear=@year and Month='11'
Select @12begin=BeginDate,@12end=EndDate From SalaryMonth where SalaryYear=@year and Month='12'

這是SQL片段,因為他是篩選一整年度的,所以年份固定
例如 我下 2016 年,他就會把 2016年的所有月份的資料全顯示出來

可是我這裡想改成 我如果下 2016年5月,那我就把 2015年6月~2016年5月的資料顯示出來
因為SQL太複雜了,所以我想用很笨的方法去寫,就是將這12條SELECT 包成一個區塊

然後如果我下的條件是 2016年5月,就是將 6~12月的年分 -1 ,1-5月不變,但這個是寫死的

所以我要將這12條SQL包成12個,用IF THEN的方式去篩選
然後出現我要的資料

原始的資料真的複雜到不行,我只能用這個笨方法去做,感謝

2 個回答

3
做工仔人!
iT邦大師 1 級 ‧ 2016-08-11 17:06:52

請參考下列方式:

SET DATEFORMAT mdy  --此部份是因為Informix日期格式為mmddyy,SQL為yymmdd,故必須做此轉換
/*↓以下定義日期變數*/
declare @ym_p3	char(7)
declare @ym_p2	char(7)
declare @ym_p1	char(7)
declare @ym_now	char(7)
declare @ym_1	char(7)
declare @ym_2	char(7)
declare @ym_3	char(7)
declare @ym_4	char(7)
declare @ym_5	char(7)
declare @ym_6	char(7)
declare @now_mm	char(2)
declare @now_yy char(4)
select @now_yy = year(getdate())
select @now_mm  = month(getdate())

if @now_mm=12
begin
	select @ym_p3	=substring(cast(@now_yy + 1 as char(4)),1,4)+'/03'
	select @ym_p2	=substring(cast(@now_yy + 1 as char(4)),1,4)+'/02'
	select @ym_p1	=substring(cast(@now_yy + 1 as char(4)),1,4)+'/01'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/12'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/11'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/10'
	select @ym_3	=substring(cast(@now_yy     as char(4)),1,4)+'/09'
	select @ym_4	=substring(cast(@now_yy     as char(4)),1,4)+'/08'
	select @ym_5	=substring(cast(@now_yy     as char(4)),1,4)+'/07'
	select @ym_6	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
end
if @now_mm=11
begin
	select @ym_p3	=substring(cast(@now_yy + 1 as char(4)),1,4)+'/02'
	select @ym_p2	=substring(cast(@now_yy + 1 as char(4)),1,4)+'/01'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/12'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/11'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/10'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/09'
	select @ym_3	=substring(cast(@now_yy     as char(4)),1,4)+'/08'
	select @ym_4	=substring(cast(@now_yy     as char(4)),1,4)+'/07'
	select @ym_5	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
	select @ym_6	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
end
if @now_mm=10
begin
	select @ym_p3	=substring(cast(@now_yy + 1 as char(4)),1,4)+'/01'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/12'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/11'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/10'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/09'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/08'
	select @ym_3	=substring(cast(@now_yy     as char(4)),1,4)+'/07'
	select @ym_4	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
	select @ym_5	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
	select @ym_6	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
end
if @now_mm=9
begin
	select @ym_p3	=substring(cast(@now_yy     as char(4)),1,4)+'/12'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/11'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/10'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/09'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/08'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/07'
	select @ym_3	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
	select @ym_4	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
	select @ym_5	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
	select @ym_6	=substring(cast(@now_yy     as char(4)),1,4)+'/03'
end
if @now_mm=8
begin
	select @ym_p3	=substring(cast(@now_yy     as char(4)),1,4)+'/11'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/10'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/09'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/08'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/07'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
	select @ym_3	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
	select @ym_4	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
	select @ym_5	=substring(cast(@now_yy     as char(4)),1,4)+'/03'
	select @ym_6	=substring(cast(@now_yy     as char(4)),1,4)+'/02'
end
if @now_mm=7
begin
	select @ym_p3	=substring(cast(@now_yy     as char(4)),1,4)+'/10'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/09'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/08'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/07'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
	select @ym_3	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
	select @ym_4	=substring(cast(@now_yy     as char(4)),1,4)+'/03'
	select @ym_5	=substring(cast(@now_yy     as char(4)),1,4)+'/02'
	select @ym_6	=substring(cast(@now_yy     as char(4)),1,4)+'/01'
end
if @now_mm=6
begin
	select @ym_p3	=substring(cast(@now_yy     as char(4)),1,4)+'/09'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/08'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/07'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
	select @ym_3	=substring(cast(@now_yy     as char(4)),1,4)+'/03'
	select @ym_4	=substring(cast(@now_yy     as char(4)),1,4)+'/02'
	select @ym_5	=substring(cast(@now_yy     as char(4)),1,4)+'/01'
	select @ym_6	=substring(cast(@now_yy-1   as char(4)),1,4)+'/12'
end
if @now_mm=5
begin
	select @ym_p3	=substring(cast(@now_yy     as char(4)),1,4)+'/08'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/07'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/03'
	select @ym_3	=substring(cast(@now_yy     as char(4)),1,4)+'/02'
	select @ym_4	=substring(cast(@now_yy     as char(4)),1,4)+'/01'
	select @ym_5	=substring(cast(@now_yy-1   as char(4)),1,4)+'/12'
	select @ym_6	=substring(cast(@now_yy-1   as char(4)),1,4)+'/11'
end
if @now_mm=4
begin
	select @ym_p3	=substring(cast(@now_yy     as char(4)),1,4)+'/07'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/03'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/02'
	select @ym_3	=substring(cast(@now_yy     as char(4)),1,4)+'/01'
	select @ym_4	=substring(cast(@now_yy-1   as char(4)),1,4)+'/12'
	select @ym_5	=substring(cast(@now_yy-1   as char(4)),1,4)+'/11'
	select @ym_6	=substring(cast(@now_yy-1   as char(4)),1,4)+'/10'
end
if @now_mm=3
begin
	select @ym_p3	=substring(cast(@now_yy     as char(4)),1,4)+'/06'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/03'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/02'
	select @ym_2	=substring(cast(@now_yy     as char(4)),1,4)+'/01'
	select @ym_3	=substring(cast(@now_yy -1  as char(4)),1,4)+'/12'
	select @ym_4	=substring(cast(@now_yy-1   as char(4)),1,4)+'/11'
	select @ym_5	=substring(cast(@now_yy-1   as char(4)),1,4)+'/10'
	select @ym_6	=substring(cast(@now_yy-1   as char(4)),1,4)+'/09'
end
if @now_mm=2
begin
	select @ym_p3	=substring(cast(@now_yy     as char(4)),1,4)+'/05'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/03'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/02'
	select @ym_1	=substring(cast(@now_yy     as char(4)),1,4)+'/01'
	select @ym_2	=substring(cast(@now_yy-1   as char(4)),1,4)+'/12'
	select @ym_3	=substring(cast(@now_yy-1   as char(4)),1,4)+'/11'
	select @ym_4	=substring(cast(@now_yy-1   as char(4)),1,4)+'/10'
	select @ym_5	=substring(cast(@now_yy-1   as char(4)),1,4)+'/09'
	select @ym_6	=substring(cast(@now_yy-1   as char(4)),1,4)+'/08'
end
if @now_mm=1
begin
	select @ym_p3	=substring(cast(@now_yy     as char(4)),1,4)+'/04'
	select @ym_p2	=substring(cast(@now_yy     as char(4)),1,4)+'/03'
	select @ym_p1	=substring(cast(@now_yy     as char(4)),1,4)+'/02'
	select @ym_now	=substring(cast(@now_yy     as char(4)),1,4)+'/01'
	select @ym_1	=substring(cast(@now_yy-1   as char(4)),1,4)+'/12'
	select @ym_2	=substring(cast(@now_yy-1   as char(4)),1,4)+'/11'
	select @ym_3	=substring(cast(@now_yy-1   as char(4)),1,4)+'/10'
	select @ym_4	=substring(cast(@now_yy-1   as char(4)),1,4)+'/09'
	select @ym_5	=substring(cast(@now_yy-1   as char(4)),1,4)+'/08'
	select @ym_6	=substring(cast(@now_yy-1   as char(4)),1,4)+'/07'
end
--print @ym_1
--print @ym_2
--print @ym_3

想偷懶,結果變成一長串.

SQL 中 IF 的語法如下:

IF  判斷條件
   BEGIN 
    .....
   END
ELSE
   BEGIN
      .....
   END 

如果 BEGIN 與 END 之間只有一行敍述式的話. 可以省略 BEGIN 及 END

附贈一下 WHILE 的語法(TSQL HELP中的範例) :

USE pubs

DECLARE tnames_cursor CURSOR
FOR
   SELECT TABLE_NAME 
   FROM INFORMATION_SCHEMA.TABLES
	 
OPEN tnames_cursor

DECLARE @tablename sysname

--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename

WHILE (@@FETCH_STATUS <> -1)    

BEGIN

   IF (@@FETCH_STATUS <> -2)
	 
   BEGIN   
	 
      SELECT @tablename = RTRIM(@tablename) 
			
      EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM ' 
            + @tablename )
          
      PRINT ' '
			
   END
	 
   FETCH NEXT FROM tnames_cursor INTO @tablename
END

CLOSE tnames_cursor
DEALLOCATE tnames_cursor
tonyrao iT邦新手 5 級 ‧ 2016-08-11 17:23:08 檢舉

非常感謝 我馬上來試看看

1
賽門
iT邦超人 1 級 ‧ 2016-08-11 18:12:02

Why not?

多拜Google大神,有拜有保庇。

我要發表回答

立即登入回答