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

sql語法

tonyrao 2016-08-11 16:53:42 ‧ 18041 瀏覽

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的方式去篩選
然後出現我要的資料

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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

做工仔人!

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

回應 3
分享
檢舉

做工仔人! iT邦大師 1 級 ‧ 2016-08-11 17:12:27 檢舉

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

SQL 中 IF 的語法如下:

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

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

做工仔人! iT邦大師 1 級 ‧ 2016-08-11 17:19:03 檢舉

附贈一下 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