time | count
------------------
2012-03-01,1
2012-03-02,1
2012-03-03,2
现在要查出3个列中,count列与sum(count)的比例sum(count)=4,所以最后结果要这样time | count
------------------
2012-03-01,0.25
2012-03-02,0.25
2012-03-03,0.50怎么查?求助高手啊!
select id,time,count/sum(count) from table;
这个返回来的结果只有一行
time | count
------------------
2012-03-01,0.25
select id,time,count/sum(count) from table group by time;
这个返回来的结果是
time | count
------------------
2012-03-01,1.0
2012-03-02,1.0
2012-03-03,1.0
实在想不出还有什么其他什么方法来处理了
------------------
2012-03-01,1
2012-03-02,1
2012-03-03,2
现在要查出3个列中,count列与sum(count)的比例sum(count)=4,所以最后结果要这样time | count
------------------
2012-03-01,0.25
2012-03-02,0.25
2012-03-03,0.50怎么查?求助高手啊!
select id,time,count/sum(count) from table;
这个返回来的结果只有一行
time | count
------------------
2012-03-01,0.25
select id,time,count/sum(count) from table group by time;
这个返回来的结果是
time | count
------------------
2012-03-01,1.0
2012-03-02,1.0
2012-03-03,1.0
实在想不出还有什么其他什么方法来处理了
解决方案 »
- update 语句
- 怎么把MYSQL的MY.INI的路径设置成相对的路径
- MYSQL以表行数作为表分区范围条件
- 急!!一个简单修改语句
- MySQL 怎么剔除重复值,求助大侠门(在线急求)
- 刚学mysql,help! mysqldump问题!
- 初学MYSQL,请问如果通过SQL语句,将MYSQL的数据个改名,谢谢!
- 跪求 :MYSQL权威指南(原书第2版)
- postgresql 客户主机ip设定的问题。给高分。
- Warning Code : 1265 Data truncated for column 'm_condition' at row 1
- 一个关于KEY的疑问
- QT+MySQL SELECT得不到结果
from table a ,(select sum(count) as total from table) b ;
select @total := sum(count) from a;
select a.time, a.count / @total
from a;
嗯,试过了,版主的居然显示没有子查询,不过explain结果均有两个查询,影响行数都为 3。从结果我还是没看出有没有优化。附:
[code]
mysql> use testtest;
Database changed
mysql> explain
-> select a.time, a.num/b.total
-> from a ,(select sum(num) as total from a) b ;
+----+-------------+------------+--------+---------------+------+---------+-----
-+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+-----
-+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL
| 1 | |
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL
| 3 | |
| 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL
| 3 | |
+----+-------------+------------+--------+---------------+------+---------+-----
-+------+-------+
3 rows in set (0.00 sec)mysql> explain
-> SELECT time , num / (SELECT SUM(num) FROM a)
-> FROM a;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 3
| |
| 2 | SUBQUERY | a | ALL | NULL | NULL | NULL | NULL | 3
| |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
2 rows in set (0.00 sec)mysql>
[/code]
Database changed
mysql> explain
-> select a.time, a.num/b.total
-> from a ,(select sum(num) as total from a) b ;
+----+-------------+------------+--------+---------------+------+---------+-----
-+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+-----
-+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL
| 1 | |
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL
| 3 | |
| 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL
| 3 | |
+----+-------------+------------+--------+---------------+------+---------+-----
-+------+-------+
3 rows in set (0.00 sec)mysql> explain
-> SELECT time , num / (SELECT SUM(num) FROM a)
-> FROM a;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 3
| |
| 2 | SUBQUERY | a | ALL | NULL | NULL | NULL | NULL | 3
| |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
2 rows in set (0.00 sec)mysql>