如果在 T-SQL 中记录为 NULL，如何替换字符串 [英] How to Substitute a String if record is NULL in T-SQL

问题描述

我正在编写一个 T-SQL 报告，显示不同客户处于不同状态的帐户数量.报告结果如下:

I'm writing a T-SQL report that shows the number of accounts that are in different statuses for different customers. The report results in something like:

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

'NULL' 状态是有效数据，但我想显示Pending"而不是显示 NULL.到目前为止，这是我的 SQL:

The 'NULL' status is valid data, but instead of displaying NULL, I want to display "Pending". Here is my SQL so far:

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，所以这可能是一个容易回答的问题.

Any help would be appreciated, I'm still learning T-SQL so this might be an easy question to answer.

推荐答案

您可以使用 COALESCE 或 ISNULL.前者是标准的，返回第一个 NOT NULL 参数(如果所有参数都是 NULL，则返回 NULL)

You can use COALESCE or ISNULL. The former is standard and returns the first NOT NULL argument (or NULL if all arguments are NULL)

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

ISNULL 仅限于 2 个参数，但如果要测试的第一个值的评估成本很高(例如子查询)，则在 SQL Server 中效率更高.

ISNULL is restricted to only 2 arguments but is more efficient in SQL Server if the first value to be tested is expensive to evaluate (e.g. a subquery).

要注意 ISNULL 的一个潜在问题"是它返回第一个参数的数据类型，因此如果要替换的字符串比列数据类型允许的长，您将需要一个演员.

One potential "gotcha" with ISNULL to be aware of is that it returns the datatype of the first parameter so if the string to be substituted is longer than the column datatype would allow you will need a cast.

例如

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 都可以正常工作.

But ISNULL(CAST(C as VARCHAR(7)),'Unknown') or COALESCE would both work as desired.

这篇关于如果在 T-SQL 中记录为 NULL，如何替换字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！