Java code...
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 map = new HashMap();
//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 entry : map.entrySet()) {
System.out.printf("%s, %d", entry.getKey(), entry.getValue());
}
...
------解决方案--------------------
给你提供个思路,先生成一张你想要的日期段的表,然后用这张表去LEFT JOIN 你真正的数据表,按日期字段进行关联即可SQL codeDECLARE @t TABLE ( Times DATETIME)
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
------解决方案--------------------
生成时间基表,左连即可。SQL codeDECLARE @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 0
SELECT 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]
GROUP BY CONVERT(VARCHAR(10),[date],120)
) b
ON b.dtYMD = dt
DROP TABLE #1