mysql - 如何从MySQL中具有日期计算的查询结果创建表？

Question

我想从 qry 结果创建一个表。以下 qry 运行正常:

create table x select 
date_format(TERMINATION_DT,'%Y-%m-%d') as Term_Date
, date_format(BIRTH_DT,'%Y-%m-%d') as DOB
from dashboard_04102014
where status = 'withdrawn';

有一些termination_dt值为空''。 where status = 'withdrawn' 过滤掉空值。但是，当我尝试使用以下 qry 执行日期计算时，我收到一条错误消息:

create table x select 
date_format(TERMINATION_DT,'%Y-%m-%d') as Term_Date
, round(datediff(date_format(TERMINATION_DT,'%Y-%m-%d'), date_format(BIRTH_DT,'%Y-%m-%d'))/365,0) as Age
from dashboard_04102014
where status = 'withdrawn';

这是错误消息:

incorrect datetime value" '0000-00-00'

我猜测那些空的 termination_dt 值被转换为 0000-00-00 从而抛出了 datediff？但这些值首先被 where 子句过滤掉。

此外，我可以完美地运行 select 部分，它会返回我想要的结果。但是当我想从中创建一个表时，我收到了第二个查询的错误消息。为什么我可以使用第一个查询创建表，但不能使用第二个查询创建表？

谢谢!

添加原始表格条:

CREATE TABLE dashboard_04102014
   (`status` varchar(9), `termination_dt` int, `birth_dt` int);

INSERT INTO Table1
(`status`, `termination_dt`, `birth_dt`)
VALUES
('Active', 0, 19560329),
('Withdrawn', 20070208, 19690131),
('Withdrawn', 20090702, 19441219),
('Active', 0, 19520912),
('Withdrawn', 20130730, 19480404);

Answer 1

如果您没有从日期列中选择 NULL 或 empty 条目，则

drop table if exists x;

create table x 
select 
  cast( date_format( BIRTH_DT,'%Y-%m-%d' ) as date ) as BIRTH_DT
  , cast( date_format( TERMINATION_DT,'%Y-%m-%d' ) as date ) as Term_Date
  , cast( round( datediff( date_format( TERMINATION_DT, '%Y-%m-%d' ), 
                     date_format( BIRTH_DT, '%Y-%m-%d' ) 
                 ) / 365, 0 ) as decimal( 6, 2 ) ) as Age
from dashboard_04102014
where 
      status = 'withdrawn'
      -- un comment following line if required
      -- AND ( BIRTH_DT is not null and BIRTH_DT = '' and BIRTH != 0 )
  and ( TERMINATION_DT is not null 
        and TERMINATION_DT != '' 
        and TERMINATION_DT != 0 )
;

结果:

desc x;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| BIRTH_DT  | date         | YES  |     | NULL    |       |
| Term_Date | date         | YES  |     | NULL    |       |
| Age       | decimal(6,2) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

select * from x;
+------------+------------+-------+
| BIRTH_DT   | Term_Date  | Age   |
+------------+------------+-------+
| 1992-07-31 | 2050-07-31 | 58.00 |
| 1971-02-24 | 2029-02-24 | 58.00 |
+------------+------------+-------+

演示 1 @ MySQL 5.5.32 Fiddle
演示 2 @ MySQL 5.5.32 Fiddle