java mysql 按日期分组查询

能力一般 发布于 2015/01/13 10:43
阅读 1K+
收藏 0
在       MySql         中创建user表
CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `age` int(11) NOT NULL,
  `createdate` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

添加数据

INSERT INTO `user` (`id`, `name`, `age`, `createdate`) VALUES
(1, 'test', 0, '2014-11-01'),
(2, 'tom', 22, '2014-11-02'),
(3, 'tom1', 22, '2014-11-03'),
(4, 'tom3', 22, '2014-11-01'),
(5, 'tom4', 22, '2014-11-03'),
(6, 'tom5', 22, '2014-11-28'),
(7, 'tom6', 22, '2014-11-28'),
(8, 'tom5', 22, '2014-11-28'),
(9, 'tom6', 22, '2014-11-05'),
(10, 'tom5', 22, '2014-11-01'),
(11, 'tom6', 22, '2014-11-08');

我要按createdate分组查询,sql如下

select count(*) as count,createdate from user group by createdate   asc

查询出来的结果是
count                createdate

3                        2014-11-28

1                        2014-11-08

1                        2014-11-05

2                       2014-11-03

3                        2014-11-02

3                        2014-11-01


但是我要显示成

count                createdate

0                        2014-11-30

0                        2014-11-29

3                        2014-11-28

0                        2014-11-27

.

.

.

1                        2014-11-08

1                        2014-11-05

2                       2014-11-03

3                        2014-11-02

3                        2014-11-01

我想要显示出这个样子,

请教各位,,sql怎么写

或者在java里面怎么补全这些日期









加载中
0
stefanzhlg
stefanzhlg

搞个日历表(每个月的日不一样)。然后left join。

Select Ddate,sum(ISNULL(Data1,0) as data1 
From (
Select T1.DDate,T2.Data1,0 from T1 left join T1 on t1.ddate=t2.ddate) 
AA group by AA.DData

能力一般
能力一般
select '2014-11-01' union select '2014-11-02' 这样的语句怎么写 请写完整或者写7天的
stefanzhlg
stefanzhlg
回复 @李大神 : 如果不要临时表,你只能select ‘2014-11-01’union select ‘2014-11-02’。。。。构造一个日历表 再去left join
能力一般
能力一般
在mysql中,,,两个表,就不会有这些问题 这是一张表的,,,, 求解
0
寂雨子
寂雨子

如果你非要这么执着  我只能告诉你一种很无奈的方法

使用过程  定义日期变量,循环查找

BEGIN
     declare time1 datetime default '2015-01-01';
     declare time2 datetime default '2015-01-02';
     declare i int default 1;
   WHILE i <31 DO
        select count(*) as count,time1 from club_order where createtime BETWEEN time1 and time2 ;
        set time1=DATE_ADD(time1,INTERVAL 1 DAY);
        set time2=DATE_ADD(time2,INTERVAL 1 DAY);
       SET i=i+1;
  END WHILE;
    
END

0
stefanzhlg
stefanzhlg

@李大神 :太长 上面回复不了 ,就在这儿写了:


SELECT Ddate,sum(case when ISNULL(createdate) then 0 else 1 end)

from(
select c.Ddate, d.createdate,0 from
(
select '2014-11-01' as Ddate UNION All
select '2014-11-02' UNION All
select '2014-11-03' UNION All
select '2014-11-04' UNION All
select '2014-11-05' UNION All
select '2014-11-06' UNION All
select '2014-11-07'
) as c LEFT JOIN user as d on c.Ddate = d.createdate
) AA  
GROUP BY AA.Ddate
返回顶部
顶部