小技巧
得到分组中每个组的第一条记录
思路:不能用top 1 和group by,而是使用自己和自己连接,找到ID最大的那条记录
SELECT * FROM card_customercarddetail X
LEFT JOIN card_customercarddetail Y
ON X.cardnumber=Y.cardnumber AND X.ID<Y.ID
WHERE Y.ID IS NULL
2。一个表的字段更新位另外一个表的值
UPDATE #tmp SET #tmp.LastBalance=(SELECT #tmp2.LastBalance FROM #tmp2 WHERE #tmp.CardNumber=#tmp2.CardNumber ),
#tmp.LastAmount=(SELECT #tmp2.LastAmount FROM #tmp2 WHERE #tmp.CardNumber=#tmp2.CardNumber)
WHERE #tmp.CardNumber IN(SELECT #tmp2.CardNumber FROM #tmp2 )
3。t-sql中补0: SET @Month=RIGHT('00'+@Month,2)
4。时间比较,也可以进行字符串比较:
CONVERT(VARCHAR(10),AccountDate,120)>=@Year+'-'+@Month+'-'+'01' AND CONVERT(VARCHAR10),AccountDate,120)<=@Year+'-'+@Month+'-'+'31'