SQL中select語句用法集錦
一、條件篩選
1、數字篩選:sql = "Select * from [sheet1$] Where 銷售單價 > 100"
2、字元條件:sql = "Select * from [sheet1$] Where 物品名稱 ='擋泥板'"
3、日期條件:sql = "Select * from [sheet1$] Where 物品名稱 ='擋泥板'"
4、區間條件:sql = "Select * from [sheet1$] Where 出庫日期 between #2005-1-4# and #2005-1-10#"
5、多個條件:用and,OR連接
'sql = "Select * from [sheet1$] Where 出庫日期 between #2005-1-4# and #2005-1-10# and 銷售單價>100"
Advertisements
6、模糊條件:%表示任意多個字元,_(下劃線)表示單個佔位符
'sql = "Select * from [sheet1$] Where 物品名稱 like '%扶手%'"
7、插入變數查詢:
'sr = "擋泥板"
'sql = "Select * from [sheet1$] Where 物品名稱 ='" & sr & "'"
8、在字元串組裡:
'sql = "Select * from [sheet1$] Where 物品名稱 in('車衣','扶手箱')"
9、借用函數優化:
'sql = "Select * from [sheet1$] Where left(物品代碼,3)='028'"
Advertisements
二、指定篩選
1、篩選全部欄位:*表示全部欄位
'sql = "Select * from ChuKu"
2、篩選指定欄位:在from前面列出要顯示的所有欄位,如果要跳過的用""""
'sql = "Select """",出庫日期,"""",物品代碼,"""",規格,單位 from ChuKu"
3、篩選不重複的:Distinct 欄位名 篩選不重複的記錄
'sql = "Select Distinct """",物品代碼 from ChuKu"
4、按百分比篩選:Top N percent 可以顯示前百分之N的記錄
'sql = "Select top 30 Percent * from ChuKu"
'對篩選的欄位用函數進行進一步的處理和格式化
'sql = "Select ID,Format(出庫日期,""yyyy-mm-dd"") from ChuKu"
5、排序:Desc降序,Asc升序
'sql = "Select * from ChuKu Order by 出庫日期 asc,銷售單價 desc"
'sql = "Select Top 10 * from ChuKu Order by 出庫日期 asc,銷售單價 desc" '篩選銷售數量前10()
三、分組顯示
Group by 可以配合函數進行分組求和,分組求最大值等。
'sql = "Select """","""",物品代碼,"""","""","""",sum(出庫數量) from ChuKu group by 物品代碼" 』 跳過的用""""
按條件顯示分組記錄
'sql = "Select """","""",物品代碼,"""","""","""",sum(出庫數量) from ChuKu group by 物品代碼 HAVING sum(出庫數量)>=3"