56,687
社区成员
发帖
与我相关
我的任务
分享
select b.name,count(a.id) as 'cid' from type1data a right join login b on b.id=a.userid
where a.date between '2011-08-01' and '2011-08-31'
and a.userid in(select id from login where dept='work' and deptid=1) group by b.name order by 2 desc
mysql> select b.name,count(a.id) from type1data a right join login b on b.id=a.userid group by b.name order by 2 desc;
+----------------+-------------+
| name | count(a.id) |
+----------------+-------------+
| 玉凯一大队 | 3 |
| 直属支队宣传科 | 0 |
| 玉凯二大队 | 0 |
| 贵遵一大队 | 0 |
| admin | 0 |
+----------------+-------------+
5 rows in set (0.00 sec)
DROP TABLE IF EXISTS `login`;
CREATE TABLE `login` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`pwd` varchar(20) DEFAULT NULL,
`rule` varchar(10) DEFAULT NULL,
`lanmus` varchar(50) DEFAULT NULL,
`deptid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=gbk;
INSERT INTO login VALUES ('1', '直属支队宣传科', '123', 'master', '1,2,4,5,6,7,8,9,10,14,16,17,18', '1');
INSERT INTO login VALUES ('2', '玉凯一大队', '123', 'work', '1,2,4,5,6,7,8,9,10,13,15,', '1');
INSERT INTO login VALUES ('3', '玉凯二大队', '123', 'work', '1,2,4,5,6,7,8,9,10,13,15,', '1');
INSERT INTO login VALUES ('4', 'admin', 'admin', 'master', '1,2,4,5,6,7,8,9,10,13,12,14,15,16,17,18,19,', '4');
INSERT INTO login VALUES ('7', '贵遵一大队', '123', 'work', '1,2,4,5,6,7,8,9,10,13,15,', '1');
DROP TABLE IF EXISTS `type1data`;
CREATE TABLE `type1data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL DEFAULT '0',
`date` varchar(20) NOT NULL DEFAULT '0000-00-00 00:00:00',
`mediaName` varchar(40) DEFAULT NULL,
`mediaGuige` varchar(20) DEFAULT NULL,
`mediaType` varchar(20) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`ticai` varchar(30) DEFAULT NULL COMMENT '电视报纸广播网络',
`banmian` varchar(50) DEFAULT NULL,
`author` varchar(50) DEFAULT NULL,
`imgstr` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=gbk;
INSERT INTO type1data VALUES ('4', '2', '2011-08-03', 'bbs', '省级二类', '广播', 'testdata', 'tab', '111', 'easy', null);
INSERT INTO type1data VALUES ('2', '2', '2011-08-03', '贵阳晚报', '地市级', '报纸', '测试数据', '新闻1', 'A4', '测试', null);
INSERT INTO type1data VALUES ('3', '2', '2011-08-03', '测试', '中央二类', '报纸', '测试1', '测试2', '测试', '测试2', null);
select l.name,count(t.id) as 'cid'
from login l left join type1data t
on l.id=t.userid
where t.date between '2011-08-01' and '2011-08-31' and t.userid in(select id from login where rule='work' and deptid=1)
group by l.name
order by 'cid'