Count()返回0而不是NULL
我已阅读其他问题,答案似乎无法解决我的情况。我有一张公司名称表和一份与这些公司有关的活动表。Count()返回0而不是NULL
我需要计算一个指定日期范围内的活动量,并返回0,如果有没有。我还需要获取最后一次活动的日期(即使它超出指定的日期范围)。我使用LEFT OUTER JOIN
,我试过ISNULL()
(虽然,如果它不存在,它怎么检查它是否为空?),我不知道该从哪里去。
这是我有:
SELECT v_rpt_Company.Company_Name, COUNT(DISTINCT SO_Activity.SO_Activity_Recid) as touches, MAX(SO_Activity.Date_Entered) As lasttouch
FROM v_rpt_Member LEFT OUTER JOIN
Company_Team ON v_rpt_Member.Member_RecID = Company_Team.Member_RecID LEFT OUTER JOIN
v_rpt_Company ON Company_Team.Company_RecID = v_rpt_Company.Company_RecID LEFT OUTER JOIN
SO_Activity ON v_rpt_Company.Company_RecID = SO_Activity.Company_RecID
WHERE (Company_Team.AcctMgr_Flag = 1) AND (v_rpt_Member.Member_ID = @member) AND ((SO_Activity.Last_Update >= CONVERT(datetime, @date_start, 101)) AND (SO_Activity.Last_Update <= CONVERT(datetime, @date_end, 101)))
GROUP BY v_rpt_Company.Company_Name
ORDER BY v_rpt_Company.Company_Name ASC,lasttouch DESC,touches DESC
这是最终的答案:(谢谢大家)
SELECT a.touches, a.name,b.lasttouch
FROM (
SELECT v_rpt_Company.Company_Name as name,
COUNT(DISTINCT SO_Activity.SO_Activity_Recid) as touches
FROM v_rpt_Company
LEFT OUTER JOIN Company_Team ON Company_Team.Company_RecID = v_rpt_Company.Company_RecID AND Company_Team.AcctMgr_Flag = 1
LEFT OUTER JOIN v_rpt_Member ON v_rpt_Member.Member_RecID = Company_Team.Member_RecID
LEFT OUTER JOIN SO_Activity ON v_rpt_Company.Company_RecID = SO_Activity.Company_RecID AND ((SO_Activity.Last_Update >= CONVERT(datetime, @date_start, 101)) AND (SO_Activity.Last_Update <= CONVERT(datetime, @date_end, 101)))
WHERE v_rpt_Member.Member_ID = @member
Group By v_rpt_Company.Company_Name) As a
LEFT OUTER JOIN
(SELECT MAX(SO_Activity.Date_Entered) As lasttouch, v_rpt_Company.Company_Name as name
FROM v_rpt_Company
LEFT OUTER JOIN Company_Team ON Company_Team.Company_RecID = v_rpt_Company.Company_RecID AND Company_Team.AcctMgr_Flag = 1
LEFT OUTER JOIN v_rpt_Member ON v_rpt_Member.Member_RecID = Company_Team.Member_RecID
LEFT OUTER JOIN SO_Activity ON v_rpt_Company.Company_RecID = SO_Activity.Company_RecID
WHERE v_rpt_Member.Member_ID = @member
GROUP BY v_rpt_Company.Company_Name) as b
ON a.name = b.name
更新:尝试此操作,首先使用v_rpt_company,因为您尝试按公司na进行分组我。
SELECT v_rpt_Company.Company_Name,
COUNT(DISTINCT SO_Activity.SO_Activity_Recid) as touches,
MAX(SO_Activity.Date_Entered) As lasttouch
FROM v_rpt_Company
LEFT OUTER JOIN Company_Team ON Company_Team.Company_RecID = v_rpt_Company.Company_RecID AND Company_Team.AcctMgr_Flag = 1
LEFT OUTER JOIN v_rpt_Member ON v_rpt_Member.Member_RecID = Company_Team.Member_RecID
LEFT OUTER JOIN SO_Activity ON v_rpt_Company.Company_RecID = SO_Activity.Company_RecID AND ((SO_Activity.Last_Update >= CONVERT(datetime, @date_start, 101)) AND (SO_Activity.Last_Update <= CONVERT(datetime, @date_end, 101)))
WHERE v_rpt_Member.Member_ID = @member
GROUP BY v_rpt_Company.Company_Name
ORDER BY v_rpt_Company.Company_Name ASC,lasttouch DESC,touches DESC
每个公司都会返回0。但是,谢谢你,这是我得到的最接近的。 – 2012-03-15 18:07:05
你可以从每个表格添加模式和2-3行到你的问题 – 2012-03-15 18:08:40
我很难发布关于表格的很多数据,我处于NDA之下。 – 2012-03-15 18:12:48
修改您的count语句,像这样:
COALESCE(COUNT(DISTINCT SO_Activity.SO_Activity_Recid),0)
没有骰子。它不显示0行数公司的行。 – 2012-03-15 18:02:13
哦,我得到你了。 (Company_Team.AcctMgr_Flag = 1)的where子句将导致该外连接在Company_Team中没有行与连接匹配时不返回记录。所有其他表的外部都加入到具有where子句的表中。您可以添加一个“OR字段为空”,使这些连接返回行 – Adam 2012-03-15 18:03:57
检查我的帖子,当你做了离开外部联接,然后添加一个where子句条件的表列它不会返回任何空列 – 2012-03-15 18:04:50
有两个问题与您查询:
- 你有
WHERE
条款来看,这将是错误的时候,那里已经为这家公司没有任何活动(因为从SO_Activity
值将是无效的情况下,那么条件也将为空)。 - 如果上次活动的日期可能超出日期范围,则需要单独加入。
这里是固定的版本:
SELECT
v_rpt_Company.Company_Name,
ISNULL(COUNT(DISTINCT SO_Activity.SO_Activity_Recid), 0) as touches,
MAX(SO2.Date_Entered) As lasttouch
FROM v_rpt_Member
LEFT OUTER JOIN Company_Team ON v_rpt_Member.Member_RecID = Company_Team.Member_RecID
LEFT OUTER JOIN v_rpt_Company ON Company_Team.Company_RecID = v_rpt_Company.Company_RecID
LEFT OUTER JOIN SO_Activity ON v_rpt_Company.Company_RecID = SO_Activity.Company_RecID
AND SO_Activity.Last_Update >= CONVERT(datetime, @date_start, 101)
AND SO_Activity.Last_Update <= CONVERT(datetime, @date_end, 101)
LEFT OUTER JOIN SO_Activity SO2 ON v_rpt_Company.Company_RecID = SO_Activity.Company_RecID
WHERE Company_Team.AcctMgr_Flag = 1 AND v_rpt_Member.Member_ID = @member
GROUP BY v_rpt_Company.Company_Name
ORDER BY v_rpt_Company.Company_Name ASC,lasttouch DESC,touches DESC
这将返回所有公司名称,触摸总是= 0,并且lasttouch始终为空。 – 2012-03-15 18:10:52
检查我更新的查询 – 2012-03-15 18:13:07
,并已显示'NULL'的'COUNT'当疗法是'Company_name'值?还是它确实是文'Company_name'是'NULL' ? – Lamak 2012-03-15 17:59:07
你可以发布你的表结构吗? – 2012-03-15 18:00:44
不,不会为有0个活动的公司返回行。我无法发布表结构,它们包含敏感的公司数据,对不起。 – 2012-03-15 18:02:38