如何获取一年里每个月的记录总数,要求不管该月有没有数据都要显示,没有的显示为0?
同样
怎么列出一个月里每天的数据,没有的显示为零。
同样
怎么列出一个月里每天的数据,没有的显示为零。
解决方案 »
- INNODB数据保存在那个文件?备份data下的数据库目录不行啊?
- MySQL Query Cache
- 查询按类别分组后每组的最新20条数据
- The total number of locks exceeds the lock table size
- 同一个数据库不同的两个网站的同步登陆问题?
- 请教简单的mysql存储过程问题
- MySql相对SqlServer执行效率低的问题
- 为什么我在Mysql 中插入一条数据,然后紧接着select刚插入的记录的ID号,怎么select 出来是一个空值???
- 什么地方可以下载MYSQL在WINDOWS下的源代码?
- 自定义函数问题
- 下载MyODBC后,配置数据源出现这样的错误,请大家看看
- mysql数据库查询方面的难题,请各位帮忙
`dId` int(6) unsigned NOT NULL auto_increment,
`time` date default NULL,
`note` varchar(100) default NULL,
`isDel` int(2) NOT NULL default '0',
`flag` char(2) default '0'
`IP` varchar(30) default NULL,
PRIMARY KEY (`dId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO #Temp
SELECT '2006-01-02'
UNION ALL
SELECT '2007-01-02'
UNION ALL
SELECT '2007-01-02'
UNION ALL
SELECT '2007-02-02'
UNION ALL
SELECT '2007-03-02'
UNION ALL
SELECT '2007-04-02'SELECT ymd.ymd,ISNULL(t.cnt,0) cnt
FROM
(
SELECT TOP 100 PERCENT CONVERT(datetime,CONVERT(varchar(4),Y.Y)+'-'+CONVERT(varchar(2),M.M)+'-01') ymd
FROM
(
SELECT DISTINCT YEAR(ymd) Y
FROM #Temp
) Y
CROSS JOIN
(
SELECT 1 AS M
UNION
SELECT 2 AS M
UNION
SELECT 3 AS M
UNION
SELECT 4 AS M
UNION
SELECT 5 AS M
UNION
SELECT 6 AS M
UNION
SELECT 7 AS M
UNION
SELECT 8 AS M
UNION
SELECT 9 AS M
UNION
SELECT 10 AS M
UNION
SELECT 11 AS M
UNION
SELECT 12 AS M
) M
ORDER BY 1
) ymd
LEFT JOIN
(
SELECT CONVERT(varchar(7),ymd,120)+'-01' ymd,COUNT(1) cnt
FROM #Temp
GROUP BY CONVERT(varchar(7),ymd,120)
) t
ON ymd.ymd = t.ymd
DROP TABLE #Temp
执行不了
怎么回事?
服务器: 消息 8120,级别 16,状态 1,行 15
列 '#Temp.ymd' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
可能是你自己改过一些地方吧,仔细看一下!
from (
select 1 as M
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
) s
left join
(select month(youDate) as m,sum(num) as n
from tb
where convert(vrachar(4),youDate,120)='2006'
group by month(youDate)) t
on s.m=t.m
我把格式调整了一下:
CREATE TABLE #Temp (ymd datetime)
INSERT INTO #Temp
SELECT '2006-01-02'
UNION ALL
SELECT '2007-01-02'
UNION ALL
SELECT '2007-01-02'
UNION ALL
SELECT '2007-02-02'
UNION ALL
SELECT '2007-03-02'
UNION ALL
SELECT '2007-04-02'SELECT ymd.ymd,ISNULL(t.cnt,0) cnt
FROM
(
SELECT TOP 100 PERCENT CONVERT(datetime,CONVERT(varchar(4),Y.Y)+'-'+CONVERT(varchar(2),M.M)+'-01') ymd
FROM
(
SELECT DISTINCT YEAR(ymd) Y
FROM #Temp
) Y
CROSS JOIN
(
SELECT 1 AS M UNION
SELECT 2 AS M UNION
SELECT 3 AS M UNION
SELECT 4 AS M UNION
SELECT 5 AS M UNION
SELECT 6 AS M UNION
SELECT 7 AS M UNION
SELECT 8 AS M UNION
SELECT 9 AS M UNION
SELECT 10 AS M UNION
SELECT 11 AS M UNION
SELECT 12 AS M
) M
ORDER BY 1
) ymdLEFT JOIN (
SELECT CONVERT(varchar(7),ymd,120)+'-01' ymd,COUNT(1) cnt
FROM #Temp
GROUP BY CONVERT(varchar(7),ymd,120)
) tON ymd.ymd = t.ymd
DROP TABLE #Temp
你自己也仔细看一下,如果还有错,我就没有办法了。
谢谢lzhs(快乐至上) !
INSERT INTO #Temp
SELECT '2006-01-02'
UNION ALL
SELECT '2007-01-02'
UNION ALL
SELECT '2007-01-02'
UNION ALL
SELECT '2007-02-02'
UNION ALL
SELECT '2007-03-02'
UNION ALL
SELECT '2007-04-02'
SELECT ymd.ymd,ISNULL(t.cnt,0) cnt
FROM
(
SELECT TOP 100 PERCENT CONVERT(datetime,CONVERT(varchar(4),Y.Y)+'-'+CONVERT(varchar(2),M.M)+'-01') ymd
FROM
(
SELECT DISTINCT YEAR(ymd) Y
FROM #Temp
) Y
CROSS JOIN
(
SELECT 1 AS M UNION
SELECT 2 AS M UNION
SELECT 3 AS M UNION
SELECT 4 AS M UNION
SELECT 5 AS M UNION
SELECT 6 AS M UNION
SELECT 7 AS M UNION
SELECT 8 AS M UNION
SELECT 9 AS M UNION
SELECT 10 AS M UNION
SELECT 11 AS M UNION
SELECT 12 AS M
) M
ORDER BY 1
) ymdLEFT JOIN (
SELECT CONVERT(varchar(7),ymd,120)+'-01' ymd,COUNT(1) cnt
FROM #Temp
GROUP BY CONVERT(varchar(7),ymd,120)+'-01' --这里原来少了个+'-01'
) tON ymd.ymd = t.ymd
DROP TABLE #Temp==========================================
不好意思,我用2000的试了一下,确实是语法的问题。
现在应该没有问题了。