mysql - 无法显示一段时间内的信息

Question

我试图显示一段时间内两列之间的条件，如下所示:

这是demo

以下是信息:

CREATE TABLE clients
(date_birth date, [date_anniversary] date)
;

INSERT INTO clients
([date_birth], [date_anniversary])
VALUES
('1911-01-20',NULL ),
('1921-01-20',NULL ),
('1931-01-20',NULL ),
('1941-01-20',NULL ),
('1951-01-20',NULL ),
('1961-01-20',NULL ),
('1971-01-20',NULL ),
('1981-01-20',NULL ),
('1991-01-20',NULL ),
(NULL, '1998-02-13'),
(NULL, '1999-02-13'),
(NULL, '2000-02-13'),
(NULL, '2001-02-13'),
(NULL, '2002-02-13'),
(NULL, '2003-02-13'),
(NULL, '2004-02-13'),
(NULL, '2005-02-13'),
(NULL, '2006-02-13');

我试图展示:

('1911-01-20',NULL ),
('1921-01-20',NULL ),
('1931-01-20',NULL ),
('1941-01-20',NULL ),
('1951-01-20',NULL ),
('1961-01-20',NULL ),
('1971-01-20',NULL ),
('1981-01-20',NULL ),
('1991-01-20',NULL ),
(NULL, '1998-02-13'),
(NULL, '1999-02-13'),

我尝试过这个但不起作用:

select * from clients where date_birth OR date_anniversary BETWEEN '1910-01-01' AND '2000-01-01'

请问有人可以帮助我吗？

Answer 1

你必须写出完整的条件:

select * from clients 
where 
    date_birth BETWEEN '1910-01-01' AND '2000-01-01'
OR 
    date_anniversary BETWEEN '1910-01-01' AND '2000-01-01'

在您的特殊情况下:两列中只有一列包含非 NULL 值，您可以使用函数 COALESCE ，返回参数列表的第一个非空值:

select 
    * from clients 
where 
    COALESCE(date_birth, date_anniversary) BETWEEN '1910-01-01' AND '2000-01-01';

但是即使这看起来更简单，性能也可能比第一个解决方案差。您应该检查 EXPLAIN。

在您的(已修改)Demo 中查看它