调试易

你的表里面的数据是这样的吗？我这里测试没问题。mysql> select * from players;
+-----------+----------+----------+----------+--------------+
| 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)

你判断null值改为1的字段的查询结果字段应该是new_leagueno

你的 LEAGUENO 是NULL还是'' ?select * from PLAYERS where LEAGUENO is null; 测试一下。 语句应该没问题。

我的查询语句也写得和你一样啊，为啥我的结果就没将initials =‘GWS ’的leagueno :new_leagueno的值附为1？？？能否讲解一下原因啊？？？+----------+----------+----------+--------------+
| initials | name     | leagueno | new_leagueno |
+----------+----------+----------+--------------+
| R        | Everentt |     2411 | NULL         |
| GWS      | Wise     |     NULL | null           |
| M        | Brown    |     6409 | NULL         |
+----------+----------+----------+--------------+

给出你的 create table PLAYERS  语句

我的 LEAGUENO 是NULL！ 用select * from PLAYERS where LEAGUENO is null 查询的结果一样，也是不能查询出正确的结果！！！无记录

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)         );

show create table PLAYERS ;

或者改成
select INITIALS,NAME , LEAGUENO,IFNULL(LEAGUENO,'1')  AS NEW_LEAGUENO
FROM PLAYERS WHERE TOWN = 'Stratford';
试下。

做一下如下试验。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 LEAGUENO='null';看有没有数据，怀疑你是保存的字符串'null'

INITIALS NAME LEAGUENO NEW_LEAGUENO
R Everentt 2411         2411
R Parmentter 8467         8467
GWS Wise
D Bishop
M Brown         6409          6409
PK Hope         1608          1608
P Parmenter 6524             6524
结果如上，还是不对！！！！！

MySQL 版本号多少？ SQL_MODE 是什么？ (另外建议你象下面一样直接贴结果)mysql> select version();
+----------------------+
| 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>

SQL codemysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.0.7-beta-nt |
+----------------------+
1 row in set (0.00 sec)mysql> select @@sql_mode 为空，无记录1 row in set (0.00 sec)

show variables like 'sql_mode';
结果呢？