用于检索家庭电话或手机的 MySQL 查询

Question

我有一个 MySQL 表，用于保存成员(member)资料。该表有 home_phone 和 cell_phone 字段，如下所示

First_name  Last_name   Home_phone  Cell_phone
John        Smith       4161234567  
Michael     More                    6471234567
Judy        Doe         4164567896  
Lolita      Garcia                  6471478956
XXXXx       YYYYYY      416231478
ZZZZZ       KKKKKK      416785123
MMMMM       NNNNNN      416789456
XXXXX       PPPPP       416555253

我想检索如下所示的数据:

First Name   Last Name   Phone Number
John         Smith       4161234567 
Michael      More        6471234567
Judy         Doe         4164567896 
Lolita       Garcia      6471478956

我如何编写 SQL 查询来实现这一点？

非常感谢。

Answer 1

COALESCE() 函数:

SELECT 
    `First_Name`, 
    `Last_Name`, 
    COALESCE(`Home_phone`, `Cell_phone`) as `Phone_Number`
FROM `table`;

---

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE t
    (`First_name` varchar(7), `Last_name` varchar(6), `Home_phone` varchar(10), `Cell_phone` varchar(10))
;

INSERT INTO t
    (`First_name`, `Last_name`, `Home_phone`, `Cell_phone`)
VALUES
    ('John', 'Smith', 4161234567, NULL),
    ('Michael', 'More', NULL, 6471234567),
    ('Judy', 'Doe', 4164567896, NULL),
    ('Lolita', 'Garcia', NULL, 6471478956),
    ('XXXXx', 'YYYYYY', 416231478, NULL),
    ('ZZZZZ', 'KKKKKK', 416785123, NULL),
    ('MMMMM', 'NNNNNN', 416789456, NULL),
    ('XXXXX', 'PPPPP', 416555253, NULL)
;

查询 1:

select * from t

Results :

| First_name | Last_name | Home_phone | Cell_phone |
|------------|-----------|------------|------------|
|       John |     Smith | 4161234567 |     (null) |
|    Michael |      More |     (null) | 6471234567 |
|       Judy |       Doe | 4164567896 |     (null) |
|     Lolita |    Garcia |     (null) | 6471478956 |
|      XXXXx |    YYYYYY |  416231478 |     (null) |
|      ZZZZZ |    KKKKKK |  416785123 |     (null) |
|      MMMMM |    NNNNNN |  416789456 |     (null) |
|      XXXXX |     PPPPP |  416555253 |     (null) |

查询 2:

select 
    `First_Name`, 
    `Last_Name`, 
    COALESCE(`Home_phone`, `Cell_phone`) as `Phone_Number`
from t

Results :

| First_name | Last_name | Phone_Number |
|------------|-----------|--------------|
|       John |     Smith |   4161234567 |
|    Michael |      More |   6471234567 |
|       Judy |       Doe |   4164567896 |
|     Lolita |    Garcia |   6471478956 |
|      XXXXx |    YYYYYY |    416231478 |
|      ZZZZZ |    KKKKKK |    416785123 |
|      MMMMM |    NNNNNN |    416789456 |
|      XXXXX |     PPPPP |    416555253 |