个人容易忘记的SQL写法

1. 处理日期型数据
Select * From Subject Where PubTime>=#01/01/2005#

2. Access中以“*”代替了标准SQL中的“%”。

3. “_”代表单一字符
Select * From Users Where Name Like '_ _'

4. Having子句允许为每个组指定条件，跟在Group By子句后面。
Select UserName,Count(*) As 文章数
From Subject
Group By UserName
Having UserName In ('Admin','Flyer')

5. 计算数学表达式
Select 5+6*10+10.5/23-(35-3)/90 As Result

6. 对于参数SQL Server中使用“@参数名”，而OleDb采用“?”，且向OleDbCommand对象加入参数时，顺序必须与SQL命令中定义的顺序一样。
strSql = "Select Count(*) From OrderClient Where ClientID = ?";

7. CASE语句
SELECT au_fname, au_lname,
   CASE state
      WHEN 'CA' THEN 'California'
      WHEN 'KS' THEN 'Kansas'
      WHEN 'TN' THEN 'Tennessee'
      WHEN 'OR' THEN 'Oregon'
      WHEN 'MI' THEN 'Michigan'
      WHEN 'IN' THEN 'Indiana'
      WHEN 'MD' THEN 'Maryland'
      WHEN 'UT' THEN 'Utah'
        END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname