如何获取null而不是0 [英] How to retrive null instead of 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

这篇关于如何获取null而不是0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！