UC知道sql 按月 如何多表联合查询？
来源：百度知道 编辑：UC知道 时间：2024/05/24 20:29:23
每个表：
Card 表中有addtime,cardmoney字段
CardFillDate 表中有filldate,fillmoney字段
CardFill 表中有filldate,fillmoney字段
CardReplace 表中有replacedate,replacemoney字段
CardRecycle 表中有recycledate,returnmoney字段
Mainsavetable 表中有outdate,chargemoney字段
我想要的查询结果：
月份 发行收费 延期收费 充值收费 更换收费 回收退费 出场收费 小计
01 100 50 200 150 500 500 150
我的查询语句不对呀，请指正！
select month(card.addtime) as '月份', sum(card.CardMoney) 发行收费,sum(cfd.FillMoney) 延期收费, sum(cf.FillMoney) 充值收费,
sum(ReplaceMoney) 更换收费, sum(ReturnMoney) 回收退费,sum(ChargeMoney) 出场收费
from Card,CardFillDate cfd,CardFill cf,CardReplace,CardRecycle,Mainsavetable
where year(card.addtime) = '2009' group by month(card.addtime) order by '月份'
这6个表中分别都有一个cardid字段作为关联，汗……
而且时间上来说我写的也不对，不应该用select month(card.addtime)
select a.yd,cardmoney,c.fillmoney,d.fillmoney as fillmoney1,replacemoney,returnmoney,chargemoney from (
select '1' as yd union select '2' union select '3' union
select '5' union select '6' union select '7' union
select '8' union select '9' union select '10' union
select '11' union select '12') a
left join
(select month(addtime)as yd,sum(cardmoney)as cardmoney from Card where year(addtime) = '2009' group by month(addtime)) b
on a.yd=b.yd
left join
(select month(filldate)as yd,sum(fillmoney)as fillmoney from CardFillDate where year(filldate) = '2009' group by month(filldate)) c
on a.yd=c.yd
left join
(select month(filldate)as yd,sum(fillmoney)as fillmoney from CardFill where year(filldate) = '2009' group by month(filldate)) d
on a.yd=d.yd
left join
(select month(replacedate)as yd,sum(replacemoney)