日期:2014-05-18 浏览次数:20754 次
SELECT COUNT(dbo.T_TrackItem.IssueTypeID) AS TypeCount, dbo.T_IssueType.IssueTypeName, dbo.T_IssueType.IssueTypeID FROM dbo.T_IssueType full JOIN dbo.T_TrackItem ON dbo.T_TrackItem.IssueTypeID = dbo.T_IssueType.IssueTypeID GROUP BY dbo.T_IssueType.IssueTypeID, dbo.T_IssueType.IssueTypeName
SELECT COUNT(ISNULL(dbo.T_IssueType.IssueTypeID, 0)) AS TypeCount, ISNULL(dbo.T_IssueType.IssueTypeName, '暂无') AS IssueTypeName, ISNULL(dbo.T_IssueType.IssueTypeID, 0) AS IssueTypeID FROM dbo.T_TrackItem full JOIN dbo.T_IssueType ON dbo.T_TrackItem.IssueTypeID = dbo.T_IssueType.IssueTypeID GROUP BY dbo.T_IssueType.IssueTypeID, dbo.T_IssueType.IssueTypeName
---------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2012-06-28 09:34:31 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------- --> 测试数据:[T_IssueType] if object_id('[T_IssueType]') is not null drop table [T_IssueType] go create table [T_IssueType]([IssueTypeID] int,[IssueTypeName] varchar(8)) insert [T_IssueType] select 1,'Bug' union all select 2,'Feature' union all select 3,'安装问题' union all select 4,'使用问题' --> 测试数据:[T_TrackItem] if object_id('[T_TrackItem]') is not null drop table [T_TrackItem] go create table [T_TrackItem]([TrackItemID] int,[IssueTypeID] int) insert [T_TrackItem] select 1,1 union all select 2,1 union all select 3,2 union all select 4,3 union all select 5,null union all select 6,null --------------开始查询-------------------------- select count(isnull(a.IssueTypeID,0)),b.IssueTypeName,a.TrackItemID from [T_TrackItem] a left join [T_IssueType] b on a.IssueTypeID=b.IssueTypeID group by b.IssueTypeName,a.TrackItemID ----------------结果---------------------------- /* IssueTypeName TrackItemID ----------- ------------- ----------- 1 Bug 1 1 Bug 2 1 Feature 3 1 安装问题 4 1 NULL 5 1 NULL 6 */
------解决方案--------------------