创建测试数据：
/* 文章表：Article */
CREATE TABLE Article(ID int,TypeID int,Title nvarchar(50),AddDate datetime)
INSERT Article SELECT 1, 1, N'文章1','2009-10-2'
UNION ALL SELECT 2, 1, N'文章2','2009-10-11'
UNION ALL SELECT 3, 1, N'文章3','2009-10-21'
UNION ALL SELECT 4, 1, N'文章4','2009-11-1'
UNION ALL SELECT 5, 1, N'文章5','2009-11-2'
UNION ALL SELECT 6, 2, N'文章6','2009-12-3'
UNION ALL SELECT 7, 2, N'文章7','2009-12-8'
UNION ALL SELECT 8, 3, N'文章8','2009-12-8'
UNION ALL SELECT 9, 3, N'文章9','2009-12-10'
UNION ALL SELECT 10, 3,N'文章10','2009-12-12'
GO
/* 文章类型表：Type */
CREATE TABLE Type(ID int,Name nvarchar(50))
INSERT Type SELECT 1, N'C#'
UNION ALL SELECT 2, N'JS'
UNION ALL SELECT 3, N'SQL'
GO
执行查询：
select * from Article
ID TypeID Title AddDate
----------- ----------- ------------ ----------------------
1 1 文章1 2009-10-02 00:00:00.000
2 1 文章2 2009-10-11 00:00:00.000
3 1 文章3 2009-10-21 00:00:00.000
4 1 文章4 2009-11-01 00:00:00.000
5 1 文章5 2009-11-02 00:00:00.000
6 2 文章6 2009-12-03 00:00:00.000
7 2 文章7 2009-12-08 00:00:00.000
8 3 文章8 2009-12-08 00:00:00.000
9 3 文章9 2009-12-10 00:00:00.000
10 3 文章10 2009-12-12 00:00:00.000
（所影响的行数为 10 行）
select * from Type
ID Name
----------- -------------
1 C#
2 JS
3 SQL
（所影响的行数为 3 行）
(1) 按年月查询文章篇数
select left(convert(char(8),AddDate,112),6) as AddDate, count(ID) as count from Article
group by left(convert(char(8),AddDate,112),6)
order by 1 desc
AddDate count
-------- -----------
200912 5
200911 2
200910 3
（所影响的行数为 3 行）
(2) 按类型查询文章篇数
SELECT [Name],
(SELECT COUNT(*) FROM Article AS a WHERE a.TypeID = t.ID) AS Count
FROM Type t
Name count
----------- -----------
C# 5
JS 2
SQL 3
（所影响的行数为 3 行）
--------------------------------------------------------------------------------------------------------------------------
23:33 2011-1-24
用LINQ查询实现：
(1)
from p in db.Articles
group p by new
{
Year = p.AddDate.Value.Year,
Month = p.AddDate.Value.Month
}
into g
orderby g.Key.Year descending, g.Key.Month descending
select new
{
YM = g.Key.Year + "年" + g.Key.Month + "月",
Count = g.Count()
}
(2)
from t in db.Types
select new
{
Name = t.Name,
Count = db.Articles.Count(a => a.TypeID == t.ID)
}
Lambda语法：
(1)
db.Articles.GroupBy(a => new {
Year = a.AddDate.Value.Year,
Month = a.AddDate.Value.Month
})
.Select(g => new {
YM = g.Key.Year + "年" + g.Key.Month + "月",
Count = g.Count()
}).OrderByDescending(g => g.YM.Substring(0,4))
.ThenByDescending(g => g.YM.Substring(5,g.YM.Length-6))
(2)
db.Types.Select(t => new
{
Name = t.Name,
Count = db.Articles.Count(a => a.TypeID == t.ID)
})
