如果在T-SQL中记录为NULL,如何替换字符串

我正在撰写一份T-SQL陈诉,表现差异客户处于差异状态的帐户数目.陈诉功效如下：

Customer1    NoService        7
Customer1    IncompleteOrder  13
Customer1    NULL             9
Customer2    NoService        12
Customer2    Available        19
Customer2    NULL             3
...

‘NULL’状态是有用数据,但我想表现“Pending”而不是表现NULL.到今朝为止,这是我的SQL：

USE cdwCSP;
SELECT
   sr.sales_region_name   AS SalesRegion,micv.value,COUNT(sr.sales_region_name)
FROM prospect p
   LEFT JOIN sales_region sr
     ON p.salesRegionId = sr.sales_region_number
   LEFT JOIN prospectOrder po
     ON po.prospectId = p.prospectId
   LEFT JOIN wo
     ON wo.prospectId = p.prospectId
   LEFT JOIN woTray wot
     ON wot.woId = wo.woId
   LEFT JOIN miscInformationCustomerCategory micc
     ON micc.prospectId = p.prospectId
   LEFT JOIN miscInformationCustomerValues micv
     ON micv.miscInformationCustomerCategoryId = micc.miscInformationCustomerCategoryId
   LEFT JOIN miscInformationCategory mic
     ON micc.miscInformationCategoryId = mic.miscInformationCategoryId
WHERE wot.dateOut IS NULL
     AND mic.categoryName LIKE '%Serviceability%'
GROUP BY sr.sales_region_name,micv.value
ORDER BY sr.sales_region_name,micv.value;

任何辅佐将不胜谢谢,我如故在进修T-SQL,以是这也许是一个轻易答复的题目.

办理要领

SELECT COALESCE(micv.value,'Pending') as value

ISNULL仅限于2个参数,但假如要测试的第一个值很昂贵(譬喻子查询),则SQL Server中的服从更高.

ISNULL要留意的一个隐藏“题目”是它返回第一个参数的数据范例,因此假如要替代的字符串比列数据范例应承的话,则必要举办逼迫转换.

譬喻.

CREATE TABLE T(C VARCHAR(3) NULL);

INSERT T VALUES (NULL);

SELECT ISNULL(C,'Unknown')
FROM T

将返回Unk

但ISNULL(CAST(C as VARCHAR(7)),’Unknown’)或COALESCE都可以按预期事变.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!