SQL和LINQ按年月、按类型显示文章篇数

创建测试数据：

/* 文章表：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)
})