如何获取null而不是0
问题描述：
在下面的代码中，当没有值时，检索到0，但我的要求是NULL.有没有可能?请回复我.
In the following code, when there is no value, 0 is retrieved but my requirement is to be NULL . any possibility? please reply me.
SELECT ntblDesignation.DisigantionName, ISNULL(COUNT(CASE WHEN tblConnectivity.intWANid = '1' THEN tblConnectivity.intWANid END), NULL) AS VirtualPinNetwork,
ISNULL(COUNT(CASE WHEN tblConnectivity.intWANid = '2' THEN tblConnectivity.intWANid END), NULL) AS LeasedLine,
ISNULL(COUNT(CASE WHEN tblConnectivity.intWANid = '3' THEN tblConnectivity.intWANid END), NULL) AS APN,
ISNULL(COUNT(CASE WHEN tblConnectivity.intWANid = '4' THEN tblConnectivity.intWANid END), NULL) AS Others
FROM tblConnectivity INNER JOIN
ntblDistrict ON tblConnectivity.intDistrictid = ntblDistrict.intDistrictid INNER JOIN
ntblDesignation ON tblConnectivity.intDesignation = ntblDesignation.intDesiginationID
GROUP BY ntblDesignation.DisigantionName
干杯，
ramu.
Cheers,
ramu.
答
计数永远不会为null，而是为0.因此，您要做的是编写将0转换为null的代码.因此，请使用大小写将0转换为null.
A count is never null, it is 0. So what you want to do, is write code to convert 0 in to null. So use a case to convert 0 to null.
SELECT
ntblDesignation.DisigantionName,
(Case when (Select count(1) from tblConnectivity where intWANid = '1')>0 THEN Count(1) else null END) AS VirtualPinNetwork,
(Case when (Select count(1) from tblConnectivity where intWANid = '2')>0 THEN Count(1) else null END) AS LeasedLine,
(Case when (Select count(1) from tblConnectivity where intWANid = '3')>0 THEN Count(1) else null END) AS APN,
(Case when (Select count(1) from tblConnectivity where intWANid = '3')>0 THEN Count(1) else null END) AS Others
FROM tblConnectivity INNER JOIN
ntblDistrict ON tblConnectivity.intDistrictid = ntblDistrict.intDistrictid INNER JOIN
ntblDesignation ON tblConnectivity.intDesignation = ntblDesignation.intDesiginationID
GROUP BY ntblDesignation.DisigantionName