求助一句简单的sql语法

Question

假设查询每样货物在2004年5月份的销售总额：可以这样写
select 货物信息,sum(需交款) 销售总额 from 货物出库表 where 出货日期 between '2004-05-01'
and '2004-05-31' group by 货物信息   order by 销售总额

Answer 1

--try
select datepart(m,出货日期) 日期,
       货品信息,
       sum(需交款) 总计
from table 
group by datepart(m,出货日期),
         货物信息

Answer 2

create table #t(YMD datetime,Nam varchar(10),TPrice money)insert into #t values('2005-4-5','Oil',100)
insert into #t values('2005-4-5','Paper',50)
insert into #t values('2005-5-8','Pencil',20)
insert into #t values('2005-5-18','Oil',50)
go
--delete from #t
select * from #t
/*
YMD                                                    Nam        TPrice                
------------------------------------------------------ ---------- --------------------- 
2005-04-05 00:00:00.000                                Oil        100.0000
2005-04-05 00:00:00.000                                Paper      50.0000
2005-05-08 00:00:00.000                                Pencil     20.0000
2005-05-18 00:00:00.000                                Oil        50.0000(4 row(s) affected)*/
declare @SDAT datetime
declare @EDAT datetime
set @SDAT = '2005-05-01'
set @EDAT = '2005-05-31'
declare @SQL as varchar(150)
set @SQL = '
select t.Nam,sum(t.TPrice)  as ''' + convert(varchar(10),@SDAT,120) +'~' +
convert(varchar(10),@EDAT,120) + ''' from #t t where t.YMD between '''+ 
convert(varchar(10),@SDAT,120) + ''' and ''' +  
convert(varchar(10),@EDAT,120) + ''' group by t.Nam'
exec(@SQL)/*
Nam        2005-05-01~2005-05-31 
---------- --------------------- 
Oil        50.0000
Pencil     20.0000
*/

Answer 3

declare @SDAT datetime
declare @EDAT datetime
set @SDAT = '2005-05-01'
set @EDAT = '2005-05-31 23:59:59'
select 货物信息,销售总额=sum(需交款) from 货物出库表 
where 出货日期 between @SDAT and @EDAT 
group by 货物信息   order by 销售总额

Answer 4

create table t#(saledate  datetime, infor    char(60), mon  int  )insert into t# values('2005-4-5','oil','100')
insert into t# values('2005-4-5','paper','50');
insert into t# values('2004-5-8','pen','20');
insert into t# values('2004-5-15','oil','50');
insert into t# values('2004-01-12','oil','100');
insert into t# values('2004-02-02','pen','200');
insert into t# values('2004-01-20','pencil','600');select * from t#2005-04-05 00:00:00.000 oil                                                          100
2005-04-05 00:00:00.000 paper                                                        50
2004-05-08 00:00:00.000 pen                                                          20
2004-05-15 00:00:00.000 oil                                                          50
2004-01-12 00:00:00.000 oil                                                          100
2004-02-02 00:00:00.000 pen                                                          200
2004-01-20 00:00:00.000 pencil                                                       600
--------------------------------------------------假如是四月份的
select   infor   , sum(mon) as total  from t# where  saledate>'2004-01-01' and saledate<'2004-12-30'group by inforoil                                                          150
pen                                                          220
pencil                                                       600

Answer 5

select 货物信息,sum(需交款) from 货物出库表 group by 货物信息,出货日期 having 出货日期 between '' and ''

Answer 6

select 货物信息,sum(需交款) from 货物出库表 group by 货物信息 where 出货日期 between a and b

Answer 7

select 货物信息,sum(需交款) from 货物出库表 group by 货物信息,出货日期 having 出货日期 between '2004-01-01' and '2005-01-01'