请看下面的语句:
SELECT CASE WHEN tbDBPersonalType.ItemValue IS NULL
THEN '合计' ELSE tbDBPersonalType.ItemValue END AS Expr2,
COUNT(dbo.tbDocument.ID) AS Expr1
FROM dbo.tbDocument RIGHT OUTER JOIN
dbo.tbDBPersonalType ON
dbo.tbDocument.PersonalTypeID = dbo.tbDBPersonalType.ID
WHERE (dbo.tbDocument.OptDate BETWEEN '2009-1-1' AND '2011-12-31')
GROUP BY dbo.tbDBPersonalType.ItemValue WITH CUBE以上是文档表和文档类型表
我本意是想返回所有的文档表型,用的右连接.即如下所示:
类型 数量
类型1 11
类型2 0
类型3 25
合计 36现在的问题是:加上了WHERE (dbo.tbDocument.OptDate BETWEEN '2009-1-1' AND '2011-12-31')后,
"类型2 0"这个结果被过滤掉了,如何让它能正常显示?
SELECT CASE WHEN tbDBPersonalType.ItemValue IS NULL
THEN '合计' ELSE tbDBPersonalType.ItemValue END AS Expr2,
COUNT(dbo.tbDocument.ID) AS Expr1
FROM dbo.tbDocument RIGHT OUTER JOIN
dbo.tbDBPersonalType ON
dbo.tbDocument.PersonalTypeID = dbo.tbDBPersonalType.ID
WHERE (dbo.tbDocument.OptDate BETWEEN '2009-1-1' AND '2011-12-31')
GROUP BY dbo.tbDBPersonalType.ItemValue WITH CUBE以上是文档表和文档类型表
我本意是想返回所有的文档表型,用的右连接.即如下所示:
类型 数量
类型1 11
类型2 0
类型3 25
合计 36现在的问题是:加上了WHERE (dbo.tbDocument.OptDate BETWEEN '2009-1-1' AND '2011-12-31')后,
"类型2 0"这个结果被过滤掉了,如何让它能正常显示?
SELECT CASE WHEN tbDBPersonalType.ItemValue IS NULL
THEN '合计' ELSE tbDBPersonalType.ItemValue END AS Expr2,
COUNT(dbo.tbDocument.ID) AS Expr1
FROM (select * from dbo.tbDocument WHERE (dbo.tbDocument.OptDate BETWEEN '2009-1-1' AND '2011-12-31') ) as tbDocument
RIGHT OUTER JOIN
dbo.tbDBPersonalType ON
dbo.tbDocument.PersonalTypeID = dbo.tbDBPersonalType.ID
GROUP BY dbo.tbDBPersonalType.ItemValue
WITH CUBE
SELECT CASE WHEN tbDBPersonalType.ItemValue IS NULL
THEN '合计' ELSE tbDBPersonalType.ItemValue END AS Expr2,
COUNT(T.ID) AS Expr1
FROM (
SELECT * FROM dbo.tbDocument
WHERE (dbo.tbDocument.OptDate BETWEEN '2009-1-1' AND '2011-12-31')) T
RIGHT OUTER JOIN
dbo.tbDBPersonalType ON
T.PersonalTypeID = dbo.tbDBPersonalType.IDGROUP BY dbo.tbDBPersonalType.ItemValue WITH CUBE
THEN '合计' ELSE tbDBPersonalType.ItemValue END AS Expr2,
isnull(COUNT(dbo.tbDocument.ID),0) AS Expr1
FROM dbo.tbDocument RIGHT OUTER JOIN
dbo.tbDBPersonalType ON
dbo.tbDocument.PersonalTypeID = dbo.tbDBPersonalType.ID
and (dbo.tbDocument.OptDate BETWEEN '2009-1-1' AND '2011-12-31')
GROUP BY dbo.tbDBPersonalType.ItemValue WITH CUBE
CASE WHEN tbDBPersonalType.ItemValue IS NULL
THEN '合计' ELSE tbDBPersonalType.ItemValue END AS Expr2,
isnull(COUNT(T.ID),0) AS Expr1
FROM
(
SELECT * FROM dbo.tbDocument
WHERE (dbo.tbDocument.OptDate BETWEEN '2009-1-1' AND '2011-12-31')) T
RIGHT OUTER JOIN
dbo.tbDBPersonalType ON
T.PersonalTypeID = dbo.tbDBPersonalType.ID
GROUP BY
dbo.tbDBPersonalType.ItemValue WITH CUBE