我在选择中有一个别名,如下所示:

SELECT
    CASE ....
    WHEN 1 THEN somefield * -10
    WHEN 0 THEN somefield * 10
    END as myAlias
FROM
    ....

问题是,在mysql查找的第一条记录中,它将“myAlias”标记为UNSIGNED
但在下一条记录中,同一个别名生成一个有符号整数,然后出现错误:
BIGINT UNSIGNED value is out of range in....

那么,在mysql执行任何操作之前,如何告诉它“myAlias”是有符号整数呢?
谢谢!
编辑:
我试过了
SELECT
    CAST(CASE ....
    WHEN 1 THEN somefield * -10
    WHEN 0 THEN somefield * 10
    END AS SIGNED) as myAlias
FROM
    ....

没有成功!

最佳答案

我假设表中的字段声明为UNSIGNED INTEGER。在这种情况下,必须在THEN表达式的CASE子句中键入cast。下面的示例演示了一个示例。
创建并插入脚本:

CREATE TABLE mytable
(
     numberfield INTEGER UNSIGNED
   , bitfield BIT
);

INSERT INTO mytable (numberfield, bitfield) VALUES
  (134, 1),
  (673, 0),
  (855, 1),
  (133, 0),
  (778, 1),
  (234, 0);

在THEN子句中键入强制转换字段:SQL Fiddle Demo
SELECT
    CASE bitfield
        WHEN 1 THEN CAST(numberfield AS SIGNED) * -10
        WHEN 0 THEN CAST(numberfield AS SIGNED) * 10
    END as myAlias
FROM mytable

输出:
MYALIAS
-------
-1340
6730
-8550
1330
-7780
2340

编写上述查询的替代方法:SQL Fiddle Demo
SELECT
    CAST(numberfield AS SIGNED) *
    (
        CASE bitfield
            WHEN 1 THEN -10
            WHEN 0 THEN 10
        END
    ) as myAlias
FROM mytable

输出:
MYALIAS
-------
-1340
6730
-8550
1330
-7780
2340

不使用类型转换字段:SQL Fiddle Demo
SELECT
    CASE bitfield
      WHEN 1 THEN numberfield * -10
      WHEN 0 THEN numberfield * 10
    END as myAlias
FROM mytable

错误:
数据截断:BIGINT UNSIGNED value在“(db_5b3bbmytablenumberfield*—(10))中超出范围:选择CASE bitfield WHEN 1,然后选择numberfield*-10 WHEN 0,然后选择numberfield*10 END作为myAlias FROM mytable

09-10 04:58
查看更多