select cl.cCCName, sum(s.imoney) as '2月'-- from dispatchLists s,--
dispatchList d ,--
customer c ,--
CustomerClass Cl,--
Inventory I
where d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate)=2
and I.cInvCode=s.cInvCode group by cl.cCCName order by sum(s.imoney)desc
---第二条 就是在原来的基础上加上了3月的数据求和select cl.cCCName, sum(s.imoney) as '2月',sum(s1.imoney) as '3月' from dispatchLists s,dispatchLists s1,
dispatchList d ,dispatchList d1,
customer c ,customer c1,
CustomerClass Cl,CustomerClass Cl1 ,
Inventory I,Inventory I1where d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate)=2
and I.cInvCode=s.cInvCode and
(d1.dlid=s1.dlid
and d1.cCusCode=c1.cCusCode
and c1.cCCCode=Cl1.cCCCode
and month(d1.dDate)= 3
and I1.cInvCode=s1.cInvCode)group by cl.cCCName order by sum(s.imoney)desc 问题:两个表生成的 2月 的数据结果不同,为什么.我就奇了怪了.
请高手帮助呀.
dispatchList d ,--
customer c ,--
CustomerClass Cl,--
Inventory I
where d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate)=2
and I.cInvCode=s.cInvCode group by cl.cCCName order by sum(s.imoney)desc
---第二条 就是在原来的基础上加上了3月的数据求和select cl.cCCName, sum(s.imoney) as '2月',sum(s1.imoney) as '3月' from dispatchLists s,dispatchLists s1,
dispatchList d ,dispatchList d1,
customer c ,customer c1,
CustomerClass Cl,CustomerClass Cl1 ,
Inventory I,Inventory I1where d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate)=2
and I.cInvCode=s.cInvCode and
(d1.dlid=s1.dlid
and d1.cCusCode=c1.cCusCode
and c1.cCCCode=Cl1.cCCCode
and month(d1.dDate)= 3
and I1.cInvCode=s1.cInvCode)group by cl.cCCName order by sum(s.imoney)desc 问题:两个表生成的 2月 的数据结果不同,为什么.我就奇了怪了.
请高手帮助呀.
dispatchList d ,dispatchList d1,
customer c ,customer c1,
CustomerClass Cl,CustomerClass Cl1 ,
Inventory I,Inventory I1where (d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate)=2
and I.cInvCode=s.cInvCode
)
or
(d1.dlid=s1.dlid
and d1.cCusCode=c1.cCusCode
and c1.cCCCode=Cl1.cCCCode
and month(d1.dDate)= 3
and I1.cInvCode=s1.cInvCode)group by cl.cCCName order by sum(s.imoney)desc
dispatchList d ,dispatchList d1,
customer c ,customer c1,
CustomerClass Cl,CustomerClass Cl1 ,
Inventory I,Inventory I1where d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate)=2
and I.cInvCode=s.cInvCode and d.dlid<>d1.dlid
and
(d1.dlid=s1.dlid
and d1.cCusCode=c1.cCusCode
and c1.cCCCode=Cl1.cCCCode
and month(d1.dDate)= 3
and I1.cInvCode=s1.cInvCode)group by cl.cCCName order by sum(s.imoney)desc 也不行
说明:我是想
如何将多个表中的某一列,按不同条件,查询并产生结果后,显示成一个表的不同的列
上边的就是多个表.
希望大家帮一下.
sum(case when month(d.dDate)=2 then s.imoney end) as '2月',
sum(case when month(d.dDate)=3 then s.imoney end) as '3月' from dispatchLists s,--
dispatchList d ,--
customer c ,--
CustomerClass Cl,--
Inventory I
where d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate) in (2,3)
and I.cInvCode=s.cInvCode
您这个语句,我在查询分析器中执行了两分钟了,还没也来.
帮帮忙呀.
sum(case when month(d.dDate)=2 then s.imoney else 0 end) as '2月',
sum(case when month(d.dDate)=3 then s.imoney else 0 end) as '3月' from dispatchLists s,--
dispatchList d ,--
customer c ,--
CustomerClass Cl,--
Inventory I
where d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate) in (2,3)
and I.cInvCode=s.cInvCode
select cl.cCCName,
sum(CASE MONTH(d.dDate) WHEN 2 THEN [s.imoney] ELSE 0 END) as '2月',
sum(CASE MONTH(d.dDate) WHEN 3 THEN [s.imoney] ELSE 0 END) as '3月' from dispatchLists s,
dispatchList d,
customer c,
CustomerClass Cl,
Inventory Iwhere d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate)=2
and I.cInvCode=s.cInvCode
group by cl.cCCName order by sum(s.imoney)desc
select cl.cCCName,
sum(case when month(d.dDate)=2 then s.imoney ELSE 0 end) as '2月',
sum(case when month(d.dDate)=3 then s.imoney ELSE 0 end) as '3月',
sum(case when month(d.dDate)=4 then s.imoney ELSE 0 end) as '4月',
sum(case when month(d.dDate)=8 then s.imoney ELSE 0 end) as '8月' from dispatchLists s,--
dispatchList d ,--
customer c ,--
CustomerClass Cl,--
Inventory I
where d.dlid=s.dlid
and d.cCusCode=c.cCusCode
and c.cCCCode=Cl.cCCCode
and month(d.dDate) in (2,3,4,8)
and I.cInvCode=s.cInvCodegroup by cl.cCCName order by sum(s.imoney)desc
谢谢