时间对应多条记录

SELECT
A.*
,B.TotalWeight
,B.TotalMoney
FROM
(
 SELECT
 CONVERT(varchar(10),A.AccountDt,120) AS AccountDt
 ,A.[NetWeight]
 ,A.[Fee]
 FROM [BtProduceManage].[dbo].[Tst_Material] as A
 inner join Bse_Depart AS B ON B.Dept_Id=A.Dept_Id
 where  DATEDIFF(DAY,A.AccountDt,@strDtSt)<=0
   AND DATEDIFF(DAY,A.AccountDt,@strDtEd)>=0
   AND A.[State]=1
   AND A.[Material_Type]=3
   AND B.Fy_Id=@strFy
 group by
 A.[NetWeight]
 ,A.[Fee]
 ,CONVERT(varchar(10),A.AccountDt,120)
) AS A
INNER JOIN
(
 SELECT
 CONVERT(varchar(10),A.AccountDt,120) AS AccountDt
 ,SUM(A.[NetWeight]) AS TotalWeight
 ,SUM(A.[Fee]*A.[NetWeight]) AS TotalMoney
 FROM [BtProduceManage].[dbo].[Tst_Material] as A
 inner join Bse_Depart AS B ON B.Dept_Id=A.Dept_Id
 where  DATEDIFF(DAY,A.AccountDt,@strDtSt)<=0
   AND DATEDIFF(DAY,A.AccountDt,@strDtEd)>=0
   AND A.[State]=1
   AND A.[Material_Type]=3
   AND B.Fy_Id=@strFy
 group by
 CONVERT(varchar(10),A.AccountDt,120)
) AS B ON B.AccountDt=A.AccountDt
ORDER BY
A.AccountDT ASC

posted on 2010-11-17 14:11  prayforsmile  阅读(192)  评论()    收藏  举报