本文共 2505 字，大约阅读时间需要 8 分钟。
创建测试数据：
/* 文章表：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) })
转载地址：http://jxzrf.baihongyu.com/