在mysql中合并两列

63lcw9qa 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(347)

我正在尝试将两列fcalvdate和lcalvdate合并到lastcalvdate中，如果fcalvdate和lcalvdate的日期不同，我想在下一行中添加lastcalvdate的新日期。
我尝试过使用以下mysql查询

SELECT 

        `paid00_rpt_animreg`.`animalid` AS `animalid`,
        `paid00_rpt_animreg`.`fcalvdate`,
        `paid00_rpt_animreg`.`lcalvdate`,
        (CASE
            WHEN (`paid00_rpt_animreg`.`fcalvdate`IS NOT NULL) THEN `paid00_rpt_animreg`.`fcalvdate`
            WHEN (`paid00_rpt_animreg`.`lcalvdate` IS NOT NULL) THEN `paid00_rpt_animreg`.`lcalvdate`
        END) AS `LastCalvDate`
    FROM
        (`paid00_rpt_animreg`
        JOIN `reg04_lkpsex`)
    WHERE
        (`paid00_rpt_animreg`.`sex` = 2)

此查询的示例输出数据如下所示


# animalid, fcalvdate, lcalvdate, LastCalvDate

'070e0d2f-2403-4f97-b623-22cb0488745c', NULL, '2016-07-14', '2016-07-14'
'070e0d2f-2403-4f97-b623-22cb0488745c', NULL, '2016-07-14', '2016-07-14'
'070e0d2f-2403-4f97-b623-22cb0488745c', NULL, '2016-07-14', '2016-07-14'
'070e0d2f-2403-4f97-b623-22cb0488745c', NULL, '2016-07-14', '2016-07-14'
'070fdbb1-f39b-4f42-804c-4d7694f126d0', NULL, NULL, NULL
'08548c78-48b4-4e5b-81e7-d71caa03564c', NULL, '2017-02-19', '2017-02-19'
'08548c78-48b4-4e5b-81e7-d71caa03564c', NULL, '2017-02-19', '2017-02-19'
'08548c78-48b4-4e5b-81e7-d71caa03564c', NULL, '2017-02-19', '2017-02-19'
'08548c78-48b4-4e5b-81e7-d71caa03564c', NULL, '2017-02-19', '2017-02-19'

我的预期产出是


# animalid, fcalvdate, lcalvdate, LastCalvDate

'070e0d2f-2403-4f97-b623-22cb0488745c', 2016-07-14, '2016-07-14', '2016-07-14'
'070e0d2f-2403-4f97-b623-22cb0488745c', 2016-07-14, '2016-07-14', '2016-07-14'
'070e0d2f-2403-4f97-b623-22cb0488745c', 2016-07-14, '2016-07-14', '2016-07-14'
'070e0d2f-2403-4f97-b623-22cb0488745c', 2016-07-14, '2016-07-15', '2017-07-14'
'070e0d2f-2403-4f97-b623-22cb0488745c', 2017-07-14, '2016-07-15', '2017-07-15'
'070fdbb1-f39b-4f42-804c-4d7694f126d0', NULL, NULL, NULL
'08548c78-48b4-4e5b-81e7-d71caa03564c', 2017-02-19, '2017-02-19', '2017-02-19'
'08548c78-48b4-4e5b-81e7-d71caa03564c', 2017-02-19, '2017-02-19', '2017-02-19'
'08548c78-48b4-4e5b-81e7-d71caa03564c', '2017-02-19', '2017-02-19', '2017-02-19'
'08548c78-48b4-4e5b-81e7-d71caa03564c', '2017-02-19', '2017-02-19', '2017-02-19'

mysql

来源：https://stackoverflow.com/questions/52961886/merging-two-column-in-mysql

1条答案

按热度按时间

z9zf31ra1#

你可以简单地使用 Coalesce() :

SELECT 
  `paid00_rpt_animreg`.`animalid` AS `animalid`,
  COALESCE(`paid00_rpt_animreg`.`fcalvdate`, 
           `paid00_rpt_animreg`.`lcalvdate`) AS `fcalvdate`, 
  `paid00_rpt_animreg`.`lcalvdate`,
  COALESCE(`paid00_rpt_animreg`.`fcalvdate`, 
           `paid00_rpt_animreg`.`lcalvdate`) AS `LastCalvDate`
FROM
   (`paid00_rpt_animreg`
   JOIN `reg04_lkpsex`)
WHERE
   (`paid00_rpt_animreg`.`sex` = 2)

赞(0）回复(0）举报 2021-06-18

我来回答

在mysql中合并两列

1条答案

相关问题

热门标签

最新问答