select aa, '20031001',sum(cc) as cc,sum(dd) as dd
from 表
where cast(bb as datetime) < '2003-10-01'
group by aa
union
select *
from 表
where cast(bb as datetime) betwwen '2003-10-01' and '2003-10-05'
from 表
where cast(bb as datetime) < '2003-10-01'
group by aa
union
select *
from 表
where cast(bb as datetime) betwwen '2003-10-01' and '2003-10-05'
解决方案 »
- 自增长列异常
- sql server 2005能插入多少条记录?
- SQL查询显示问题
- 新手问题,请高手来帮助我啊!
- sql server agent无法启动
- 执行dbcc checkdb('databasename')报错:在 sysobjects、sysindexes、syscolumns 或 systypes 中存在一致性错误,妨碍了进一步的 CHECKDB
- 问一个数据库命名的问题
- 请众高手帮忙?很急,谢谢!
- 对一个表的一个事件(如insert)可以建多个触发器吗?
- .net面试题,求大神解答(附了答案,可能有误)
- 看一看这个,一个sql 语句能不能搞定?
- 各位高手,大力、邹建等帮忙,sql句子问题
from 表
where cast(bb as datetime) < '2003-10-01'
group by aa
union
select *
from 表
where cast(bb as datetime) betwwen '2003-10-01' and '2003-10-05'
union all
select aa,bb,sum(cc),sum(dd)
from test and bb <'20031001' group by aa,bb
union all
select aa,bb,cc,dd from 表 where cast(bb as datetime) between '20031001' and '20031005'
union all
select aa,bb,cc,dd from 表 where cast(bb as datetime) between '20031001' and '20031005'
union all
select aa,'期中',sum(cc) cc,sum(dd) from 表 where cast(bb as datetime) between '20031001' and '20031005' group by aa
union all
select aa,max(bb) bb,sum(cc) cc,sum(dd) dd
from test and bb <'20031001' group by aa
select aa,bb,cc,dd,ee,ff,gg from test where bb BETWEEN '20031001' and '20031005'
union all
select aa,max(bb) bb,sum(cc) cc,sum(dd) dd,min(ee) ee,min(ff) ff,min(gg) ff
from test and bb <'20031001' group by aa) a
order by bb,.....
楼主的ee 是char(20),不能使用min(ee)
declare @bb1 char(10),@bb2 char(10)
select @bb1='20031001',@bb2='20031005'--查询
select aa,bb,cc,dd,ee,ff
from(
select sortid=1,* from 表 where bb between @bb1 and @bb2
union all
select sortid=0,null,null,sum(cc),sum(dd),null,null from 表 where bb<@bb2
) a order by sortid,aa,bb
declare @bb1 char(10),@bb2 char(10)
select @bb1='20031001',@bb2='20031005'--查询
select aa,bb,cc,dd,ee,ff
from(
select sortid=1,* from 表 where bb between @bb1 and @bb2
union all
select sortid=0,aa,null,sum(cc),sum(dd),null,null from 表 where bb<@bb2 group by aa
) a order by aa,sortid,bb
declare @表 table(aa char(10),bb char(10),cc float,dd float,ee char(20),ff char(10))
insert into @表(aa,bb,cc,dd)
select 'rrr','20031005',200.00,0
union all select 'xxx','20031001',100.23,0
union all select 'yyy','20030901',200.33,0
union all select 'zzz','20030911',0,100--定义要查询的区间
declare @bb1 char(10),@bb2 char(10)
select @bb1='20031001',@bb2='20031005'--查询
select aa,bb,cc,dd,ee,ff
from(
select sortid=1,* from @表 where bb between @bb1 and @bb2
union all
select sortid=0,aa,null,sum(cc),sum(dd),null,null from @表 where bb<@bb2 group by aa
) a order by aa,sortid,bb
返回表达式的最大值。语法
MAX ( [ ALL | DISTINCT ] expression )expression常量、列名、函数以及算术运算符、按位运算符和字符串运算符的任意组合。MAX 可用于数字列、字符列和 datetime 列,但不能用于 bit 列。不允许使用聚合函数和子查询。
union all
select aa,'期中',sum(cc) cc,sum(dd) from 表 where cast(bb as datetime) between '20031001' and '20031005' group by aa
http://www.pdriver.com/bbs5/dispbbs.asp?boardID=14&ID=113894