Solutions to invalid is Null segment judgment and IFNULL() failure in MySql

MySql Null field judgment and IFNULL failure processing

ps: (If you don't want to see the process, just add a select outside your sql)

select a,b,c from table group by a,b,c //At this time, if a,b,c is judged to be null, it does not matter. select IFNULL(a,0),IFNULL(b,0),IFNULL(3,0) 
from (select a,b,c from table group by a,b,c ) //That's it

Let's take a look at this very simple table.

When I use SQL statements to group by PID and convert columns to rows, the results are displayed like this

No matter how I display IFNULL(SID1,0) or IF( SID1 is null,0,SID1), the result is still NULL -.-! I feel helpless.

My SQL is written like this. If you have not used the MAX function, you can ignore it and just look at IFNULL. It is obvious that I added IFNULL.

SELECT
			PID,
			MAX(CASE WHEN SID = 1 THEN IFNULL(PNUM ,0) END) AS SID1,
			MAX(CASE WHEN SID = 2 THEN IFNULL(PNUM ,0) END) AS SID2,
			MAX(CASE WHEN SID = 3 THEN IFNULL(PNUM ,0) END) AS SID3
		FROM
			(
				SELECT
					PID,
					SUM(PNUM) PNUM,
					SID
				FROM
					A
				GROUP BY
					PID
			) temp
		GROUP BY
			temp.PID

But I found that the result is still NULL, and then I found that IFNULL(), is null can only determine that the corresponding field exists in the table and is NULL, but there is obviously no such field in my table, so it cannot be determined!

After you select, the NULL value in the query is not recognized by mysql's is null and IFNULL()

Having said so much, how should we solve it?

It’s very simple, if you don’t have a watch, just get one! (Create a new Table?) What are you thinking about!

First, remove the IFNULL in the query. It has no effect.

Then use a select statement to query the result set as a table. This is equivalent to the field existing in the table you are querying and the value is NULL.

SELECT
	PID,IFNULL(SID1,0) SID1,IFNULL(SID2,0) SID2,IFNULL(SID3,0) SID3
FROM
	(
		SELECT
			PID,
			MAX(CASE WHEN SID = 1 THEN PNUM END) AS SID1,
			MAX(CASE WHEN SID = 2 THEN PNUM END) AS SID2,
			MAX(CASE WHEN SID = 3 THEN PNUM END) AS SID3
		FROM
			(
				SELECT
					PID,
					SUM(PNUM) PNUM,
					SID
				FROM
					A
				GROUP BY
					PID
			) temp
		GROUP BY
			temp.PID
	) temp1

See, the result is out hahahaha~

Problems encountered when using IFNULL() in MySql

About ifnull() Function

IFNULL(a,b) c

If a is not null, then the value of c is a

If a is null, then the value of c is b

Problems encountered in actual use

IFNULL(a,b) c

If a is not null but is ' ' an empty string, then the value of c is a empty string.

The actual desired result is that a is an empty string or null, and the value of c is b.

Solution

Use the if() function instead

if(a ='' or a is null,b,a) c

This means: if a is null or an empty string, c=b; otherwise c=a;

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.