2表 分组统计

Question

CREATE TABLE `table3` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(2) default NULL,
  `date` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=gbk;INSERT INTO `table3` (`id`, `type`, `date`) VALUES 
(1, 'A', '2007-05-28 12:17:59'),
(2, 'A', '2007-05-28 13:17:59'),
(3, 'B', '2007-05-28 13:23:59'),
(4, 'B', '2007-05-28 14:25:59'),
(5, 'C', '2007-05-28 15:25:20'),
(6, 'C', '2007-05-29 22:25:20'),
(7, 'B', '2007-05-29 23:26:20'),
(8, 'A', '2007-05-29 09:16:46'),
(9, 'B', '2007-05-30 09:16:46');CREATE TABLE `table4` (
  `id` int(11) NOT NULL auto_increment,
  `tbid` int(11) NOT NULL default '0',
  `name` varchar(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=gbk;INSERT INTO `table4` (`id`, `tbid`, `name`) VALUES 
(1, 1, 'fg'),
(2, 2, 'fh'),
(3, 3, 'hj'),
(4, 3, 'jk'),
(5, 5, 'lk'),
(6, 5, 'io'),
(7, 5, 'op'),
(8, 8, 'oo'),
(9, 8, 'pp');说明：table3 中的id关联到table4 中的tbid
要求：按照 table3 每天日期，然后分组统计，按A分为一组， B,C分为一组分别进行统计,并且要存在于table4中
搜索结果如下：
 date           A          B,C
 2007-05-28      2          2
 2007-05-29      1          1
 2009-05-30      0          0

Answer 1

要求：按照 table3 每天日期，然后分组统计，按A分为一组， B,C分为一组分别进行统计,并且要存在于table4中
=================================================================================================
在结果中
2009-05-30那条date对应的是table3 id为9的记录，但是此id值，并未出现在table4的tbid字段中，如何解释你的要求？

Answer 2

@foolbirdflyfirst   
2009-05-30那条date对应的是table3 id为9的记录，但是此id值，并未出现在table4的tbid字段中，如何解释你的要求？ 
没有出现在table4中的tbid字段中， 就计算为0 看搜索结果 
2009-05-30      0          0
      

Answer 3

还有一点没看清楚
table4对应的table3 id有1,2,3,5,8
3对应的记录是B,日期为2007-05-28
5对应的记录是C，日期也是2007-05-28
其余1,2,8均为A的记录，那么何来的date            A          B,C
2007-05-29      1          1这条记录呢？

Answer 4

看了下，按楼主的意思。date         A          B,C
2007-05-28   2          2 
2007-05-29   1          1 //这里的B，C组应该是0吧，因为table3中的B，C id（6，7）z在table4的tbid中并不存在啊
2007-05-30   0          0

Answer 5

SELECT tb1.acount AS A, ifnull( tb2.acount, 0 ) AS 'B,C', tb1.date
FROM (SELECT count( DISTINCT (
table3.id
) ) AS acount, 
TYPE , DATE_FORMAT( `date` , '%Y-%m-%d' ) AS date
FROM `table3` , `table4` 
WHERE `type` = 'A'
AND table3.id = table4.tbid
GROUP BY DATE_FORMAT( `date` , '%Y-%m-%d' ) 
)tb1
LEFT JOIN (SELECT count( DISTINCT (
table3.id
) ) AS acount, 
TYPE , DATE_FORMAT( `date` , '%Y-%m-%d' ) AS date
FROM `table3` , table4
WHERE `type` 
IN (
'B', 'C'
)
AND table3.id = table4.tbid
GROUP BY DATE_FORMAT( `date` , '%Y-%m-%d' ) 
)tb2 ON tb1.date = tb2.date

Answer 6

@dzxccsu  您的是对的 不好意思 我弄错了 @foolbirdflyfirst 应该是dzxccsu 那条结果      正确结果
date         A          B,C
2007-05-28   2          2 
2007-05-29   1          0 
2007-05-30   0          0

Answer 7

mysql> select b.jdate,IFNULL(a.A,0) as A,ifnull(a.BC,0) as `B'C` from (select le
ft(date,10) as idate,sum(if(type='A',1,0)) as A,sum(if(type='B' or type='C',1,0)
) as BC from table3 where id in (select distinct tbid from table4) group by left
(date,10)) a right join (select distinct left(date,10) as jdate from table3) b o
n a.idate=b.jdate;
+------------+---+-----+
| jdate      | A | B'C |
+------------+---+-----+
| 2007-05-28 | 2 |   2 |
| 2007-05-29 | 1 |   0 |
| 2007-05-30 | 0 |   0 |
+------------+---+-----+
3 rows in set (0.00 sec)

Answer 8

效率不是很高,不过总算是完成了.呵呵.SELECT ifnull( tb1.acount, 0 ) AS A, ifnull( tb2.acount, 0 ) AS 'B,C', tbd.date
FROM (SELECT DISTINCT (
DATE_FORMAT( DATE, '%Y-%m-%d' ) 
) AS date
FROM table3
)tbd
LEFT JOIN (SELECT count( DISTINCT (
table3.id
) ) AS acount, DATE_FORMAT( `date` , '%Y-%m-%d' ) AS date
FROM `table3` , `table4` 
WHERE `type` = 'A'
AND table3.id = table4.tbid
GROUP BY DATE_FORMAT( `date` , '%Y-%m-%d' ) 
)tb1 ON tbd.date = tb1.date
LEFT JOIN (SELECT count( DISTINCT (
table3.id
) ) AS acount, 
TYPE , DATE_FORMAT( `date` , '%Y-%m-%d' ) AS date
FROM `table3` , table4
WHERE `type` 
IN (
'B', 'C'
)
AND table3.id = table4.tbid
GROUP BY DATE_FORMAT( `date` , '%Y-%m-%d' ) 
)tb2 ON tbd.date = tb2.date

Answer 9

效率不是很高,不过总算是完成了.呵呵.SELECT ifnull( tb1.acount, 0 ) AS A, ifnull( tb2.acount, 0 ) AS 'B,C', tbd.date
FROM (SELECT DISTINCT (
DATE_FORMAT( DATE, '%Y-%m-%d' ) 
) AS date
FROM table3
)tbd
LEFT JOIN (SELECT count( DISTINCT (
table3.id
) ) AS acount, DATE_FORMAT( `date` , '%Y-%m-%d' ) AS date
FROM `table3` , `table4` 
WHERE `type` = 'A'
AND table3.id = table4.tbid
GROUP BY DATE_FORMAT( `date` , '%Y-%m-%d' ) 
)tb1 ON tbd.date = tb1.date
LEFT JOIN (SELECT count( DISTINCT (
table3.id
) ) AS acount, 
TYPE , DATE_FORMAT( `date` , '%Y-%m-%d' ) AS date
FROM `table3` , table4
WHERE `type` 
IN (
'B', 'C'
)
AND table3.id = table4.tbid
GROUP BY DATE_FORMAT( `date` , '%Y-%m-%d' ) 
)tb2 ON tbd.date = tb2.date

Answer 10

刚才排版有问题，我再贴mysql> select b.jdate,IFNULL(a.A,0) as A,ifnull(a.BC,0) as BC from (select left(date,10) as idate,sum(if(type='A',1,0)) as A,sum(if(type='B' or type='C',1,0)) as BC from table3 where id in (select distinct tbid from table4) group by left(date,10)) a right join (select distinct left(date,10) as jdate from table3) b on a.idate=b.jdate;
+------------+---+-----+
| jdate      | A | BC |
+------------+---+-----+
| 2007-05-28 | 2 |   2 |
| 2007-05-29 | 1 |   0 |
| 2007-05-30 | 0 |   0 |
+------------+---+-----+
3 rows in set (0.00 sec)

Answer 11

SELECT substring( t3.`date` , 1, 10 ) AS `date` , sum( t3.`type` = 'A' ) AS A, sum( t3.`type` = 'B' ) + sum( t3.`type` = 'C' ) AS BnC
FROM table3 t3 where t3.id in(
select distinct(tbid) from table4
)
GROUP BY substring( t3.`date` , 1, 10 )
没有记录的将不出现在列表中，lz可以改一下

Answer 12

谢谢 jakey9826 dzxccsu lzl8146 foolbirdflyfirst 谢谢大家热心帮忙 我测试下先

Answer 13

@lzl8146 你的没有出来结果

Answer 14

select year(date) as `date`, sum(if(type='A',1,0)) as A, sum(if(type='B' or type='C',1,0)) as BC from table3, table4 where table.id=table4.tbid group by year(date)

Answer 15

@xuzuning  查询结果不是我想要的哦

Answer 16

我这里可以啊。。是不是你漏了什么
select b.jdate,IFNULL(a.A,0) as A,ifnull(a.BC,0) as BC from (select left(date,10) as idate,sum(if(type='A',1,0)) as A,sum(if(type='B' or type='C',1,0)) as BC from table3 where id in (select distinct tbid from table4) group by left(date,10)) a right join (select distinct left(date,10) as jdate from table3) b on a.idate=b.jdate;

Answer 17

用命令行可以 用phpmyadmin不知道为啥就没出结果

Answer 18

date,字段名用的是mysql的保留字,有可能是这的问题,最好写成`date` `type`

Answer 19

是phpmyadmin自身有问题，我刚下了phpMyAdmin-3.1.5-english.zip测试没有问题哦~

Answer 20

@lzl8146  也许是这个版本问题