问题描述:
我用的SQL server2000
数据库中有一个表:DAYPRODUCT
表中有一个日期字段:date假如我要取3月1日到3月22日之间的数据,总共是22天
但这之间有些天数据库中没有记录:比方说3月14日 3月21日
但我想在做报表时 按时间升序排列即为:从3月1日到3月22日
对于没有记录的那两天,我想让他在报表中显示为0
报表的最终形式应为这样
时间 交易量
2008-03-01 500
2008-03-02 700
2008-03-03 550
2008-03-14 0
这个应该怎么样做啊
谢谢,大家指导一下,给点意见
我用的SQL server2000
数据库中有一个表:DAYPRODUCT
表中有一个日期字段:date假如我要取3月1日到3月22日之间的数据,总共是22天
但这之间有些天数据库中没有记录:比方说3月14日 3月21日
但我想在做报表时 按时间升序排列即为:从3月1日到3月22日
对于没有记录的那两天,我想让他在报表中显示为0
报表的最终形式应为这样
时间 交易量
2008-03-01 500
2008-03-02 700
2008-03-03 550
2008-03-14 0
这个应该怎么样做啊
谢谢,大家指导一下,给点意见
解决方案 »
- 启动Tomcat总出现这个问题,求指教
- Cookie存储空格的问题
- 在线等!!急,关于jsp页面问题。
- java 用控件批量上传并且压缩图片
- tomcat5.5为什么不能解释jsp文件?
- 大家看看这个系统要多少钱
- 请教:在采用 Struts + Hibernate 框架的情况下,如何在JSP中显示MySQL数据库中BLOB类型的图片(详细见内文),多谢!
- 哪里有smartupload包下载
- 一个关于打印applet的问题,在线,跟帖有分,百分等待。。
- Servlet高手急救:欲从servlet输出图形,请问linux环境下如何配置,web服务器为tomcat
- jsp自定义函数,原理和自定义标签差不多
- demo在项目里是谁负责做啊?架构师吗?程序员需要不需要做demo啊?
就是大月31天 小月30天 润月28天
sql吗 有些难写。通过程序还是可以实现的。
比如临时表,把3月1日到3月22日之间是22天日期存入
select a.日期, (case when a.日期 is null then 0 else a.交易量 end) from DAYPRODUCT a ritht join 临时表 b on a.日期 = b.日期
就是拼sql。
1、首先算出某月的天数。如,1月31天。
2、StringBuffer sb = new StringBuffer();
sb.append("select isnull((select 时间,交易量 from table where 时间='2008-03-01''),'0')");//先取3月1日的数据
3、然后循环剩余天数,假如还剩30天
for(int i=1;i<13;i++){
sb.append("union all select isnull((select 时间,交易量 from table where 时间='i'),'0')");//获取剩余天数
}
4、最后执行此sql即可。
Statememt stmt = yourConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY);
ResutSet rs = stmt.executeQuery("select 时间,交易量 from 表 where convert(时间, char(10), 112) between '2008-03-01' and '2008-03-22' order by 时间"); //convert的参数不是112就是120,忘了,好久没用SQLServer,LZ自己查一下帮助
Calendar c1 = new GregorianCalendar(2008, 2, 1);
Calendar c2 = new GregorianCalendar(2008, 2, 22);
Map<String, Integer> map = new HashMap<String, Integer>();
//SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
String s1="", s2="";
int v = 0;
while (c1.before(c2) == false) {
s1 = String.format("%1$tF", c1.getTime());
if (s1.equals(s2)) {
map.put(s2, v);
continue;
}
if (rs.next()) {
s2 = String.format("%1$tF", rs.getDate("时间")); //时间为null字段应该没有吧,重复的时间如果有的话以下要修改,这里以不重复处理
v = rs.getInt("交易量");
if (s1.equals(s2)) {
map.put(s2, v);
} else if (s1.compareTo(s2) < 0) { //s1>s2的可能没有吧,按LZ的数据
map.put(s1, 0);
}
} else {
map.put(s1, 0);
}
}for (Map.Entry<String, Integer> entry : map.entrySet()) {
System.out.printf("%1$tF, %d", entry.getKey(), entry.getValue());
}
...
这里改成System.out.printf("%s, %d", entry.getKey(), entry.getValue()); //自己都忘了key是String了
Statememt stmt = yourConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY);
ResutSet rs = stmt.executeQuery("select 时间,交易量 from 表 where convert(时间, char(10), 112) between '2008-03-01' and '2008-03-22' order by 时间"); //convert的参数不是112就是120,忘了,好久没用SQLServer,LZ自己查一下帮助
Calendar c1 = new GregorianCalendar(2008, 2, 1);
Calendar c2 = new GregorianCalendar(2008, 2, 22);
Map<String, Integer> map = new HashMap<String, Integer>();
//SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
String s1="", s2="";
int v = 0;
while (c1.before(c2) == false) {
s1 = String.format("%1$tF", c1.getTime());
if (s1.equals(s2)) {
map.put(s2, v);
continue;
}
if (rs.next()) {
s2 = String.format("%1$tF", rs.getDate("时间")); //时间为null字段应该没有吧,重复的时间如果有的话以下要修改,这里以不重复处理
v = rs.getInt("交易量");
if (s1.equals(s2)) {
map.put(s2, v);
} else if (s1.compareTo(s2) < 0) { //s1>s2的可能没有吧,按LZ的数据
map.put(s1, 0);
}
} else {
map.put(s1, 0);
}
c1.add(Calendar.Date, 1);
}for (Map.Entry<String, Integer> entry : map.entrySet()) {
System.out.printf("%s, %d", entry.getKey(), entry.getValue());
}
...
谢谢你的指点
昨天晚上回去不甘心
调试了大半个晚上,在程序中实现了可能执行效率和方法没有你的好吧
先把任务完成,我再慢慢学习
我的具体做法是:
昨天可能也着急,题目的一些其他条件也没有说
今天稍微补充一下:
查询日期段由用户自己选择,作为参数传递
我定义了一个STRING数组,用来存放连续的日期
用一个FOR循环,循环一次,日期天数加1,然后存入数组
当SQL查询出返回数据集之后
我把查询出的日期和我之前存放好的日期利用循环判断是否相等、
相等的话,就存入相应的交易量
不相等的话,就直接赋值给0具体实现就是这样
代码中有很多for循环
我不知道这样做好不好
大家对我做的这个,有什么意见
还请指点一下
DECLARE @starttiem DATETIME,@endtime DATETIME
SELECT @starttiem = '2009-03-01', @endtime ='2009-03-22'
WHILE @starttiem <= @endtime
BEGIN
INSERT INTO @t
SELECT @starttiem
SET @starttiem = CONVERT(VARCHAR(10),Dateadd(DAY,1,@starttiem),120)
END
select * from @t
DECLARE @st DATETIME,@et DATETIME,@n INT
SELECT @st='2009-3-1',@et='2009-3-22',@n =DATEDIFF(dd,'2009-3-1','2009-3-22') + 1 SET ROWCOUNT @n
SELECT ID=IDENTITY(INT),dt = CAST(NULL AS DATETIME) INTO #1 FROM sysobjects,syscolumns
UPDATE #1 SET dt = DATEADD(dd,ID-1,@st)
SET ROWCOUNT 0SELECT a.dt,ISNULL(sm,0) 交易量 FROM #1 a
LEFT JOIN
(
SELECT CONVERT(VARCHAR(10),[date],120) dtYMD,SUM(交易量) sm
FROM DAYPRODUCT
WHERE [date] >=@st AND [date]<DATEADD(dd,1,@et)
GROUP BY CONVERT(VARCHAR(10),[date],120)
) b
ON b.dtYMD = dtDROP TABLE #1
再做一个时间表
和这个时间表关联就好了
时间表里面的数据你可以每天都有啊给你个sql函数连接
sql各种日期函数用法总结
import java.util.Arrays;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;/**
* 统计报表数据容器
*
* @param <R> 行名类型
* @param <C> 列名类型
*
* @since 2009-4-28 上午10:41:48
*/
public class Report<R, C> { /**
* 报表容器
*/
private Map<R, Map<C, Long>> data = new LinkedHashMap<R, Map<C, Long>>();
/**
* 总计
*/
private Map<C, Long> total;
/**
* 列名
*/
private List<C> columnNames;
/**
* 构造方法
* @param rowNames 行名集
* @param columnNames 列名集
*/
public Report(R[] rowNames, C[] columnNames) {
this(Arrays.asList(rowNames), Arrays.asList(columnNames));
}
public Report(R[] rowNames, Collection<C> columnNames) {
this(Arrays.asList(rowNames), columnNames);
}
public Report(Collection<R> rowNames, C[] columnNames) {
this(rowNames, Arrays.asList(columnNames));
}
public Report(Collection<R> rowNames, Collection<C> columnNames) {
this.columnNames = new ArrayList<C>(columnNames);
init(rowNames, columnNames);
}
/**
* 根据行名、列名存放数据
* @param rowName
* @param columnName
* @param count
*
* @since 2009-4-28 上午10:42:15
*/
public void addData(R rowName, C columnName, Long count) {
Map<C, Long> row = data.get(rowName);
row.put(columnName, row.get(columnName) + count);
total.put(columnName, total.get(columnName) + count);
}
public void addData(R rowName, C columnName, Integer count) {
addData(rowName, columnName, count.longValue());
} public List<C> getColumnNames() {
return columnNames;
} public Map<C, Long> getTotal() {
return total;
}
public Map<R, Map<C, Long>> getData() {
return data;
}
/**
* 初始化数据报表
* @param rowNames
* @param columnNames
*
* @since 2009-4-28 上午10:43:53
*/
private void init(Collection<R> rowNames, Collection<C> columnNames) {
for(Iterator<R> i = rowNames.iterator(); i.hasNext(); ) {
data.put(i.next(), generateRow(columnNames));
}
total = generateRow(columnNames);
}
/**
* 初始化报表中的一行
* @param columnNames
* @return
*
* @since 2009-4-28 上午10:43:32
*/
private Map<C, Long> generateRow(Collection<C> columnNames) {
Map<C, Long> row = new LinkedHashMap<C, Long>();
for(Iterator<C> i = columnNames.iterator(); i.hasNext(); ) {
row.put(i.next(), 0L);
}
return row;
}
}
测试代码:import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Random;import com.bao.pojo.Report;public class ReportService {
private final static int[] MAX_DAY = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
public final static String COLUMN_1_NAME = "交易量";
public Report<Date, String> getReport(int year, int month, int startDay, int endDay) {
// 行名
List<Date> dates = getDates(year, month, startDay, endDay);
// 列名
String[] columnNames = new String[]{ COLUMN_1_NAME };
// 报表对象
Report<Date, String> report = new Report<Date, String>(dates, columnNames);
Date[] testDates = getTestData(year, month, startDay, endDay);
for(int i = 0; i < testDates.length; i++) {
// 添加数据
report.addData(testDates[i], COLUMN_1_NAME, 400);
}
return report;
}
/**
* 生成测试日期,假设是从数据库中取出来的
* @param year
* @param month
* @param startDay
* @param endDay
* @return
* @since 2009-4-29 下午01:07:13
*/
private Date[] getTestData(int year, int month, int startDay, int endDay) {
Date[] dates = new Date[10];
Calendar c = initCalendar(year, month, startDay);
Random ran = new Random();
for(int i = 0, k = endDay - startDay + 1; i < dates.length; i++) {
c.set(Calendar.DATE, ran.nextInt(k) + 1);
dates[i] = c.getTime();
}
return dates;
}
/**
* 生成统计报表连续的日期
*
* @param year
* @param month
* @param startDay
* @param endDay
* @return
*
* @since 2009-4-29 下午01:06:12
*/
private List<Date> getDates(int year, int month, int startDay, int endDay) {
if(year < 1) {
throw new IllegalArgumentException("year must be great than 1.");
}
if(startDay < 1 || endDay < 1 || endDay > getMonthMaxDay(year, month)) {
throw new IllegalArgumentException("day is illegal value.");
}
if(endDay < startDay) {
throw new IllegalArgumentException("endDay must be great than startDay.");
}
List<Date> dates = new ArrayList<Date>(endDay - startDay + 1);
Calendar c = initCalendar(year, month, startDay);
for(int i = 0, k = endDay - startDay + 1; i < k; i++) {
dates.add(c.getTime());
c.add(Calendar.DATE, 1);
}
return dates;
}
/**
* 初始化 Calendar
* @param year
* @param month
* @param startDay
* @return
*
* @since 2009-4-29 下午01:06:23
*/
private Calendar initCalendar(int year, int month, int startDay) {
Calendar c = Calendar.getInstance();
c.set(Calendar.YEAR, year);
c.set(Calendar.MONTH, month - 1);
c.set(Calendar.DATE, startDay);
c.set(Calendar.HOUR_OF_DAY, 0);
c.set(Calendar.MINUTE, 0);
c.set(Calendar.SECOND, 0);
c.set(Calendar.MILLISECOND, 0);
return c;
}
/**
* 获得某个月最大的天数
* @param year
* @param month
* @return
*
* @since 2009-4-29 下午01:06:35
*/
private static int getMonthMaxDay(int year, int month) {
int max = MAX_DAY[month - 1];
if(month != 2) {
return max;
}
if(year % 400 == 0 || (year % 100 != 0 && year % 4 == 0)) {
max++;
}
return max;
}
}
import java.util.Date;import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.bao.pojo.Report;
import com.bao.service.ReportService;public class TestServlet extends javax.servlet.http.HttpServlet implements
javax.servlet.Servlet { private static final long serialVersionUID = -7122921551754535982L; public TestServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
ReportService rs = new ReportService();
Report<Date, String> report = rs.getReport(2009, 3, 1, 22);
request.setAttribute("report", report);
forward("/test.jsp", request, response);
}
private void forward(String path, HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
getServletContext().getRequestDispatcher(path).forward(request, response);
}
}页面:<%@ page contentType="text/html; charset=gbk"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<html>
<head>
<title>test</title>
</head><style type="text/css">
* {
font-size: 10pt;
}
table {
border-top: 1px solid black;
border-left: 1px solid black;
border-collapse: collapse;
}table th {
background-color: #c0c0c0;
}table th, table td {
border-right: 1px solid black;
border-bottom: 1px solid black;
padding: 4px 10px;
}
</style><body>
<table>
<colgroup>
<col align="left" />
<c:forEach items="${report.columnNames}" var="colName">
<col align="right" />
</c:forEach>
</colgroup>
<thead>
<tr>
<th></th>
<c:forEach items="${report.columnNames}" var="colName">
<th>${colName}</th>
</c:forEach>
</tr>
</thead>
<tbody>
<c:forEach items="${report.data}" var="entry">
<tr>
<td><fmt:formatDate pattern="yyyy-MM-dd" value="${entry.key}" /></td>
<c:forEach items="${entry.value}" var="e">
<td>${e.value}</td>
</c:forEach>
</tr>
</c:forEach>
</tbody>
<tfoot>
<tr>
<th>总计</th>
<c:forEach items="${report.total}" var="totalEntry">
<th>${totalEntry.value}</th>
</c:forEach>
</tr>
</tfoot>
</table>
</body>
</html>页面显示:
create table DAYPRODUCT(id int identity,date smalldatetime,quantity int)insert DAYPRODUCT
select '2008-3-10',1000
union all
select '2008-3-11',1001
union all
select '2008-3-12',1002
union all
select '2008-3-13',1003
union all
select '2008-3-14',1004
union all
select '2008-3-15',1005declare @reportDate smalldatetime
set @reportDate='2008-3-1';with product as(select date,quantity from DAYPRODUCT where date between @reportDate and dateadd(mm,1,@reportDate)-1)select * from product
union all
select date,0 quantity from (select top 31 @reportDate+ row_number() over(order by colid)-1 date from syscolumns) tmp
where date<dateadd(mm,1,@reportDate) and not exists(select 1 from product where date=tmp.date)
order by datedrop table DAYPRODUCT
create table DAYPRODUCT(id int identity,date smalldatetime,quantity int)insert DAYPRODUCT
select '2008-3-10',1000
union all
select '2008-3-11',1001
union all
select '2008-3-12',1002
union all
select '2008-3-13',1003
union all
select '2008-3-14',1004
union all
select '2008-3-15',1005declare @reportDate smalldatetime
set @reportDate='2008-3-1';with product as(select date,quantity from DAYPRODUCT where date between @reportDate and dateadd(mm,1,@reportDate)-1)select t1.date,(case when t2.quantity is null then 0 else t2.quantity end) quantity
from (select top 31 @reportDate+ row_number() over(order by colid)-1 date,0 quantity from syscolumns) t1
left join product t2 on t1.date=t2.date and t2.date<dateadd(mm,1,@reportDate)
order by datedrop table DAYPRODUCT
新产生必要的数据行, 还就是LZ所要求, 期间内的每一天, 实现如果没有数据以0填充
我们可以先用循环产生必要行, 放入中间表变量如@tMid, 再用中间表@tMid左连接实际数据表查询即可
下面是我根据LZ的要求写成一个函数if( object_id('fn_DayProduct') is not null )
drop function fn_DayProduct
go
create function fn_DayProduct( @dtBeg smalldatetime, @dtEnd smalldatetime )
returns @tRet table
(
时间 smalldatetime ,
交易量 int
)
as
begin
declare @dtTmp smalldatetime
set @dtTmp = @dtBeg
declare @tMid table
(
时间 smalldatetime ,
交易量 int
) while( @dtTmp <= @dtEnd )
begin
insert into @tMid values( @dtTmp, 0 )
set @dtTmp = dateadd( day, 1, @dtTmp )
end insert into @tRet
select z.时间, isnull(s.交易量, z.交易量)
from @tMid as z
left join DAYPRODUCT as s
on z.时间 = s.date
order by z.时间
return
end
go-- 调用时直接用以下语句即可
select * from dbo.fn_DayProduct( '2008-03-01', '2008-03-22' )
DECLARE @dn As int;
Select @BeginDate='2009-4-1',@EndDate='2009-4-30'
Set @dn=DATEDIFF(dd,@BeginDate,@EndDate)+1 ;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
Select Convert(char(10),DateAdd(dd,N-1,@BeginDate),120) As DateList From Nums Where N<=@dn用它左连接数据表.最好写成SQL FUNCTION,可以直接调用
as
declare @saledate datetime
declare @salemoney numeric(18,4) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t2]
CREATE TABLE [dbo].[t2] (
[销售日期] [datetime] NULL ,
[销售金额] [numeric](18, 4) NULL
) ON [PRIMARY]if object_id('tempdb..#tmp') is not null
drop table #tmp
----生成临时表
select id = identity(int,0,1) into #tmp from syscolumns,sysobjects
----生成区间日期
insert t2
select dateadd(dd,b.id,@startdate),0 from #tmp b
where dateadd(dd,b.id,@startdate) <= @enddateselect * from t2
要么用程序作,要么用存储过程,要么用临时表
比如临时表,把3月1日到3月22日之间是22天日期存入
select a.日期, (case when a.日期 is null then 0 else a.交易量 end) from DAYPRODUCT a ritht join 临时表 b on a.日期 = b.日期
CREATE TABLE tb(d DATETIME,v VARCHAR(50))
INSERT INTO tb SELECT '2009-4-1','500'
UNION ALL SELECT '2009-4-2','400'
UNION ALL SELECT '2009-4-5','300'
UNION ALL SELECT '2009-4-9','200'
UNION ALL SElECT '2009-4-10','100'DECLARE @t table (d DATETIME,v VARCHAR(50))DECLARE @bdate DATETIME
DECLARE @edate DATETIME
DECLARE @datediff INT
DECLARE @num INT
SET @bdate = '2009-4-1'
SET @edate = '2009-4-10'
SET @datediff=datediff(day,@bdate,@edate)
SET @num = 0
WHILE @num <= @datediff
BEGIN
INSERT INTO @t
SELECT A.d,CASE WHEN A.v IS NULL THEN 0 ELSE A.v END FROM
(
SELECT DATEADD(day,@num,@bdate) AS d,
(
SELECT v FROM tb WHERE d=DATEADD(day,@num,@bdate)
) AS v
) AS A
SET @num = @num +1
END
SELECT * FROM tb
SELECT * FROM @t
-- 结果
/* tb表*/
2009-04-01 00:00:00.000 500
2009-04-02 00:00:00.000 400
2009-04-05 00:00:00.000 300
2009-04-09 00:00:00.000 200
2009-04-10 00:00:00.000 100/* @t */
2009-04-01 00:00:00.000 500
2009-04-02 00:00:00.000 400
2009-04-03 00:00:00.000 0
2009-04-04 00:00:00.000 0
2009-04-05 00:00:00.000 300
2009-04-06 00:00:00.000 0
2009-04-07 00:00:00.000 0
2009-04-08 00:00:00.000 0
2009-04-09 00:00:00.000 200
2009-04-10 00:00:00.000 100
drop function fn_DayProduct
go
create function fn_DayProduct( @dtBeg smalldatetime, @dtEnd smalldatetime )
returns @tRet table
(
时间 smalldatetime ,
交易量 int
)
as
begin
declare @dtTmp smalldatetime
set @dtTmp = @dtBeg
declare @tMid table
(
时间 smalldatetime ,
交易量 int
) while( @dtTmp <= @dtEnd )
begin
insert into @tMid values( @dtTmp, 0 )
set @dtTmp = dateadd( day, 1, @dtTmp )
end insert into @tRet
select z.时间, isnull(s.交易量, z.交易量)
from @tMid as z
left join DAYPRODUCT as s
on z.时间 = s.date
order by z.时间
return
end
go-- 调用时直接用以下语句即可
select * from dbo.fn_DayProduct( '2008-03-01', '2008-03-22' )
看看吧
学习了,关注!
我把取出的数据
做图并要打印出查询结果
我试着在程序里控制吧
WWW.3benben.com