如何在SQL Server中的case语句中处理null [英] How to handle null in case statement in SQL server

问题描述

SELECT Prov.ProvinceName，CASE WHEN P.ProjectTypeID = 2 THEN COUNT（*）END Road，

SUM（例如P.ProjectTypeID = 2那么PP.Quantity ELSE 0 END）KM，

CASE WHEN P.ProjectTypeID = 1 THEN COUNT（*）END Bridge，

SUM（CASE WHEN P.ProjectTypeID = 1 THEN PP.Quantity ELSE 0 END）RM，

CASE WHEN P.ProjectTypeID = 3 THEN COUNT（*）END Building

来自项目P

INNER JOIN ProjectPRA PP ON P.ProjectID = PP.ProjectID

INNER JOIN zDistrict D ON P.DistrictID = D.DistrictID

INNER JOIN zProvince Prov ON D.ProvinceID = Prov.ProvinceID

WHERE PP.PRAID = 3

GROUP BY P.ProjectTypeID，Prov.ProvinceName



out put

---我不希望重复NULL

SELECT Prov.ProvinceName, CASE WHEN P.ProjectTypeID = 2 THEN COUNT(*) END Road,
SUM(CASE WHEN P.ProjectTypeID = 2 THEN PP.Quantity ELSE 0 END) KM,
CASE WHEN P.ProjectTypeID = 1 THEN COUNT(*) END Bridge,
SUM(CASE WHEN P.ProjectTypeID = 1 THEN PP.Quantity ELSE 0 END) RM,
CASE WHEN P.ProjectTypeID = 3 THEN COUNT(*) END Building
FROM Project P
INNER JOIN ProjectPRA PP ON P.ProjectID = PP.ProjectID
INNER JOIN zDistrict D ON P.DistrictID = D.DistrictID
INNER JOIN zProvince Prov ON D.ProvinceID = Prov.ProvinceID
WHERE PP.PRAID=3
GROUP BY P.ProjectTypeID,Prov.ProvinceName

out put
--- I don't want NULL to be repeated

Badakhshan	NULL	0	3	100	NULL
Badakhshan	8	95	NULL	0	NULL
Badghis	NULL	0	2	48	NULL
Badghis	1	15	NULL	0	NULL
Baghlan	NULL	0	2	83	NULL
Baghlan	8	62	NULL	0	NULL
Balkh	2	14	NULL	0	NULL
Bamyan	NULL	0	4	151	NULL
Bamyan	13	116	NULL	0	NULL
Daikundi	NULL	0	5	150	NULL
Daikundi	23	236	NULL	0	NULL
Daikundi	NULL	0	NULL	0	1
Farah	6	44	NULL	0	NULL
Faryab	NULL	0	7	262	NULL
Faryab	11	134	NULL	0	NULL
Ghazni	5	60	NULL	0	NULL
Ghor	NULL	0	1	60	NULL
Ghor	4	77	NULL	0	NULL
Helmand	2	30	NULL	0	NULL
Jowzjan	NULL	0	1	25	NULL
Jowzjan	5	57	NULL	0	NULL
Kabul	NULL	0	2	87	NULL
Kabul	4	25	NULL	0	NULL
Kabul	NULL	0	NULL	0	2
Kandahar	NULL	0	2	370	NULL
Kandahar	2	11	NULL	0	NULL
Khost	NULL	0	10	730	NULL
Khost	1	20	NULL	0	NULL
Kunar	13	126	NULL	0	NULL
Kunduz	NULL	0	1	80	NULL
Kunduz	13	94	NULL	0	NULL
Logar	NULL	0	2	70	NULL
Logar	1	10	NULL	0	NULL
Nengarhar	2	24	NULL	0	NULL
Nimroz	NULL	0	1	70	NULL
Nimroz	5	43	NULL	0	NULL
Nuristan	NULL	0	4	108	NULL
Nuristan	16	166	NULL	0	NULL
Paktika	2	21	NULL	0	NULL
Paktya	NULL	0	9	169	NULL
Paktya	3	45	NULL	0	NULL
Panjsher	NULL	0	1	60	NULL
Panjsher	1	9	NULL	0	NULL
Parwan	NULL	0	2	38	NULL
Samangan	1	13	NULL	0	NULL
Takhar	NULL	0	3	92	NULL
Takhar	8	74	NULL	0	NULL
Urozgan	2	40	NULL	0	NULL
Wardak	NULL	0	1	40	NULL
Wardak	2	16	NULL	0	NULL
Zabul	NULL	0	1	40	NULL
Zabul	1	10	NULL	0	NULL

我尝试了什么：



我尝试了子查询，外部申请，联合和不同sql case语句中的语句

What I have tried:

I have tried sub query, outer apply, union and different statement in sql case statement

推荐答案

试试这个：

Try this:

SELECT Prov.ProvinceName
	,CASE 
		WHEN P.ProjectTypeID = 2
			THEN COUNT(*)
		--Adding ELSE to cancel out the NULL
		ELSE 0
		END Road
	,SUM(CASE 
			WHEN P.ProjectTypeID = 2
				THEN PP.Quantity
			ELSE 0
			END) KM
	,CASE 
		WHEN P.ProjectTypeID = 1
			THEN COUNT(*)
		--Adding ELSE to cancel out the NULL
		ELSE 0
		END Bridge
	,SUM(CASE 
			WHEN P.ProjectTypeID = 1
				THEN PP.Quantity
			ELSE 0
			END) RM
	,CASE 
		WHEN P.ProjectTypeID = 3
			THEN COUNT(*)
		--Adding ELSE to cancel out the NULL
		ELSE 0		
		END Building
FROM Project P
INNER JOIN ProjectPRA PP ON P.ProjectID = PP.ProjectID
INNER JOIN zDistrict D ON P.DistrictID = D.DistrictID
INNER JOIN zProvince Prov ON D.ProvinceID = Prov.ProvinceID
WHERE PP.PRAID = 3
GROUP BY P.ProjectTypeID
	,Prov.ProvinceName

首先，如果你在SSMS中运行查询，文本NULL只是通知你单元格没有实际价值。如果您要在其他程序中运行该语句，则可能会以不同方式显示NULL值。如果您要在自己的程序中运行该语句，当您收到NULL值时，您可以决定如何显示它们。



话虽如此，如果您想使用SQL修改输出，一种简单的方法是使用 COALESCE [ ^ ]函数返回第一个非空值。例如，查询可能类似于

First of all, if you're running the query in SSMS, the text NULL is just to notify you that the cell has no actual value. If you're going to run the statement in some other program it may show NULL values differently. If you're going to run the statement in your own program, when you receive the NULL values, you can decide how to show them.

Having that said, if you want to modify the output using SQL, one easy way is to use COALESCE[^] function which returns the first non null value. For example the query could look something like

SELECT Prov.ProvinceName, 
       COALESCE( CASE WHEN P.ProjectTypeID = 2 THEN COUNT(*) END, 0) Road,
       COALESCE( SUM(CASE WHEN P.ProjectTypeID = 2 THEN PP.Quantity ELSE 0 END), 0) KM,
       COALESCE( CASE WHEN P.ProjectTypeID = 1 THEN COUNT(*) END, 0) Bridge,
       COALESCE( SUM(CASE WHEN P.ProjectTypeID = 1 THEN PP.Quantity ELSE 0 END), 0) RM,
       COALESCE( CASE WHEN P.ProjectTypeID = 3 THEN COUNT(*) END, 0) Building
FROM Project P
INNER JOIN ProjectPRA PP ON P.ProjectID = PP.ProjectID
INNER JOIN zDistrict D ON P.DistrictID = D.DistrictID
INNER JOIN zProvince Prov ON D.ProvinceID = Prov.ProvinceID
WHERE PP.PRAID=3 
GROUP BY P.ProjectTypeID,Prov.ProvinceName

问题是现在很难知道是否返回0，因为COUNT返回0或因为该值为NULL。解决这个问题的一种方法是将值转换为字符数据，并在null的情况下使用空字符串，例如

The problem is that it's now hard to know if 0 is returned because the COUNT returns 0 or because the value is NULL. One way to tackle this would be to convert the values to character data and use an empty string in case of null, for example

...
COALESCE( CAST( CASE WHEN P.ProjectTypeID = 1 THEN COUNT(*) END AS varchar(100)), 0) Bridge,
...

但现在我们正在做过多的格式化和SQL isn真的是为此设计的......

But now we're doing excessive formatting and SQL isn't really designed for this...

这篇关于如何在SQL Server中的case语句中处理null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！