ID YeWuYuan KeHuMing RuKuan DateTime
1 wanggang liming 500 2008-9-15
2 liuhong zhangli 300 2008-9-16
2 liuli liming 200 2008-9-16类似这样的MSSQL数据库的一个表,字段分别为【ID、业务员、客户名、入款金额、入款时间】,我想查询指定时间段内(如2008-8-16至2008-9-15),入款总额大于600的客户。请教各位老师,SQL语句该怎么写呢。
1 wanggang liming 500 2008-9-15
2 liuhong zhangli 300 2008-9-16
2 liuli liming 200 2008-9-16类似这样的MSSQL数据库的一个表,字段分别为【ID、业务员、客户名、入款金额、入款时间】,我想查询指定时间段内(如2008-8-16至2008-9-15),入款总额大于600的客户。请教各位老师,SQL语句该怎么写呢。
FROM TableName
WHERE [DateTime] Between '2008-08-16' AND '2008-09-15'
GROUP BY KeHuMing
HAVING SUM(RuKuan)>600
select kehuming from (
select kehuming,sum(RuKuan) summary from table
where datetime>='2008-8-16' and datetime<'2008-9-15'
group by kehuming
) a where a.summary>600;
select KeHuMing from tb
where [DateTime] between '2008-8-16' and '2008-9-15'
group by KeHuMing
having sum(RuKuan)>600
FROM TableName
WHERE [DateTime] Between '2008-08-16' AND '2008-09-15'
GROUP BY KeHuMing
HAVING SUM(RuKuan)>600
where [DateTime] between '2008-8-16' and '2008-9-15'
group by KeHuMing
having sum(RuKuan)>600
create table #table1(
YeWuYuan nvarchar(50),
KeHuMing nvarchar(50),
RuKuan decimal(10,2),
[DateTime] DateTime
)insert into #table1
select 'wanggang','liming',500, '2008-9-15'union all
select 'liuli','liming',700, '2008-9-15' union all
select 'liuhong','zhangli',300, '2008-9-16'union all
select 'liuli','liming',200, '2008-9-16'declare @startTime datetime
declare @endTime datetimeset @startTime ='2008-8-16'
set @endTime ='2008-9-15'select KeHuMing,sum(RuKuan) as RuKuan
from #table1
where [DateTime] between @startTime and @endTime
group by KeHuMingdrop table #table1
结果:
KeHuMing RuKuan
---------------
liming 1200.00
KeHuMing
FROM 表
WHERE
DATETIME > '2008-8-16' AND DATETIME < '2008-9-15'
GROUP BY KeHuMing
HAVING SUM(RuKuan) > 600
create table #table1(
YeWuYuan nvarchar(50),
KeHuMing nvarchar(50),
RuKuan decimal(10,2),
[DateTime] DateTime
)insert into #table1
select 'wanggang','liming',500, '2008-9-15'union all
select 'liuli','liming',700, '2008-9-15' union all
select 'liuhong','zhangli',300, '2008-9-16'union all
select 'liuli','liming',200, '2008-9-16'declare @startTime datetime
declare @endTime datetimeset @startTime ='2008-8-16'
set @endTime ='2008-9-15'select KeHuMing,sum(RuKuan) as RuKuan
from #table1
where [DateTime] between @startTime and @endTime
group by KeHuMing
having sum(RuKuan) > 600drop table #table1
FROM TableName
WHERE [DateTime] Between '2008-8-16' AND '2008-9-16'
GROUP BY KeHuMing
HAVING SUM(RuKuan)>600
或者
select KeHuMing,sum(RuKuan) as 入款总金额
from TableName
where DateTime>='2008-8-16' and DateTime<='2008-9-16'
group by KeHuMing
having sum(RuKuan)>600