java 连续日期,关于日期连续显示有关问题

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