原来的问题已经解决
地址http://topic.csdn.net/u/20110825/22/54dccabf-88a7-4590-a779-3bd699c0c514.html
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);DROP TABLE IF EXISTS `type2data`;
CREATE TABLE `type2data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT NULL,
`date` varchar(10) DEFAULT NULL,
`zfbm` varchar(30) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`pathstr` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=gbk;
INSERT INTO type2data VALUES ('1', '2', '2011-08-09', '支队', 'test', 'stest');
INSERT INTO type2data VALUES ('3', '2', '2011-08-17', '支队', 'aaaa', 'aaaa');要求结果能显示发布数为0的用户
玉凯一大队 3
玉凯二大队 1
贵遵一大队 0但是 这个count出来的数字,要是两张表的相加之和查一张表的sql代码如下SELECT a.name,COUNT(b.id) FROM login a
LEFT JOIN (SELECT * FROM type1data WHERE DATE BETWEEN '2011-08-01' AND '2011-08-31') b ON a.id=b.userid
WHERE a.dept='work' and a.deptid=1 GROUP BY a.name order by 2 desc
地址http://topic.csdn.net/u/20110825/22/54dccabf-88a7-4590-a779-3bd699c0c514.html
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);DROP TABLE IF EXISTS `type2data`;
CREATE TABLE `type2data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT NULL,
`date` varchar(10) DEFAULT NULL,
`zfbm` varchar(30) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`pathstr` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=gbk;
INSERT INTO type2data VALUES ('1', '2', '2011-08-09', '支队', 'test', 'stest');
INSERT INTO type2data VALUES ('3', '2', '2011-08-17', '支队', 'aaaa', 'aaaa');要求结果能显示发布数为0的用户
玉凯一大队 3
玉凯二大队 1
贵遵一大队 0但是 这个count出来的数字,要是两张表的相加之和查一张表的sql代码如下SELECT a.name,COUNT(b.id) FROM login a
LEFT JOIN (SELECT * FROM type1data WHERE DATE BETWEEN '2011-08-01' AND '2011-08-31') b ON a.id=b.userid
WHERE a.dept='work' and a.deptid=1 GROUP BY a.name order by 2 desc
、、
没明白什么意思
比如我要的结果是这样:
玉凯一大队 3
玉凯二大队 1
贵遵一大队 0可这个数据实际上来自两个表
玉凯一大队 2
玉凯二大队 0
贵遵一大队 0
和
玉凯一大队 1
玉凯二大队 1
贵遵一大队 0我只不过想通过一个sql查两个表count出来的和
SELECT a.`id`,a.`userid`,a.`date` FROM type2data a
UNION ALL
SELECT a.`id`,a.`userid`,a.`date` FROM type1data a WHERE DATE BETWEEN '2011-08-01' AND '2011-08-31') b
ON a.`id`=b.userid
WHERE a.rule='work' AND a.deptid=1 GROUP BY a.name order by 2 desc
;