2表 分组统计

[银熊]

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

[dzxccsu]

看了下，按楼主的意思。

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

[程序猿之殇]

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

[银熊]

@dzxccsu 您的是对的 不好意思 我弄错了 @foolbirdflyfirst 应该是dzxccsu 那条结果 正确结果

date         A          B,C
2007-05-28   2          2 
2007-05-29   1          0 
2007-05-30   0          0

[lzl8146]

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)

[程序猿之殇]

效率不是很高,不过总算是完成了.呵呵.

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

[程序猿之殇]

效率不是很高,不过总算是完成了.呵呵.

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

[dzxccsu]

[Quote=引用 5 楼 jakey9826 的回复:]
PHP codeSELECT tb1.acountASA,ifnull( tb2.acount,0)AS'B,C',tb1.dateFROM (

SELECTcount( DISTINCT (
table3.id
) )ASacount,TYPE,DATE_FORMAT( `date`,'%Y-%m-%d')ASdateFROM `table3`,`table4`
WHERE `type`='A'AND table3.id=table4.tbid
GROUP BY DATE_FORMAT( `date`,'%Y-%m-%d')
)tb1
LEFTJOIN(

SELECTcount( DISTINCT (
table3.id
) )ASacount,TYPE,DATE_FORMAT( `date`,'%Y-%m-%d')ASdateFROM `table3`,table4
WHE…
[/Quote]
要的上面结果的话，自由火大哥已经给出答案了

[lzl8146]

刚才排版有问题，我再贴

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)

[foolbirdflyfirst]

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可以改一下

[银熊]

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

[银熊]

@lzl8146 你的没有出来结果

[xuzuning]

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)

[银熊]

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

[lzl8146]

[Quote=引用 14 楼 kissweb 的回复:]
@lzl8146 你的没有出来结果
[/Quote]

我这里可以啊。。是不是你漏了什么

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;

[zmouki]

[Quote=引用 11 楼 lzl8146 的回复:]
刚才排版有问题，我再贴

SQL code
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;
+------------+---+-----+…
[/Quote]
我这里也没问题~！

[银熊]

[Quote=引用 17 楼 lzl8146 的回复:]
引用 14 楼 kissweb 的回复:
@lzl8146 你的没有出来结果


我这里可以啊。。是不是你漏了什么


SQL code
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 …
[/Quote]
用命令行可以 用phpmyadmin不知道为啥就没出结果

[程序猿之殇]

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

[银熊]

[Quote=引用 20 楼 jakey9826 的回复:]
date,字段名用的是mysql的保留字,有可能是这的问题,最好写成`date` `type`
[/Quote]
@ 自由火
测试了下，不是这个问题

[lzl8146]

[Quote=引用 19 楼 kissweb 的回复:]
引用 17 楼 lzl8146 的回复:
引用 14 楼 kissweb 的回复:
@lzl8146 你的没有出来结果


我这里可以啊。。是不是你漏了什么


SQL code
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 d…
[/Quote]

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

[银熊]

@lzl8146 也许是这个版本问题