SQL 資料集參數寫法範例

過濾組件綁定參數功能優化

1）參數顯示優化：

• 只顯示主題內資料源包含的參數，不再顯示與主題無關的參數；

• 參數顯示參數來源路徑和參數名，方便定位參數來源；

• 參數展示使用雙層目錄樹，展示效果更清晰；

2）參數綁定優化

• 支援一個過濾元件綁定多個SQL參數，綁定多個參數後，所有綁定參數都受過濾元件值的控制

• 同一個儀表板的過濾組件設定綁定SQL參數時，不能選擇在該儀表板中已經被綁定過的參數

SELECT * FROM 互網路連結存取資料 WHERE YAER(銷售日期)='${年份}' and month(銷售日期)='${月份}'

SELECT * FROM 互網路連結存取資料 WHERE format(統計日期,'yyyyMM') >=concat('${開始年份}',case when '${開始月份}'>9 then '${開始月份}'else concat(0,'${開始月份}') end ) and format(統計日期,'yyyyMM') <=concat('${截至年份}',case when '${截至月份}'>9 then '${截至月份}'else concat(0,'${截至月份}') end )

SELECT * FROM 互網路連結存取資料 WHERE CAST(YEAR(註冊時間) as varchar) = '${年份}' and CAST(FLOOR((MONTH(註冊時間)+2)/3) as varchar) = '${季度}'

參數只能取一個值

select *  from new_dian  where  1=1 ${if(len(所屬大區2) == 0,"","and  所屬大區 in ('" + 所屬大區2 + "')")}

或者：select * from new_dian where 1=1 <parameter> and 所屬大區 in ('${所屬大區}')</parameter>

SELECT * FROM 互網路連結存取資料 WHERE TO_CHAR("銷售日期,"'YYYY')='${年份A}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'YYYY')='${年份}' and EXTRACT(MONTH FROM "銷售日期")='${月份}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'YYYY-MM-DD') in ('${日期參數}')

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'YYYY-MM-DD') between '${開始日期}' and '${結束日期}'

SELECT * FROM new_salesdetail  WHERE TO_CHAR("銷售日期",'YYYYMM') >=concat('${開始年份}',case when '${開始月份}'>9 then '${開始月份}'else concat(0,'${開始月份}') end ) and TO_CHAR("銷售日期",'YYYYMM') <=concat('${截止年份}',case when '${截止月份}'>9 then '${截止月份}' else concat(0,'${截止月份}') end )

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("註冊時間",'YYYY')='${年份}' and FLOOR((EXTRACT(MONTH FROM "註冊時間")+2)/3)='${季度}'

正文

參數只能取一個值

SELECT * FROM demo_contract WHERE "合同類型"='${正文參數}'

SELECT * FROM demo_contract WHERE "合同類型" in ('${正文參數}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and "合同類型" in ('${正文參數}') </parameter>

SELECT * FROM new_salesdetail WHERE cast(year(銷售日期) as varchar)='${年份}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'yyyy')='${年份}' and cast(EXTRACT(MONTH FROM "銷售日期") as varchar)='${月份}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'yyyy-mm-dd') in ('${日期參數}')

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'yyyy-mm-dd') between '${開始日期}' and '${結束日期}'

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("註冊時間",'yyyymm') >=concat('${開始年份}',case when cast('${開始月份}' as tinyint)>9 then '${開始月份}' else concat('0','${開始月份}') end ) and TO_CHAR("註冊時間",'yyyymm') <=concat('${截至年份}',case when cast('${截至月份}' as tinyint)>9 then '${截至月份}' else concat('0','${截至月份}') end )

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("註冊時間",'yyyy')='${年份}' and cast(FLOOR((EXTRACT(MONTH FROM "註冊時間")+2)/3) as varchar)='${季度}'

參數只能取一個值

SELECT * FROM demo_contract WHERE "合同類型"='${正文參數}'

SELECT * FROM demo_contract WHERE "合同類型" in ('${正文參數}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and "合同類型" in ('${正文參數}') </parameter> 用於

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'YYYY')='${年份A}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'YYYY')='${年份}' and EXTRACT(MONTH FROM "銷售日期")='${月份}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'YYYY-MM-DD') in ('${日期參數}')

SELECT * FROM new_salesdetail WHERE TO_CHAR("銷售日期",'YYYY-MM-DD') between '${開始日期}' and '${結束日期}'

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("註冊時間",'yyyymm') >=concat('${開始年份}',case when cast('${開始月份}' as tinyint)>9 then '${開始月份}' else concat('0','${開始月份}') end ) and TO_CHAR("註冊時間",'yyyymm') <=concat('${截至年份}',case when cast('${截至月份}' as tinyint)>9 then '${截至月份}' else concat('0','${截至月份}') end )

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("註冊時間",'YYYY')='${年份}' and FLOOR((EXTRACT(MONTH FROM "註冊時間")+2)/3) ='${季度}'

參數只能取一個值

SELECT * FROM demo_contract WHERE "合同類型"='${正文參數}'

SELECT * FROM demo_contract WHERE "合同類型" in ('${正文參數}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and "合同類型" in ('${正文參數}') </parameter>

SELECT * FROM xxxx WHERE cast(year(銷售日期) as STRING)='${年份}'

SELECT * FROM xxxx WHERE cast(year(銷售日期) as STRING)='${年份}' and cast(month(銷售日期) as STRING)='${月份}'

SELECT * FROM xxxx WHERE 註冊時間 in ('${日期參數}')

SELECT * FROM xxxx WHERE 註冊時間 between '${開始日期}' and '${結束日期}'

SELECT * FROM xxxx WHERE TO_CHAR("註冊時間",'yyyymm') >=concat('${開始年份}',case when cast('${開始月份}' as tinyint)>9 then '${開始月份}' else concat('0','${開始月份}') end ) and TO_CHAR("註冊時間",'yyyymm') <=concat('${截至年份}',case when cast('${截至月份}' as tinyint)>9 then '${截至月份}' else concat('0','${截至月份}') end )

SELECT * FROM xxxx WHERE cast(year(註冊時間) as STRING)='${年份}' and cast(truncate((month(註冊時間)+2)/3) as STRING)='${季度}'

參數只能取一個值

SELECT * FROM demo_contract WHERE 合同類型='${正文參數}'

SELECT * FROM demo_contract WHERE 合同類型 in ('${正文參數1}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and 合同類型 in ('${正文參數}') </parameter>

SELECT * FROM xxxx WHERE toString(toYear(註冊時間)) ='${年份}'

SELECT * FROM xxxx WHERE toString(toYear(註冊時間)) ='${年份}' and toString(toMonth(註冊時間)) ='${月份}'

SELECT * FROM xxxx WHERE 註冊時間 in ('${日期參數}')

SELECT * FROM xxxx WHERE 註冊時間 between '${開始日期}' and '${結束日期}'

SELECT * FROM xxxx WHERE TO_CHAR("註冊時間",'yyyymm') >=concat('${開始年份}',case when cast('${開始月份}' as tinyint)>9 then '${開始月份}' else concat('0','${開始月份}') end ) and TO_CHAR("註冊時間",'yyyymm') <=concat('${截至年份}',case when cast('${截至月份}' as tinyint)>9 then '${截至月份}' else concat('0','${截至月份}') end )

SELECT * FROM xxxx WHERE toString(toYear(註冊時間))='${年份}' and toString(floor((toMonth(註冊時間)+2)/3))='${季度}'

參數只能取一個值

SELECT * FROM demo_contract WHERE "合同類型"='${正文參數}'

SELECT * FROM demo_contract WHERE "合同類型" in ('${正文參數}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and "合同類型" in ('${正文參數}') </parameter>