21,886
社区成员
发帖
与我相关
我的任务
分享
Select b.author,count(a.id) as cc From `content` as a INNER JOIN `writer` as b on a.id=b.arcid group by b.author order by count(a.id) desc
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 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 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;
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) 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;
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 |
+--------+-----------+
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.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;