数据库中null和任何值都是不相等的,用is null select * from tablename where leaguno is null
show create table players; 看一下。估计你的 leaguno 放得是 ' ' N个空格
CREATE TABLE PLAYERS (PLAYERNO INTEGER NOT NULL, NAME CHAR(15) NOT NULL, INITIALS CHAR(3) NOT NULL, BIRTH_DATE DATE , SEX CHAR(1) NOT NULL, JOINED SMALLINT NOT NULL, STREET VARCHAR(30) NOT NULL, HOUSENO CHAR(4) , POSTCODE CHAR(6) , TOWN VARCHAR(30) NOT NULL, PHONENO CHAR(13) , LEAGUENO CHAR(4) , PRIMARY KEY (PLAYNO) );
你的不会是字符串 'NULL' 吧 select * from where leaguno is null 的结果如何?
{你的不会是字符串 'NULL' 吧 select * from where leaguno is null 的结果如何? } 我都尝试了,还是不行!!!你看和我的表创建有关系没????
那就先不管你的表。直接用下面的试! create table PLAYERS1 ( INITIALS varchar(10), NAME varchar(20), LEAGUENO char(4) );insert into PLAYERS1 values ('R','Everentt','2411'), ('R','Parmentter','8467'), ('GWS','Wise',null), ('D','Bishop',null), ('M','Brown','6409'), ('PK','Hope','1608'), ('P','Parmenter','6524'); select * from PLAYERS1;select INITIALS,NAME , LEAGUENO, CASE WHEN LEAGUENO IS NULL THEN '1' END AS NEW_LEAGUENO FROM PLAYERS1;
如果这样,很简单,你的数据有问题了!贴你的 select * from PLAYERS where leaguno is null ;
贴你的数据及SHOW CREATE TABLE PLAYERS
这个是对的,如果你要找的仅仅是空字符串(“”),而不是null,则要用select * from tablename where trim(leaguno)=""
select * from tablename where leaguno is null
(PLAYERNO INTEGER NOT NULL,
NAME CHAR(15) NOT NULL,
INITIALS CHAR(3) NOT NULL,
BIRTH_DATE DATE ,
SEX CHAR(1) NOT NULL,
JOINED SMALLINT NOT NULL,
STREET VARCHAR(30) NOT NULL,
HOUSENO CHAR(4) ,
POSTCODE CHAR(6) ,
TOWN VARCHAR(30) NOT NULL,
PHONENO CHAR(13) ,
LEAGUENO CHAR(4) ,
PRIMARY KEY (PLAYNO) );
我都尝试了,还是不行!!!你看和我的表创建有关系没????
create table PLAYERS1 (
INITIALS varchar(10),
NAME varchar(20),
LEAGUENO char(4)
);insert into PLAYERS1 values
('R','Everentt','2411'),
('R','Parmentter','8467'),
('GWS','Wise',null),
('D','Bishop',null),
('M','Brown','6409'),
('PK','Hope','1608'),
('P','Parmenter','6524'); select * from PLAYERS1;select INITIALS,NAME , LEAGUENO,
CASE WHEN LEAGUENO IS NULL THEN '1' END AS NEW_LEAGUENO
FROM PLAYERS1;