主页 / dba / 问题 / 305877
Accepted
Martial
Asked: 2022-01-11 02:31:54 +0800 CST

unsigned int 上的负值与 union all

  • 772

当我对带有 unsigned int 字段的查询进行联合时，它会返回否定结果。

有人可以向我解释这种行为吗？如何在“ticketIn”中拥有真实身份？

架构（MySQL v5.7）

CREATE TABLE IF NOT EXISTS `history` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `ticket` INT(11) unsigned NOT NULL,
  `group` INT(11) unsigned NOT NULL,
  `prev_group` INT(11) unsigned NOT NULL,
  `date_mod` DATETIME NOT NULL,
  PRIMARY KEY(Id)
);

-- data
INSERT INTO history
    (`ticket`, `group`, `prev_group`, `date_mod`)
VALUES
    (2201100001, 53042, 0, '2022-01-10 00:04:05.000'),
    (2201100002, 53042, 0, '2022-01-10 00:10:01.000'),
    (2201100003, 53042, 0, '2022-01-10 00:10:02.000'),
    (2201100003, 52973, 53042, '2022-01-10 00:11:25.000'),
    (2201100004, 53043, 0, '2022-01-10 00:54:41.000'),
    (2201100004, 52972, 53043, '2022-01-10 01:08:20.000')
;

查询 #1

SET
  @s = CAST('2022-01-10' AS DATE);

查询 #2

SELECT
  DATE_FORMAT(`history_out`.date_mod, '%Y-%m-%d') AS "thedate",
  `history_out`.group,
  `history_out`.ticket AS "ticketOut",
  NULL AS "ticketIn",
  `history_out`.id
FROM
  history `history_out`
WHERE
  (`history_out`.prev_group IN (53042))
  AND (`history_out`.date_mod >= @s)
UNION
ALL
SELECT
  DATE_FORMAT(`history_in`.date_mod, '%Y-%m-%d') AS "thedate",
  `history_in`.group,
  NULL AS "ticketOut",
  `history_in`.ticket AS "ticketIn",
  `history_in`.id
FROM
  history `history_in`
WHERE
  (`history_in`.group IN (53042))
  AND (`history_in`.date_mod >= @s);
日期 团体 出票 票务 ID
2022-01-10 52973 2201100003 4
2022-01-10 53042 -2093867295 1
2022-01-10 53042 -2093867294 2
2022-01-10 53042 -2093867293 3

在 DB Fiddle 上查看

mysql union

1 个回答

  1. Best Answer

    尝试这个：

    SELECT
  DATE_FORMAT(`history_out`.date_mod, '%Y-%m-%d') AS "thedate",
  `history_out`.group,
  `history_out`.ticket AS "ticketOut",
  cast(NULL as unsigned int) AS "ticketIn",
  `history_out`.id
FROM
  history `history_out`
WHERE
  (`history_out`.prev_group IN (53042))
  AND (`history_out`.date_mod >= @s)
UNION
ALL
SELECT
  DATE_FORMAT(`history_in`.date_mod, '%Y-%m-%d') AS "thedate",
  `history_in`.group,
  NULL AS "ticketOut",
  `history_in`.ticket AS "ticketIn",
  `history_in`.id
FROM
  history `history_in`
WHERE
  (`history_in`.group IN (53042))
  AND (`history_in`.date_mod >= @s);

    dbfiddle.uk

    PS。当我使用 RDBMS 时，联合中的第一个选择决定了列的格式。MySQL 5.7 可能已经很老了。

    • 2

相关问题