sql select问题

[wchengyu]

要求：表a中有字段 物品名称，价格，销售数量，日期
sql语句，实现结果显示 第一周销售数量，第二周销售数量，或第一个月销售数量，第二个月销售数量
可以不显示其他内容。

[chuifengde]

不懂你第一周是怎么定义的

[wanttoknow2001]

select sum(价格*销售数量) as 销售数量,str(year(日期))+str(month(日期)) as 日期 from cost group by 类型,str(year(日期))+str(month(日期))

这是按月统计 不知道是不是你要的

[happy_fly_bird]

我有一个每月的营业额和总额,希望能给你帮助,是好久以前写的.

create table table1(tid int primary key , ttime datetime,balance int,yyt int,person int)

insert into table1 values(1,'2002-5-25',145774,1,1001)
insert into table1 values(2,'2003-4-29',14588,2,1001)
insert into table1 values(3,'2005-5-12',125664,3,1003)
insert into table1 values(4,'2002-1-22',645555,5,1002)

insert into table1 values(5,'2005-2-22',64457,1,1004)
insert into table1 values(6,'2005-8-22',64457,1,1004)
insert into table1 values(7,'2005-4-22',64457,1,1004)
insert into table1 values(8,'2005-12-22',64457,1,1004)
insert into table1 values(9,'2005-6-22',64457,2,1004)
insert into table1 values(10,'2005-9-22',64457,2,1004)
insert into table1 values(11,'2005-11-22',64457,4,1004)

go
select tid, year(ttime)as years,month(ttime) as months,balance,yyt,person into table3 from table1
go
select *from table3
go
select t3.*, (t3.months1+t3.months2+t3.months3+t3.months4+t3.months5) as sumprice,
(t3.months1+t3.months2+t3.months3+t3.months4+t3.months5)/5 as avgprice
from(select years ,
sum( case t.months when 1 then balance else 0 end) as months1,

sum(case t.months when 2 then balance else 0 end) as months2,
sum (case t. months when 3 then balance else 0 end )as months3,
sum (case t.months when 4 then balance else 0 end) as months4,
sum (case t.months when 5 then balance else 0 end) as months5
from table3 as t
group by t.years) as t3

[happy_fly_bird]

我想思想是一样的,你看了应该会明白
balance 营业额,yyt是几号窗口,person是员工代号

[wchengyu]

谢谢楼上的，每月差不多可以实现了，那每周呢，str(week(日期))好像不行。

[taxpayer]

select 物品名称,sum(销售数量) as 销售数量,datename(ww,日期) from a group by 物品名称,datename(ww,日期)

[rookie_one]

select 物品名称,sum(销售数量) as 销售数量,DATEPART (week,日期) 第几周
from a
group by 物品名称,DATEPART(week,日期)

--DATEPART(week,日期)返回本日期属于本年度的第几周，强烈建议lz看看下边这个
http://blog.joycode.com/ghj/archive/2003/11/11/6319.aspx