这样的格式用SQL怎么实现，高分求助
如题
日期 序号
2004-10-22 14:44:45.000 200410000006
2004-10-22 10:44:12.657 200410000008
2004-10-22 10:48:18.890 200410000009
2004-10-23 10:49:02.187 200410000010
2004-10-26 14:23:45.093 200410000011
2004-10-27 12:06:31.967 200410000012
2004-10-27 12:09:44.577 200410270001
2004-10-27 14:27:07.797 200411030001
日期 序号
2004-10-22 14:44:45.000 200410000006
2004-10-22 10:44:12.657 200410000008
2004-10-22 10:48:18.890 200410000009
小计 3
2004-10-23 10:49:02.187 200410000010
小计 1
2004-10-26 14:23:45.093 200410000011
小计 1
2004-10-27 12:06:31.967 200410000012
2004-10-27 12:09:44.577 200410270001
2004-10-27 14:27:07.797 200411030001
小计 3
合计 8
问题点数：50、回复次数：3Top
1 楼hai2003xp（古井小调）回复于 2005-02-01 17:25:58 得分 10
select 日期,序号 from tb
union all select '小计',count(*) from tb group by convert(varchar(10),日期,120)
union all select '合计',count(*) from tbTop
2 楼Qihua_wu（小吴）回复于 2005-02-01 17:30:56 得分 10
select case when len(日期)<10 then '小计' else 日期 end,序号
from
(
select convert(varchar,日期,120) 日期 ,序号 from tablename
union all
select convert(varchar,日期,111),convert(varchar,count(*))
from tablename
group by convert(datetime,convert(varchar,日期,111)+'23:59:59'))
order by 日期)
union all
select '合计',convert(varchar,count(*)) from tablename
Top
3 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-02-01 17:55:24 得分 30
--生成测试数据：
create table #t(
日期 datetime,
序号 varchar(20))
insert into #t select cast('2004-10-22 14:44:45.000' as datetime),'200410000006'
insert into #t select cast('2004-10-22 10:44:12.657' as datetime),'200410000008'
insert into #t select cast('2004-10-22 10:48:18.890' as datetime),'200410000009'
insert into #t select cast('2004-10-23 10:49:02.187' as datetime),'200410000010'
insert into #t select cast('2004-10-26 14:23:45.093' as datetime),'200410000011'
insert into #t select cast('2004-10-27 12:06:31.967' as datetime),'200410000012'
insert into #t select cast('2004-10-27 12:09:44.577' as datetime),'200410270001'
insert into #t select cast('2004-10-27 14:27:07.797' as datetime),'200411030001'
--执行查询：
select
case when (a.dates is null and a.序号 is null) then '合计'
when (a.序号 is null) then '小计'
else convert(varchar(21),b.日期,120)
end as 日期,
case when (a.序号 is not null) then a.序号
else rtrim(a.counts)
end as 序号
from
(select
convert(char(10),日期,120) as dates,
序号,
count(*) as counts
from
#t
group by
convert(char(10),日期,120) ,序号 WITH ROLLUP) a
left join
#t b
on
a.序号 = b.序号
--查看结果：
日期 序号
---------------------------------------
2004-10-22 14:44:45 200410000006
2004-10-22 10:44:12 200410000008
2004-10-22 10:48:18 200410000009
小计 3
2004-10-23 10:49:02 200410000010
小计 1
2004-10-26 14:23:45 200410000011
小计 1
2004-10-27 12:06:31 200410000012
2004-10-27 12:09:44 200410270001
2004-10-27 14:27:07 200411030001
小计 3
合计 8
Top
相关问题
关键词
得分解答快速导航
相关链接
广告也精彩
反馈
