求一SQL语句

[wei123456]

表结构如下:

表DiningTime
DType BeginTime EndTime
早餐 8:00 9:00
午餐 11:30 13:00
晚餐 17:30 19:00
宵夜 21:30 22:30

表Person
PID PName
C001 www
C002 qqq
C003 eee
.... ...

表Meal
PID MealTime MealMoney
C001 2005-09-25 08:12:00 5
C001 2005-09-25 08:15:00 5
C002 2005-09-25 08:30:01 10
C001 2005-09-25 11:45:00 8
C003 2005-09-25 18:01:00 7
C002 2005-09-26 12:10:00 10
.... ................... ...

想产生如下结果:
(时间) (次数) (金额)
MealTime DType PID PName MealNumber MealMoney
2005-09-25 早餐 C001 www 2 10
2005-09-25 早餐 C002 qqq 1 10
2005-09-25 早餐 C003 eee 0 0
..........
2005-09-25 午餐 C001 www 1 8
2005-09-25 午餐 C002 qqq 0 0
2005-09-25 午餐 C003 eee 0 0
..........
2005-09-25 晚餐 C001 www 0 0
2005-09-25 晚餐 C002 qqq 0 0
2005-09-25 晚餐 C003 eee 1 7
..........
2005-09-25 宵夜 C001 www 0 0
2005-09-25 宵夜 C002 qqq 0 0
2005-09-25 宵夜 C003 eee 0 0
..........
2005-09-26 早餐 C001 www 0 0
2005-09-26 早餐 C002 qqq 0 0
2005-09-26 早餐 C003 eee 0 0
..........
2005-09-26 午餐 C001 www 0 0
2005-09-26 午餐 C002 qqq 0 10
2005-09-26 午餐 C003 eee 0 0
..........
2005-09-26 晚餐 C001 www 0 0
2005-09-26 晚餐 C002 qqq 0 0
2005-09-26 晚餐 C003 eee 0 0
..........
2005-09-26 宵夜 C001 www 0 0
2005-09-26 宵夜 C002 qqq 0 0
2005-09-26 宵夜 C003 eee 0 0
...........
(我就是想统计一下每天的餐别消费情况,哪些人有消费,哪些人无消费,消费的人消费了多少次,消费了多少钱)

另:如果觉得一条SQL语句解决不了,存储过程都行,总之不限方法,但速度要快,因为这些记录一天会有上万条,如果觉得分不够的话,加分就是,谢谢,谢谢!

[子陌红尘]

select
c.MealTime,
b.DType,
a.PID,
a.PName,
MealNumber = (case d.MealTime is null then 0 else 1 end),
MealMoneysum = sum(d.MealMoney)
from
Person a
cross join
DiningTime b
cross join
(select MealTime=convert(char(10),MealTime,120) from Meal group by convert(char(10),MealTime,120))
left join
Meal d
on
a.PID = d.PID
and
convert(char(5),d.MealTime,108) between right('0'+b.BeginTime,5) and right('0'+b.EndTime,5)
and
c.MealTime = convert(char(10),d.MealTime,120)
group by
c.MealTime,b.DType,a.PID,a.PName
order by
c.MealTime,b.DType,a.PID,a.PName

[$扫地僧$]

select CONVERT(char(10),T_Meal.MealTime,120) as MealTime,T_Meal.DType,person.PID,person.PName,count(person.PID) as MealNumber,sum(T_Meal.MealMoney) as MealMoney
from person ,(select DiningTime.DType,Meal.* from DiningTime,Meal where CAST(CONVERT(char(5),Meal.MealTime,108) as datetime) BETWeen DiningTime.BeginTime and DiningTime.EndTime) T_Meal
where T_Meal.PID=person.PID
group by CONVERT(char(10),T_Meal.MealTime,120),T_Meal.DType,person.PID,person.PName

[zjcxc]

SELECT
MealTime=CONVERT(char(10),A.MealTime,120),
DType=C.DType,
PID=A.PID,
PName=B.PName,
MealNumber=COUNT(A.PID),
MealMoney=SUM(A.MealMoney)
FROM Person b
LEFT JOIN Meal A
ON A.PID=B.PID
LEFT JOIN DiningTime C
ON CAST(CONVERT(char(5),MealTime,108) as datetime)
BETWEEN C.BeginTime AND C.EndTime
GROUP BY CONVERT(char(10),A.MealTime,120), C.DType, A.PID, B.PName

[vivianfdlpw]

select convert(char(10),A.MealTime,120) as 'MealTime'
,C.DType
,A.PID
,B.PName
,count(1) as 'MealNumber'
,sum(A.MealMoney) as 'MealMoney'
from Meal A
join Person B on A.PID=B.PID
join DiningTime C on A.MealTime
between (convert(char(10),A.MealTime,120)+' '+C.BeginTime)
and
(convert(char(10),A.MealTime,120)+' '+C.EndTime)
group by convert(char(10),A.MealTime,120)
,C.DType
,A.PID
,B.PName
order by MealTime,C.DType,A.PID

[wei123456]

谢谢各位,因发完贴后随即出差,至今方回,现马上测,之后便给分..............

[wei123456]

虽然结果与我想象中还差不少,但已经够了,谢谢各位