首页>Program>source

我正在一个网站上工作,该网站要求我显示每天用户输入的平均数量的图表.我已经有一个SQL查询,可以向我返回此信息:

SELECT sum(number)/count(number) as average, date FROM stats WHERE * GROUP BY date

这给了我想要的结果,但是结果以三位小数精度给出.我想四舍五入这个数字.我当然可以用PHP或模板引擎来做,但是我很好奇数据库中是否有办法做到这一点。

有没有一种方法可以将输出转换为整数(在MySQL中)?

最新回答
  • 2天前
    1 #

    SELECT 
      CAST(sum(number)/count(number) as UNSIGNED) as average, 
      date 
    FROM stats 
    WHERE * 
    GROUP BY date
    

  • 2天前
    2 #

    CAST的有效类型 在MySQL中如下

      BINARY[(N)]

      CHAR[(N)]

      DATE

      DATETIME

      DECIMAL[(M[,D])]

      SIGNED [INTEGER]

      TIME

      UNSIGNED [INTEGER]

      因此您可以使用

      SELECT CAST(sum(number)/count(number) AS UNSIGNED) as average...
      

      SIGNED 如果是 SUM 部分最多可以加一个负数。

  • 2天前
    3 #

    如何使用MySQL FORMAT 功能吗?

    mysql> SELECT FORMAT(12345.123456, 4);
    +-------------------------+
    | FORMAT(12345.123456, 4) |
    +-------------------------+
    | 12,345.1235             |
    +-------------------------+
    1 row in set (0.00 sec)
    mysql> SELECT FORMAT(12345.123456, 0);
    +-------------------------+
    | FORMAT(12345.123456, 0) |
    +-------------------------+
    | 12,345                  |
    +-------------------------+
    1 row in set (0.00 sec)
    

  • 2天前
    4 #

    使用 DIV 运算符。

    mysql> SELECT 5 DIV 2;
        -> 2
    

    Integer division. Similar to FLOOR(), but is safe with BIGINT values. Incorrect results may occur for noninteger operands that exceed BIGINT range.

    pre
    SELECT convert(int, sum(number)/count(number)) as average,
      date
    FROM stats
    WHERE * GROUP BY date
    

    SELECT 
      CAST(sum(number)/count(number) as INT) as average, 
      date 
    FROM stats 
    WHERE * 
    GROUP BY date
    

  • 找不到android ContextCompatcheckSelfPermission()
  • 了解Haskell类型签名