怎么实现将score列的A、B、C按照3、2、1的值相加，求出score列的和来
我有一张表，里面大概数据如下：怎么实现将score列的A、B、C按照3、2、1的值相加，求出score列的和来 回复 1# lvluo1988
是说把 C 当 3 B 当 2 A 当 1 来做计算？ seesea2517 发表于 2016-05-26 10:44 static/image/common/back.gif
回复 1# lvluo1988
有点顺序反了。如果是的话，如下方案可以参考：mysql> select 68 - ascii('A'), 68 - ascii('B'), 68 - ascii('C');
+-----------------+-----------------+-----------------+
| 68 - ascii('A') | 68 - ascii('B') | 68 - ascii('C') |
+-----------------+-----------------+-----------------+
| 3 | 2 | 1 |
+-----------------+-----------------+-----------------+
1 row in set
mysql> set @score := 'A';
select if(@score = 'A', 3, if(@score = 'B', 2, if('@score = C', 1, -1)));
Query OK, 0 rows affected
+-------------------------------------------------------------------+
| if(@score = 'A', 3, if(@score = 'B', 2, if('@score = C', 1, -1))) |
+-------------------------------------------------------------------+
| 3 |
+-------------------------------------------------------------------+
1 row in set
mysql> set @score := 'A';
select
case @score
when 'A' then 3
when 'B' then 2
when 'C' then 1
else -1
end;
Query OK, 0 rows affected
+---------------------------------------------------------------------+
| case @score
when 'A' then 3
when 'B' then 2
when 'C' then 1
else -1
end |
+---------------------------------------------------------------------+
| 3 |
+---------------------------------------------------------------------+
1 row in set
mysql> 看晕我了。。。{:qq35:} {:qq35:} {:qq35:}
回复 4# lvluo1988
{:qq12:} 。。。直接用 case when 不就可以了
select sum(t.sc) from (select sc = case score when 'A' then 3 when 'B' then 2 when 'C' then 1 from table) t;
页:
[1]