select INITIALS,NAME , LEAGUENO,CASE
WHEN LEAGUENO IS NULL THEN '1'
END AS NEW_LEAGUENO
FROM PLAYERS WHERE TOWN = 'Stratford';以上的查询语句,将‘LEAGUENO ’为空值时,用‘1’填充。语句感觉写的也没问题啊,但是为啥总是不对!如下:INITIALS NAME LEAGUENO NEW_LEAGUENO
R Everentt 2411
R Parmentter 8467
GWS Wise
D Bishop
M Brown 6409
PK Hope 1608
P Parmenter 6524 GWS,D的LEAGUENO本应为‘1’,但是还是空值,请高手指导一下!!!
WHEN LEAGUENO IS NULL THEN '1'
END AS NEW_LEAGUENO
FROM PLAYERS WHERE TOWN = 'Stratford';以上的查询语句,将‘LEAGUENO ’为空值时,用‘1’填充。语句感觉写的也没问题啊,但是为啥总是不对!如下:INITIALS NAME LEAGUENO NEW_LEAGUENO
R Everentt 2411
R Parmentter 8467
GWS Wise
D Bishop
M Brown 6409
PK Hope 1608
P Parmenter 6524 GWS,D的LEAGUENO本应为‘1’,但是还是空值,请高手指导一下!!!
+-----------+----------+----------+----------+--------------+
| town | initials | name | leagueno | new_leagueno |
+-----------+----------+----------+----------+--------------+
| stratford | R | Everentt | 2411 | NULL |
| stratford | GWS | Wise | NULL | NULL |
| stratford | M | Brown | 6409 | NULL |
+-----------+----------+----------+----------+--------------+
3 rows in set (0.00 sec)mysql> select initials,name,leagueno,
-> case when leagueno is null then '1' end as new_leagueno
-> from players where town='stratford';
+----------+----------+----------+--------------+
| initials | name | leagueno | new_leagueno |
+----------+----------+----------+--------------+
| R | Everentt | 2411 | NULL |
| GWS | Wise | NULL | 1 |
| M | Brown | 6409 | NULL |
+----------+----------+----------+--------------+
3 rows in set (0.00 sec)
| initials | name | leagueno | new_leagueno |
+----------+----------+----------+--------------+
| R | Everentt | 2411 | NULL |
| GWS | Wise | NULL | null |
| M | Brown | 6409 | 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) );
select INITIALS,NAME , LEAGUENO,IFNULL(LEAGUENO,'1') AS NEW_LEAGUENO
FROM PLAYERS WHERE TOWN = 'Stratford';
试下。
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;看结果是什么?
R Everentt 2411 2411
R Parmentter 8467 8467
GWS Wise
D Bishop
M Brown 6409 6409
PK Hope 1608 1608
P Parmenter 6524 6524
结果如上,还是不对!!!!!
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)mysql>
+----------------------+
| version() |
+----------------------+
| 5.0.7-beta-nt |
+----------------------+
1 row in set (0.00 sec)mysql> select @@sql_mode 为空,无记录1 row in set (0.00 sec)
结果呢?