通过从mysql中的字符串中删除第一个字符来查找sum

问题描述：

input_table

username  input
A         A10
B         A9
C         A8
D         A7
E         A0
F         A4

like so on

How i can find sum of input by removing 'A' char from 'input' field

input_table p>

 用户名输入
A A10 
B A9 \  nC A8 
D A7 
E A0 
F A4 
  code>  pre> 
 
 如此 p> 
 
 
如何找到输入的总和 从'input'字段中删除'A'char  p> 
  div>

答

Try the below code

select SUM(SUBSTRING(input, 2)) from input_table

答

Try this, may work;)

SQL Fiddle

MySQL 5.6 Schema:

CREATE TABLE input_table
    (`username` varchar(1), `input` varchar(3))
;

INSERT INTO input_table
    (`username`, `input`)
VALUES
    ('A', 'A10'),
    ('B', 'A9'),
    ('C', 'A8'),
    ('D', 'A7'),
    ('E', 'A0'),
    ('F', 'A4')
;

Query 1:

select sum(replace(input, 'A', '')) from input_table

Results:

| sum(replace(input, 'A', '')) |
|------------------------------|
|                           38 |

答

Use RIGHT function to get the number in the right side and CAST it and then find the SUM.

Query

SELECT SUM(CAST(RIGHT(input, LENGTH(input) - 1) AS UNSIGNED)) AS `SUM`
FROM input_table;

答

Try this also:

SELECT SUM(CAST(TRIM(LEADING 'A' FROM input)) AS UNSIGNED) AS total FROM table;

通过从mysql中的字符串中删除第一个字符来查找sum

相关推荐