汇总查询：截取字符查询

--建表
CREATE TABLE nian(id INT,niandu VARCHAR(50),username VARCHAR(50))
INSERT nian SELECT 1,'2002上半年','username1'
UNION ALL SELECT 2,'2003上半年','username2'
UNION ALL SELECT 3,'2003上半年','username3'
UNION ALL SELECT 4,'2003下半年','username4'
UNION ALL SELECT 5,'2003下半年','username5'
UNION ALL SELECT 6,'2003上半年','username6'
UNION ALL SELECT 7,'2005上半年','username7'

GO
--提醒：用分组查询,左边SELECT后面的字段,必须是聚合函数或者已经被GROUP BY的字段 
--GROUP BY 后不只是可以BY 列名，比如，此例
--查询
SELECT COUNT(id) AS idCount,LEFT(niandu,4) FROM nian GROUP BY LEFT(niandu,4)
--另一种想要的查询结果：
--SELECT COUNT(id) AS idCount,niandu FROM nian GROUP BY niandu UNION ALL SELECT COUNT(id),LEFT(niandu,4) AS niandu FROM nian GROUP BY LEFT(niandu,4)
--比较古老的方法
--SELECT  COUNT(id) AS idcount,nian2 FROM ( SELECT  id,LEFT(niandu,4) AS nian2 FROM nian ) nian GROUP BY nian2
GO
--删除表
DROP TABLE nian