摘要:SQL語法
<SQL>
----時間轉換語法
WHERE CONVERT(varchar( 8), CreateTime, 112) = @SDate
@SDate可以直接用字串 YYYYMMDD
WHERE CONVERT(varchar( 8), CreateTime, 111) = @SDate
@SDate可以直接用字串 YYYY/MM/DD
----Table裡多筆取一筆最新的
Select * From (Select *,
ROW_NUMBER() Over
(Partition By CompName Order By CreateTIme Desc) As Sort From vComp) TMP_S
Where TMP_S.Sort=1)
(Select * ROW_NUMBER() OVER (分割 by 公司名稱 用建立時間做到序 As Sort )
ROW_NUMBER() Over ----流水號
Partition By CompName ----以公司名字做分割
Desc----倒敘 (asc---順序)
Where TMP_S.Sort=1 ---最新一筆資料
----- 找出今年
select year( DATEADD(year, 0, getdate())) 今年
select year( DATEADD(year, 1, getdate())) 明年
---- 判斷是否有重複筆數
select substring(PKID,8,7) as a from IFS..AppItemC where substring(PKID, 1, 3) =
(select year(DATEADD(year, 0, getdate())) )) b group by a having count(a) > 1
----count/sum代條件
select
count( CASE WHEN T.isY='Y' THEN 1 ELSE NULL END ) as Ysum ,
count( CASE WHEN T.isY='N' THEN 1 ELSE NULL END ) as Nsum ,
from Tbname T
----group by 後面帶入條件
錯誤做法：select * from user_num where count(user)>＝2 group by user;
正確做法：select * from user_num group by user HAVING count(user)>＝2
