我又两个表,一个表是问题类型表(T_IssueType),数据如下:
IssueTypeID IssueTypeName
1 Bug
2 Feature
3 安装问题
4 使用问题另一个表是事务表(T_TrackItem),主要字段如下:
TrackItemID IssueTypeID
1 1
2 1
3 2
4 3
5 NULL
6 NULL我现在通过全连接来查看T_TrackItem中每个问题类型的个数,SQL语句如下
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查询的结果如下:
TypeCount IssueTypeName IssueTypeID
2 Bug 1
1 Feature 2
1 安装问题 3
0 使用问题 4
0 NULL NULL但是我想把T_TrackItem表中字段IssueTypeID为空的数据的个数也查询出来,比如上面T_TrackItem表中为空的数目为两个,我的SQL语句如下:
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
查询的结果如下:
TypeCount IssueTypeName IssueTypeID
2 暂无 0
2 Bug 1
1 Feature 2
1 安装问题 3
1 使用问题 4问题就出现在最后一行数据上,本来“使用问题”的数目为0的,现在多了一个1,不知道问题出现在哪,大家帮帮忙吧,在线等
IssueTypeID IssueTypeName
1 Bug
2 Feature
3 安装问题
4 使用问题另一个表是事务表(T_TrackItem),主要字段如下:
TrackItemID IssueTypeID
1 1
2 1
3 2
4 3
5 NULL
6 NULL我现在通过全连接来查看T_TrackItem中每个问题类型的个数,SQL语句如下
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查询的结果如下:
TypeCount IssueTypeName IssueTypeID
2 Bug 1
1 Feature 2
1 安装问题 3
0 使用问题 4
0 NULL NULL但是我想把T_TrackItem表中字段IssueTypeID为空的数据的个数也查询出来,比如上面T_TrackItem表中为空的数目为两个,我的SQL语句如下:
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
查询的结果如下:
TypeCount IssueTypeName IssueTypeID
2 暂无 0
2 Bug 1
1 Feature 2
1 安装问题 3
1 使用问题 4问题就出现在最后一行数据上,本来“使用问题”的数目为0的,现在多了一个1,不知道问题出现在哪,大家帮帮忙吧,在线等
SELECT ISNULL(COUNT(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 left JOIN dbo.T_IssueType ON dbo.T_TrackItem.IssueTypeID = dbo.T_IssueType.IssueTypeID
GROUP BY dbo.T_IssueType.IssueTypeID, dbo.T_IssueType.IssueTypeName得到这样的结果:
TypeCount IssueTypeName IssueTypeID
0 暂无 0
2 Bug 1
1 Feature 2
1 安装问题 3
-- 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
*/
-- 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.IssueTypeID
from
[T_TrackItem] a left join [T_IssueType] b
on
a.IssueTypeID=b.IssueTypeID
group by
b.IssueTypeName,a.IssueTypeID
----------------结果----------------------------
/* IssueTypeName IssueTypeID
----------- ------------- -----------
2 NULL NULL
2 Bug 1
1 Feature 2
1 安装问题 3(4 行受影响)
*/你需要的结果是?
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
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(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.IssueTypeNameSELECT dbo.T_TrackItem.[IssueTypeID] ,dbo.T_IssueType.IssueTypeID 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
SELECT COUNT(T_TrackItem.[IssueTypeID]) AS TypeCount, ISNULL(T_IssueType.IssueTypeName, '暂无') AS IssueTypeName, ISNULL(T_IssueType.IssueTypeID, 0) AS IssueTypeID
FROM
(
SELECT [TrackItemID],ISNULL([IssueTypeID],0)AS [IssueTypeID] FROM dbo.T_TrackItem
) AS [T_TrackItem]
full JOIN dbo.T_IssueType
ON T_TrackItem.IssueTypeID = T_IssueType.IssueTypeID
GROUP BY T_IssueType.IssueTypeID, T_IssueType.IssueTypeName
----------------结果----------------------------
/*
TypeCount IssueTypeName IssueTypeID
----------- ------------- -----------
2 暂无 0
2 Bug 1
1 Feature 2
1 安装问题 3
0 使用问题 4
警告: 聚合或其他 SET 操作消除了 Null 值。
*/
我知道,但是这样写的话,并没有统计T_TrackItem中字段IssueType为空的数目,得出的结果为0:
TypeCount IssueTypeName IssueTypeID
0 暂无 0
2 Bug 1
1 Feature 2
1 安装问题 3
0 使用问题 4
第一行TypeCount应该是2,这里为0.
-- 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,b.IssueTypeID
from
[T_TrackItem] a full join [T_IssueType] b
on
a.IssueTypeID=b.IssueTypeID
group by
b.IssueTypeName,b.IssueTypeID
----------------结果----------------------------
/* IssueTypeName IssueTypeID
----------- ------------- -----------
2 NULL NULL
2 Bug 1
1 Feature 2
1 安装问题 3
1 使用问题 4(5 行受影响)*/这样吗?
-- 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)),isnull(b.IssueTypeName,'暂无'),isnull(b.IssueTypeID,0)
from
[T_TrackItem] a full join [T_IssueType] b
on
a.IssueTypeID=b.IssueTypeID
group by
b.IssueTypeName,b.IssueTypeID
----------------结果----------------------------
/*----------- -------- -----------
2 暂无 0
2 Bug 1
1 Feature 2
1 安装问题 3
1 使用问题 4(5 行受影响)*/
FROM
(
SELECT [TrackItemID],ISNULL([IssueTypeID],0)AS [IssueTypeID] FROM dbo.T_TrackItem
) AS [T_TrackItem]
full JOIN dbo.T_IssueType
ON T_TrackItem.IssueTypeID = T_IssueType.IssueTypeID
GROUP BY T_IssueType.IssueTypeID, T_IssueType.IssueTypeName
----------------结果----------------------------
/*
TypeCount IssueTypeName IssueTypeID
----------- ------------- -----------
2 暂无 0
2 Bug 1
1 Feature 2
1 安装问题 3
0 使用问题 4
警告: 聚合或其他 SET 操作消除了 Null 值。
*/
SELECT COUNT(T_TrackItem.[IssueTypeID]) AS TypeCount, ISNULL(T_IssueType.IssueTypeName, '暂无') AS IssueTypeName, ISNULL(T_IssueType.IssueTypeID, 0) AS IssueTypeID
FROM
(
SELECT [TrackItemID],ISNULL([IssueTypeID],0)AS [IssueTypeID] FROM dbo.T_TrackItem
) AS [T_TrackItem]
full JOIN dbo.T_IssueType
ON T_TrackItem.IssueTypeID = T_IssueType.IssueTypeID
GROUP BY T_IssueType.IssueTypeID, T_IssueType.IssueTypeName牛x啊,小弟佩服佩服结贴