mysql> select * from writer; +-------+--------+ | arcid | author | +-------+--------+ | 1 | 张三 | | 1 | 李四 | | 2 | 张三 | | 3 | 王五 | | 4 | 李四 | | 4 | 王五 | +-------+--------+ 6 rows in set (0.00 sec)mysql> select w.author,sum(t.ct) -> from writer w left join -> (select arcid,1/count(*) ct -> from writer -> group by arcid) t -> on w.arcid=t.arcid -> group by w.author; +--------+-----------+ | author | sum(t.ct) | +--------+-----------+ | 张三 | 1.5000 | | 李四 | 1.0000 | | 王五 | 1.5000 | +--------+-----------+ 3 rows in set (0.02 sec)
-- 保留小数点后一位select w.author,sum(t.ct) from writer w left join (select arcid,format(1/count(*),1) ct from writer group by arcid) t on w.arcid=t.arcid group by w.author;
select a.author,ROUND(sum(1/b.cnt),1) from writer a inner join ( select arcid,count(*) as cnt from writer w inner join content c on w.arcid=c.id where c.islock=1 ) b on a.arcid=b.arcid group by a.author
我完整的语句应该如下: Select b.author,count(a.id) as cc From `content` as a INNER JOIN `writer` as b on a.id=b.arcid where a.typeid in (21,28,29,30,31,32) and a.islock=1 group by b.author order by count(a.id) desc
select w.author,sum(t.ct) from writer w left join (select t1.arcid,format(1/count(*),1) ct from writer t1,content t2 where t1.arcid=t2.id and t2.typid in(21,28,29,30,31,32) and t2.islock=1 group by arcid) t on w.arcid=t.arcid group by w.author;
CREATE TABLE `cms`.`content` ( `id` INT( 6 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `title` VARCHAR( 50 ) NOT NULL , `islock` TINYINT( 1 ) NOT NULL ) ENGINE = InnoDB;CREATE TABLE `cms`.`writer` ( `arcid` INT( 6 ) NOT NULL , `author` VARCHAR( 50 ) NOT NULL ) ENGINE = InnoDB;INSERT INTO `cms`.`content` ( `id` , `title` , `islock` ) VALUES ( NULL , 'aaaaaa', '1' ), ( NULL , 'bbbbbb', '1' ), ( NULL , 'cccccc', '1' ), ( NULL , 'dddddd', '1' );INSERT INTO `cms_content`.`writer` ( `arcid` , `author` ) VALUES ( '1', '张三' ), ( '1', '李四' ), ( '2', '张三' ), ( '3', '王五' ), ( '4', '李四' ), ( '4', '王五' ); 我的查询语句如下: SELECT b.author, count( a.id ) AS cc FROM `content` AS a INNER JOIN `writer` AS b ON a.id = b.arcid WHERE a.islock =1 GROUP BY b.author ORDER BY count( a.id ) DESC LIMIT 0 , 30得到结果如下: author cc 张三 2 李四 2 王五 1而我希望得到的统计结果,应为: author cc 张三 1.5 李四 1.5 王五 1 即,精确统计到保留一位小数就行了如遇整除不尽的,不用四舍五入。 要按我的语句,反而文章多出来了 望高手指教,谢谢
select w.author,sum(t.ct) from writer w left join (select t1.arcid,format(1/count(*),1) ct from writer t1,content t2 where t1.arcid=t2.id and t2.islock=1 group by arcid) t on w.arcid=t.arcid group by w.author; -- 正确结果应该是:+--------+-----------+ | author | sum(t.ct) | +--------+-----------+ | 张三 | 1.5 | | 李四 | 1 | | 王五 | 1.5 | +--------+-----------+
感谢zhoupuyue 问题已经解决
select w.author,sum(t.ct) sumnum from writer w left join (select t1.arcid,format(1/count(*),1) ct from writer t1,content t2 where t1.arcid=t2.id and t2.islock=1 group by arcid) t on w.arcid=t.arcid group by w.author order by sumnum desc limit 30;
mysql> select * from writer;
+-------+--------+
| arcid | author |
+-------+--------+
| 1 | 张三 |
| 1 | 李四 |
| 2 | 张三 |
| 3 | 王五 |
| 4 | 李四 |
| 4 | 王五 |
+-------+--------+
6 rows in set (0.00 sec)mysql> select w.author,sum(t.ct)
-> from writer w left join
-> (select arcid,1/count(*) ct
-> from writer
-> group by arcid) t
-> on w.arcid=t.arcid
-> group by w.author;
+--------+-----------+
| author | sum(t.ct) |
+--------+-----------+
| 张三 | 1.5000 |
| 李四 | 1.0000 |
| 王五 | 1.5000 |
+--------+-----------+
3 rows in set (0.02 sec)
-- 保留小数点后一位select w.author,sum(t.ct)
from writer w left join
(select arcid,format(1/count(*),1) ct
from writer
group by arcid) t
on w.arcid=t.arcid
group by w.author;
from writer a inner join (
select arcid,count(*) as cnt
from writer w inner join content c on w.arcid=c.id
where c.islock=1
) b on a.arcid=b.arcid
group by a.author
参考一下这个贴子的提问方式http://forum.csdn.net/BList/OtherDatabase
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试。
Select b.author,count(a.id) as cc From `content` as a INNER JOIN `writer` as b on a.id=b.arcid where a.typeid in (21,28,29,30,31,32) and a.islock=1 group by b.author order by count(a.id) desc
参考一下这个贴子的提问方式http://forum.csdn.net/BList/OtherDatabase
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试。
from writer w left join
(select t1.arcid,format(1/count(*),1) ct
from writer t1,content t2
where t1.arcid=t2.id and t2.typid in(21,28,29,30,31,32) and t2.islock=1
group by arcid) t
on w.arcid=t.arcid
group by w.author;
`id` INT( 6 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 50 ) NOT NULL ,
`islock` TINYINT( 1 ) NOT NULL
) ENGINE = InnoDB;CREATE TABLE `cms`.`writer` (
`arcid` INT( 6 ) NOT NULL ,
`author` VARCHAR( 50 ) NOT NULL
) ENGINE = InnoDB;INSERT INTO `cms`.`content` (
`id` ,
`title` ,
`islock`
)
VALUES (
NULL , 'aaaaaa', '1'
), (
NULL , 'bbbbbb', '1'
), (
NULL , 'cccccc', '1'
), (
NULL , 'dddddd', '1'
);INSERT INTO `cms_content`.`writer` (
`arcid` ,
`author`
)
VALUES (
'1', '张三'
), (
'1', '李四'
), (
'2', '张三'
), (
'3', '王五'
), (
'4', '李四'
), (
'4', '王五'
);
我的查询语句如下:
SELECT b.author, count( a.id ) AS cc
FROM `content` AS a
INNER JOIN `writer` AS b ON a.id = b.arcid
WHERE a.islock =1
GROUP BY b.author
ORDER BY count( a.id ) DESC
LIMIT 0 , 30得到结果如下:
author cc
张三 2
李四 2
王五 1而我希望得到的统计结果,应为:
author cc
张三 1.5
李四 1.5
王五 1
即,精确统计到保留一位小数就行了如遇整除不尽的,不用四舍五入。
要按我的语句,反而文章多出来了 望高手指教,谢谢
select w.author,sum(t.ct)
from writer w left join
(select t1.arcid,format(1/count(*),1) ct
from writer t1,content t2
where t1.arcid=t2.id and t2.islock=1
group by arcid) t
on w.arcid=t.arcid
group by w.author;
-- 正确结果应该是:+--------+-----------+
| author | sum(t.ct) |
+--------+-----------+
| 张三 | 1.5 |
| 李四 | 1 |
| 王五 | 1.5 |
+--------+-----------+
from writer w left join
(select t1.arcid,format(1/count(*),1) ct
from writer t1,content t2
where t1.arcid=t2.id and t2.islock=1
group by arcid) t
on w.arcid=t.arcid
group by w.author
order by sumnum desc
limit 30;