group by 问题

Question

我的表
ID(主键)  MyDATE(日期字段)  MyPrice（价格）MyCount（数量）MyBalance（现有库存） MyName
     1          2005-1-1          10                   5              2          MP3
     2          2005-1-15         15                   10             5          U盘 
     3          2005-1-28         5                    5              7          MP3
     4          2005-2-1          7                    12             5          MP3
     5          2005-2-5          3                    3              3          U盘
     6          2005-2-10         2                    1              6          U盘
     7          2005-2-15         8                    8              8         MP3       
想统计：1，2月的价格，个种商品的价格、数量总和，库存按 每月最后时间的数量 计算。
如： 1月 MP3,价格和是15，数量10，库存 按1月中最后时间的记录算，就是 7
请教各位 这个SQL 怎么 写

Answer 1

Create Proc Proc_Order_Filter
(
   @SQLEx varchar(1000)
)
as
BEGIN
    DECLARE @strSQL VARCHAR(2000);
 
    SET @strSQL='SELECT ID,MYDATEIM,MYPRICE,
                  MYCOUNT,MYNAME,MyBalance,
                  SUM(MYPRICE) AS MyPriceName
                 FROM  ORDERS WHERE(1>0)';
    IF(@SQLEx is not null and rtrim(ltrim(@SQLEx))<>'' and @SQLEx<>N'')
    BEGIN
      SET @strSQL=@strSQL+@SQLEx;
    END
    SET @strSQL=@strSQL+'GROUP BY ID,MYDATEIM,MYPRICE,MYCOUNT,MYNAME,MYBALANCE';
    EXEC(@strSQL);
END//*****传入查询条件就可以了.

Answer 2

select convert(char(06),T1.MyDATE,112) as [month], T1.MyPrice ,T1.MyCount ,T2.MyBalance,T1.MyName 
from  (select max(MyDATE) as MyDATE , sum(MyPrice) as MyPrice ,sum(MyCount) as MyCount,MyName  from TableName group by convert(char(06),MyDATE),MyName) T1,TableName T2
where T1.MyName=T2.MyName and T1.MyDATE=T2.MyDATE

Answer 3

----创建测试数据
declare @t table(id int,mydate datetime,myprice int,mycount int,mybalance int, myname varchar(10))
insert @t
select 1,'2005-01-01',10,5,2,'MP3' union all
select 2,'2005-01-15',15,10,5,'U盘' union all
select 3,'2005-01-28',5,5,7,'MP3' union all
select 4,'2005-02-01',7,12,5,'MP3' union all
select 5,'2005-02-05',3,3,3,'U盘' union all
select 6,'2005-02-10',2,1,6,'U盘' union all
select 7,'2005-02-15',8,8,8,'MP3' 
----按月对每个产品汇总
select a.*,b.mybalance from 
(select convert(varchar(7),mydate,120) as mydate,myname,sum(myprice) as myprice,sum(mycount) as mycount
from @t group by convert(varchar(7),mydate,120),myname
) a left join
(select convert(varchar(7),mydate,120) as mydate, myname,mybalance from @t a 
where (select count(*) from @t where myname = a.myname 
and convert(varchar(7),mydate,120) = convert(varchar(7),a.mydate,120)
and mydate > a.mydate) < 1
)b 
on a.myname = b.myname and a.mydate = b.mydate

Answer 4

谢谢各位，
hellwork 如果数据中有重复的，统计还是不准确，在你的测试数据上加一条
select 8,'2005-02-10',4,4,4,'U盘',那么统计出来，2月U盘就有 两条了
如下：
----创建测试数据
declare @t table(id int,mydate datetime,myprice int,mycount int,mybalance int, myname varchar(10))
insert @t
select 1,'2005-01-01',10,5,2,'MP3' union all
select 2,'2005-01-15',15,10,5,'U盘' union all
select 3,'2005-01-28',5,5,7,'MP3' union all
select 4,'2005-02-01',7,12,5,'MP3' union all
select 5,'2005-02-05',3,3,3,'U盘' union all
select 6,'2005-02-10',2,1,6,'U盘' union all
select 7,'2005-02-15',8,8,8,'MP3' union all
select 8,'2005-02-10',4,4,4,'U盘'     --注意这行数据和前面数据的 名称和时间都重复了
----按月对每个产品汇总
select a.*,b.mybalance from 
(select convert(varchar(7),mydate,120) as mydate,myname,sum(myprice) as myprice,sum(mycount) as mycount
from @t group by convert(varchar(7),mydate,120),myname
) a left join
(select convert(varchar(7),mydate,120) as mydate, myname,mybalance from @t a 
where (select count(*) from @t where myname = a.myname 
and convert(varchar(7),mydate,120) = convert(varchar(7),a.mydate,120)
and mydate > a.mydate) < 1
)b 
on a.myname = b.myname and a.mydate = b.mydate

Answer 5

是库存计算出的问题.
请楼主确认一下:如果名称和时间相同,是否将编号最大的行作为库存?如果是,则可以这样(只修改了库存计算的代码,请看代码中被修改部分的注释):
select a.*,b.mybalance from 
(select convert(varchar(7),mydate,120) as mydate,myname,sum(myprice) as myprice,sum(mycount) as mycount
from @t group by convert(varchar(7),mydate,120),myname
) a left join
(select convert(varchar(7),mydate,120) as mydate, myname,mybalance from @t a 
where (select count(*) from @t where myname = a.myname 
and convert(varchar(7),mydate,120) = convert(varchar(7),a.mydate,120)
and mydate >= a.mydate and id >a.id ) < 1      ----修改了这行,将mydate>a.mydate改为mydate >= a.mydate and id >a.id,当日期相同时,取最大编号的库存
)b 
on a.myname = b.myname and a.mydate = b.mydate
------------------------------------------------------------------------------------
请楼主再测试一下.

Answer 6

谢谢了，问题以解决。
不过 有 一个语句 不是很明白，望hellwork 指点。
select count(*) from @t where myname = a.myname 
and convert(varchar(7),mydate,120) = convert(varchar(7),a.mydate,120)
and mydate >= a.mydate and id >a.id ) < 1
中 convert(varchar(7),mydate,120) = convert(varchar(7),a.mydate,120)相等后
为何还要 mydate >= a.mydate 这个呢？

Answer 7

convert(varchar(7),mydate,120) = convert(varchar(7),a.mydate,120)是为了保证月份相同,因为楼主对每种产品的统计是按月进行的,相同月的同一种产品才能汇总.
mydate >= a.mydate and id > a.id是用来确定每个月中日期最大那一行作为库存.如果楼主的表中每个月每种产品mydate的日期值是随着id递增的话,就可以不用mydate>=a.mydate,而直接使用id > a.id.