数据表
name brand_id goods_num
回力 1 2
耐克 2 3目标结果集是
name brand_id goods_num_rate
回力 1 40%
耐克 2 60%goods_num总共有5个,回力有2个那么 占比例为20%,同理,耐克3个占60%
name brand_id goods_num
回力 1 2
耐克 2 3目标结果集是
name brand_id goods_num_rate
回力 1 40%
耐克 2 60%goods_num总共有5个,回力有2个那么 占比例为20%,同理,耐克3个占60%
from tb
concat( (goods_num/(select sum(goods_num) from tb))*100,'%') as goods_num_rate
from tt
concat( (goods_num/aa)*100,'%') as goods_num_ratefrom tt,(select sum(goods_num) as aa from tb) a
表 a 是源表,表b是目标结果集,我想要的结果
3楼想法是对的,可惜语法在mysql不能通过
mysql> SELECT * FROM TTP3;
+--------+----------+-----------+
| name | brand_id | goods_num |
+--------+----------+-----------+
| 鍥炲姏 | 1 | 2 |
| 鑰愬厠 | 2 | 3 |
+--------+----------+-----------+
2 rows in set (0.00 sec)mysql> SELECT NAME,brand_id,
-> CONCAT( ROUND(ROUND(goods_num/aa,2)*100,0),'%') AS goods_num_rate FROM tt
p3,(SELECT SUM(goods_num) AS aa FROM ttp3) a;
+--------+----------+----------------+
| NAME | brand_id | goods_num_rate |
+--------+----------+----------------+
| 鍥炲姏 | 1 | 40% |
| 鑰愬厠 | 2 | 60% |
+--------+----------+----------------+
2 rows in set (0.00 sec)mysql>
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM TTP3;
+------+----------+-----------+
| name | brand_id | goods_num |
+------+----------+-----------+
| 回力 | 1 | 2 |
| 耐克 | 2 | 3 |
+------+----------+-----------+
2 rows in set (0.01 sec)mysql> SELECT NAME,brand_id,
-> CONCAT( ROUND(ROUND(goods_num/aa,2)*100,0),'%') AS goods_num_rate FROM tt
p3,(SELECT SUM(goods_num) AS aa FROM ttp3) a;
+------+----------+----------------+
| NAME | brand_id | goods_num_rate |
+------+----------+----------------+
| 回力 | 1 | 40% |
| 耐克 | 2 | 60% |
+------+----------+----------------+
2 rows in set (0.00 sec)mysql>
发生了can not reopen table a
我改物理表试试
SELECT NAME,brand_id,CONCAT( ROUND(ROUND(goods_num/@aa,2)*100,0),'%') AS goods_num_rate FROM ttp3
SET @aa=(SELECT SUM(goods_num) FROM ttp3);
SELECT NAME,brand_id,
CONCAT( ROUND(ROUND(goods_num/@aa,2)*100,0),'%') AS goods_num_rate FROM ttp3