isnull(col,0)DATENAME
返回代表指定日期的指定日期部分的字符串。语法
DATENAME ( datepart , date )
返回代表指定日期的指定日期部分的字符串。语法
DATENAME ( datepart , date )
解决方案 »
- 怪现象 一个表里的某条记录的两个字段相除居然为0
- 一个关于查询的问题 ,同一个表的内连接查询,高手来帮帮忙啊
- 求SQL SERVER命令大全
- 存储过程中,怎么检测一个值是否数字
- 将'2005-09-01'smalldatetime 转变成 '20050901' char 类型
- (大侠们帮忙)这个查询怎么写???
- 有没有什么如何做个数据库服务器的中文参考书啊?
- 请教,如何往MSSQLSERVER里导入200万条记录啊?主要是用于测试查询速度
- 插入、修改数据时,数据库是必须写入磁盘才返回成功,还是延迟写入,先返回成功!
- 是否sqlserver最大支持的每条记录的大小为 8000多B
- 求数据库设计工具?
- 关于两个表之间只做增量不做追加,并定时触发!
group by convert(varchar(7),date,120)
SELECT a.Mon,
ISNULL(b.DeathNumber,0) as TotalDN,
ISNULL(WoundNumber,0) as TotalWN,
ISNULL(LoseMoney,0) as TotalLM
FROM
(
SELECT 1 as Mon UNION ALL
SELECT 2 as Mon UNION ALL
SELECT 3 as Mon UNION ALL
SELECT 4 as Mon UNION ALL
SELECT 5 as Mon UNION ALL
SELECT 6 as Mon UNION ALL
SELECT 7 as Mon UNION ALL
SELECT 8 as Mon UNION ALL
SELECT 9 as Mon UNION ALL
SELECT 10 as Mon UNION ALL
SELECT 11 as Mon UNION ALL
SELECT 12 as Mon
) a LEFT JOIN
(
SELECT DatePart(mm,Date) as Mon,
SUM(DeathNumber) as TotalDN,
SUM(WoundNumber) as TotalWN,
SUM(LoseMoney) as TotalLM
FROM Accident_Report
GROUP BY DatePart(mm,Date)
) b
ON a.Mon=b.Mon
SELECT a.Mon,
ISNULL(b.TotalDN,0) as TotalDN,
ISNULL(b.TotalWN,0) as TotalWN,
ISNULL(b.TotalLM,0) as TotalLM
FROM
(
SELECT 1 as Mon UNION ALL
SELECT 2 as Mon UNION ALL
SELECT 3 as Mon UNION ALL
SELECT 4 as Mon UNION ALL
SELECT 5 as Mon UNION ALL
SELECT 6 as Mon UNION ALL
SELECT 7 as Mon UNION ALL
SELECT 8 as Mon UNION ALL
SELECT 9 as Mon UNION ALL
SELECT 10 as Mon UNION ALL
SELECT 11 as Mon UNION ALL
SELECT 12 as Mon
) a LEFT JOIN
(
SELECT DatePart(mm,Date) as Mon,
SUM(DeathNumber) as TotalDN,
SUM(WoundNumber) as TotalWN,
SUM(LoseMoney) as TotalLM
FROM Accident_Report
GROUP BY DatePart(mm,Date)
) b
ON a.Mon=b.Mon
应该不会,我用的是LEFT JOIN ,12个月都会有显示的。你先试一下咯,我没测,随手写的。
SELECT a.Mon,
ISNULL(b.UnitId,'') as UnitId,
ISNULL(b.TotalDN,0) as TotalDN,
ISNULL(b.TotalWN,0) as TotalWN,
ISNULL(b.TotalLM,0) as TotalLM
FROM
(
SELECT 1 as Mon UNION ALL
SELECT 2 as Mon UNION ALL
SELECT 3 as Mon UNION ALL
SELECT 4 as Mon UNION ALL
SELECT 5 as Mon UNION ALL
SELECT 6 as Mon UNION ALL
SELECT 7 as Mon UNION ALL
SELECT 8 as Mon UNION ALL
SELECT 9 as Mon UNION ALL
SELECT 10 as Mon UNION ALL
SELECT 11 as Mon UNION ALL
SELECT 12 as Mon
) a LEFT JOIN
(
SELECT DatePart(mm,Date) as Mon,
MIN(UnitId) as UnitId
SUM(DeathNumber) as TotalDN,
SUM(WoundNumber) as TotalWN,
SUM(LoseMoney) as TotalLM
FROM Accident_Report
GROUP BY DatePart(mm,Date)
) b
ON a.Mon=b.Mon