select C.MDate ,B.MType ,A.PID ,A.PName ,isnull(C.Mnum,0) as 'Mnum' ,isnull(C.MMoney,0) as 'MMoney' from Person A left (select distinct MType from Meal) B on 1>0 left join Meal C on A.PID=C.PID group by A.PID,C.MDate,B.MType
忘了加order by:select C.MDate ,B.MType ,A.PID ,A.PName ,isnull(C.Mnum,0) as 'Mnum' ,isnull(C.MMoney,0) as 'MMoney' from Person A left (select distinct MType from Meal) B on 1>0 left join Meal C on A.PID=C.PID group by A.PID,C.MDate,B.MType order by C.MDate,B.MType,A.PID
left (select distinct MType from Meal) B on 1>0 这样写可以吗?
group by A.PID,C.MDate,B.MType 这样也行吗?
create table person (PID varchar(10),PName varchar(10),Psec varchar(10),PAge int) insert person select '001','wang','m',20 insert person select '002','li','w',23 insert person select '003','zhang','w',19create table Meal (MDate smalldatetime,MType varchar(10),PID varchar(10),PName varchar(10),Mnum int, MMoney int) insert Meal select '2005-09-26','早餐','001','wang',2,10 insert Meal select '2005-09-26','早餐','003','zhang',1,3 insert Meal select '2005-09-26','午餐','001','wang',1,15 insert Meal select '2005-09-26','晚餐','002','li',2,7 insert Meal select '2005-09-27','午餐','002','wang',1,8 insert Meal select '2005-09-27','宵夜','003','zhang',3,10 select T_Meal.MDate,T_Meal.MType,T_Meal.PID,T_Meal.PName,isnull(Meal.Mnum,0) as Mnum ,isnull(Meal.MMoney,0) as MMoney from Meal, (select * from (select T_MDate.*,T_MType.*,T_PID.* from (select MDate from Meal group by MDate) T_MDate, (select MType from Meal group by MType ) T_MType, (select PID,PName from person group by PID,PName) T_PID) T) T_Meal where cast(T_Meal.MDate as varchar)+T_Meal.MType+T_Meal.PID+T_Meal.PName *=cast(Meal.MDate as varchar)+Meal.MType+Meal.PID+Meal.PName order by T_Meal.MDate,(case T_Meal.MType when '早餐' then 1 when '午餐' then 2 when '晚餐' then 3 when '宵夜' then 4 end),T_Meal.PID,T_Meal.PName
select C.MDate ,B.MType ,A.PID ,A.PName ,isnull(C.Mnum,0) as 'Mnum' ,isnull(C.MMoney,0) as 'MMoney' from Person A left join(select distinct MType from Meal) B on 1>0 left join Meal C on A.PID=C.PID group by A.PID,C.MDate,B.MType order by C.MDate,B.MType,A.PID
select C.MDate ,B.MType ,A.PID ,A.PName ,isnull(C.Mnum,0) as 'Mnum' ,isnull(C.MMoney,0) as 'MMoney' from Person A left (select distinct MType from Meal) B on 1>0 left join Meal C on A.PID=C.PID group by A.PID,C.MDate,B.MType order by C.MDate,B.MType,A.PID
select a.mdate ,a.mtype ,a.pid ,a.pname ,isnull(b.mnum,0) ,isnull(b.Mmoney,0) from (select * from person a left join (Select distinct mtype from meal)B on 1>0 left join (Select distinct mdate from meal) C on 1>0)a left join meal b on b.pid=a.pid and b.mtype=a.mtype and b.mdate=a.mdate
--不好意思,记了加order by select a.mdate ,a.mtype ,a.pid ,a.pname ,isnull(b.mnum,0) ,isnull(b.Mmoney,0) from (select * from person a left join (Select distinct mtype from meal)B on 1>0 left join (Select distinct mdate from meal) C on 1>0)a left join meal b on b.pid=a.pid and b.mtype=a.mtype and b.mdate=a.mdate order by a.mdate,a.mtype,a.pid
,B.MType
,A.PID
,A.PName
,isnull(C.Mnum,0) as 'Mnum'
,isnull(C.MMoney,0) as 'MMoney'
from Person A
left (select distinct MType from Meal) B on 1>0
left join Meal C on A.PID=C.PID
group by A.PID,C.MDate,B.MType
,B.MType
,A.PID
,A.PName
,isnull(C.Mnum,0) as 'Mnum'
,isnull(C.MMoney,0) as 'MMoney'
from Person A
left (select distinct MType from Meal) B on 1>0
left join Meal C on A.PID=C.PID
group by A.PID,C.MDate,B.MType
order by C.MDate,B.MType,A.PID
这样写可以吗?
这样也行吗?
insert person select '001','wang','m',20
insert person select '002','li','w',23
insert person select '003','zhang','w',19create table Meal (MDate smalldatetime,MType varchar(10),PID varchar(10),PName varchar(10),Mnum int, MMoney int)
insert Meal select '2005-09-26','早餐','001','wang',2,10
insert Meal select '2005-09-26','早餐','003','zhang',1,3
insert Meal select '2005-09-26','午餐','001','wang',1,15
insert Meal select '2005-09-26','晚餐','002','li',2,7
insert Meal select '2005-09-27','午餐','002','wang',1,8
insert Meal select '2005-09-27','宵夜','003','zhang',3,10
select T_Meal.MDate,T_Meal.MType,T_Meal.PID,T_Meal.PName,isnull(Meal.Mnum,0) as Mnum ,isnull(Meal.MMoney,0) as MMoney
from Meal,
(select * from (select T_MDate.*,T_MType.*,T_PID.* from
(select MDate from Meal group by MDate) T_MDate,
(select MType from Meal group by MType ) T_MType,
(select PID,PName from person group by PID,PName) T_PID) T) T_Meal
where
cast(T_Meal.MDate as varchar)+T_Meal.MType+T_Meal.PID+T_Meal.PName *=cast(Meal.MDate as varchar)+Meal.MType+Meal.PID+Meal.PName
order by T_Meal.MDate,(case T_Meal.MType when '早餐' then 1 when '午餐' then 2 when '晚餐' then 3 when '宵夜' then 4 end),T_Meal.PID,T_Meal.PName
,B.MType
,A.PID
,A.PName
,isnull(C.Mnum,0) as 'Mnum'
,isnull(C.MMoney,0) as 'MMoney'
from Person A
left join(select distinct MType from Meal) B on 1>0
left join Meal C on A.PID=C.PID
group by A.PID,C.MDate,B.MType
order by C.MDate,B.MType,A.PID
,B.MType
,A.PID
,A.PName
,isnull(C.Mnum,0) as 'Mnum'
,isnull(C.MMoney,0) as 'MMoney'
from Person A
left (select distinct MType from Meal) B on 1>0
left join Meal C on A.PID=C.PID
group by A.PID,C.MDate,B.MType
order by C.MDate,B.MType,A.PID
,a.mtype
,a.pid
,a.pname
,isnull(b.mnum,0)
,isnull(b.Mmoney,0) from
(select * from person a
left join (Select distinct mtype from meal)B on 1>0
left join (Select distinct mdate from meal) C on 1>0)a
left join meal b on b.pid=a.pid and b.mtype=a.mtype and b.mdate=a.mdate
select a.mdate
,a.mtype
,a.pid
,a.pname
,isnull(b.mnum,0)
,isnull(b.Mmoney,0) from
(select * from person a
left join (Select distinct mtype from meal)B on 1>0
left join (Select distinct mdate from meal) C on 1>0)a
left join meal b on b.pid=a.pid and b.mtype=a.mtype and b.mdate=a.mdate
order by a.mdate,a.mtype,a.pid