/**/
create table test
(
id int identity(1,1),
bank_name varchar(20),--银行名称
start_date datetime,--开始时间
end_date datetime,--结束时间
je int --金额
)
insert into test select'a','2006-1-1','2006-2-1',1000
insert into test select'a','2006-1-1','2006-2-1',2000
insert into test select'b','2006-1-1','2006-2-1',3000
insert into test select'b','2006-1-1','2006-2-1',4000----------------------------------------------------怎样查询出start_date和end_date之间的数据,并且按照bank_name分组例如:要查询出大于start_date并且小于end_date的所有数据,并且按照rq分组
drop table test
解决方案 »
- vb中怎样将从文本框获取的“0002905771”的字符串转换成char类型的数据呢?
- 如何 SQL 将表列转为字段?
- 求助:如果有重复记录,就合并某个字段的sql语句
- 由于某些数据库维护或重新配置操作,SQL Server 经历了 'SQL Plans' 缓存存储区(计划缓存的一部分)的 1 次刷新
- 如何动态的创建视图的列
- 问条sql语句问题,为什么这条sql查出来的结果不对呢?
- 求sql语句
- 远程访问SQL Server数据库的问题
- 做的是C/S的软件,数据库用的是SQL SERVER2000,用INSTALLSHIELD6.3做的安装,客户端的作好了,请问数据库端怎么办,难道真的要装个SQL SERVER么(在此等候,烦劳赐教)
- 在触发器里可以调用ASP程序吗?
- 求答:时间相加,字段格式是varchar,~~~
- 稍微复杂的统计
例如:要查询出大于start_date并且小于end_date的所有数据,并且按照bank_name分组
declare @et datetime
set @st='2005-01-01'
set @et='2007-01-01'select bank_name,sum(je) as je from test where start_date>@st and end_date<@et group by bank_name--这样?
"例如:要查询出大于start_date并且小于end_date的所有数据,并且按照rq分组"
你所谓的 start_date和end_date是指表中字段,还是你指定的变量值?若是前者,那么之间的数据又是指什么?
where '日期' between start_date and end_date
group by bank_name
declare @dt2 as datetime
set @dt1 = '2006-1-1'
set @dt2 = '2006-2-1'select bank_name as 银行名称, sum(je) as 金额
from test
where start_date>=@dt1 and start_date <= @dt2 and end_date>=@dt1 and end_date <= @dt2
group by bank_name
declare @dt2 as datetime
select @dt1 = min(start_date) from test
select @dt2 = max(end_date) from testselect bank_name as 银行名称, sum(je) as 金额
from test
where start_date>=@dt1 and start_date <= @dt2 and end_date>=@dt1 and end_date <= @dt2
group by bank_name
declare @date1 as datetime
set @date1 = '2006-12-20'
select bank_name,sum(je) as je
from test where @date1 between (start_date and end_date)
group by bank_name
from test where @date1 between (start_date and end_date)
group by bank_name
select * from test where @date1 between (start_date and end_date)
order by bank_name