关于FULL JOIN连接查询的问题 ̄ ̄!

DumDum 2008-09-09 04:21:51
现在有两个表需要用到FULL JOIN连接查询,一个表是年销售计划,就是一年12个月那个月那个部门销售某个商品多少,另一个表销售明细表,就是在某一时间,那个部门卖闻某个商品多少。下面是我的查询


select isnull(a.syear,b.syear) as syear,
isnull(a.DepartmentID,b.DepartmentID) as DepartmentID,
isnull(a.CommodityID,b.CommodityID) as CommodityID
from
(select sYear ,DepartmentID,CommodityID,
sum(case when sMOnth =1 then isnull(quantity,0) else 0 end) as P01,
sum(case when sMOnth =2 then isnull(quantity,0) else 0 end) as P02,
sum(case when sMOnth =3 then isnull(quantity,0) else 0 end) as P03,
sum(case when sMOnth =4 then isnull(quantity,0) else 0 end) as P04,
sum(case when sMOnth =5 then isnull(quantity,0) else 0 end) as P05,
sum(case when sMOnth =6 then isnull(quantity,0) else 0 end) as P06,
sum(case when sMOnth =7 then isnull(quantity,0) else 0 end) as P07,
sum(case when sMOnth =8 then isnull(quantity,0) else 0 end) as P08,
sum(case when sMOnth =9 then isnull(quantity,0) else 0 end) as P09,
sum(case when sMOnth =10 then isnull(quantity,0) else 0 end) as P10,
sum(case when sMOnth =11 then isnull(quantity,0) else 0 end) as P11,
sum(case when sMOnth =12 then isnull(quantity,0) else 0 end) as P12
from tSalePlan
group by sYear,DepartmentID,CommodityID
) as a
full join
(select year(sDate) as sYear ,DepartmentID,CommodityID,
sum(case when month(sDate) =1 then isnull(quantity,0) else 0 end) as S01,
sum(case when month(sDate) =2 then isnull(quantity,0) else 0 end) as S02,
sum(case when month(sDate) =3 then isnull(quantity,0) else 0 end) as S03,
sum(case when month(sDate) =4 then isnull(quantity,0) else 0 end) as S04,
sum(case when month(sDate) =5 then isnull(quantity,0) else 0 end) as S05,
sum(case when month(sDate) =6 then isnull(quantity,0) else 0 end) as S06,
sum(case when month(sDate) =7 then isnull(quantity,0) else 0 end) as S07,
sum(case when month(sDate) =8 then isnull(quantity,0) else 0 end) as S08,
sum(case when month(sDate) =9 then isnull(quantity,0) else 0 end) as S09,
sum(case when month(sDate) =10 then isnull(quantity,0) else 0 end) as S10,
sum(case when month(sDate) =11 then isnull(quantity,0) else 0 end) as S11,
sum(case when month(sDate) =12 then isnull(quantity,0) else 0 end) as S12
from tSaleOrder
group by year(sDate),DepartmentID,CommodityID) as b
on a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityID
order by a.sYear,a.DepartmentID,a.CommodityID


里面的单个查询没有问题,现在FULL JOin连接查询后,SYear,DepartmentID,CommodityID这三个字段能匹配的数据没有合并,被分拆成多条数据了 ̄ ̄!不知道为什么 ̄ ̄!那个大哥帮看看 ̄ ̄!
我另有一个DepartmentID,CommodityID这两个字段匹配的相似的查询没有问题,就这个不知道为什么不行 ̄ ̄!
...全文
255 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
DumDum 2008-09-09
  • 打赏
  • 举报
回复
不过试试另外两个有没有空格了
DumDum 2008-09-09
  • 打赏
  • 举报
回复
Syear都是int的
Herb2 2008-09-09
  • 打赏
  • 举报
回复
on a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityID
===》
on rtrim(a.sYear) = rtrim(b.sYear)
and ltrim(rtrim(a.DepartmentID))=ltrim(rtrim(b.DepartmentID))
and ltrim(rtrim(a.CommodityID)) =ltrim(rtrim(b.CommodityID))
Garnett_KG 2008-09-09
  • 打赏
  • 举报
回复

但是结果比如:
年份  部门ID  商品ID 1  2  3  4  5  6  7  8  9  10  11  12
----------------------------------------------------------------------------------
2007 A0001 G0001 236 0 0 0 0 0 0 0 0 0 0 0
2007 A0001 G0001 0 360 0 0 0 0 0 0 0 0 0 0
2007 A0001 G0001 0 0 120 0 0 0 0 0 0 0 0 0
2007 A0001 G0001 0 0 0 360 0 0 0 0 0 0 0   0
---------------------------------------------------------------------------------------

哦..

a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityID

应该是有这三个条件有看起来值相同但不匹配的资料了

慢慢查吧。
Herb2 2008-09-09
  • 打赏
  • 举报
回复
还有就是你的部门ID和产品ID 的类型是否一个是char型,另一个是varchar型
Herb2 2008-09-09
  • 打赏
  • 举报
回复
1、(select sYear ,DepartmentID,CommodityID,
2、(select year(sDate) as sYear ,DepartmentID,CommodityID,
确定你1中的syear是否是 int类型,因为2中的year(sDate) 肯定是int类型的


DumDum 2008-09-09
  • 打赏
  • 举报
回复
我说的分拆的意思是存在多条SYear,DepartmentID,CommodityID一样,只是销售在不同的月份里面,
select isnull(a.syear,b.syear) as syear,
isnull(a.DepartmentID,b.DepartmentID) as DepartmentID,
isnull(a.CommodityID,b.CommodityID) as CommodityID
a.*,b.*
isnull(a.P01,0) - isnull(b.S01,0) as R01,
isnull(a.P02,0) - isnull(b.S01,0) as R02,
isnull(a.P03,0) - isnull(b.S01,0) as R03,
isnull(a.P04,0) - isnull(b.S01,0) as R04,
isnull(a.P05,0) - isnull(b.S01,0) as R05,
isnull(a.P06,0) - isnull(b.S01,0) as R06,
isnull(a.P07,0) - isnull(b.S01,0) as R07,
isnull(a.P08,0) - isnull(b.S01,0) as R08,
isnull(a.P09,0) - isnull(b.S01,0) as R09,
isnull(a.P10,0) - isnull(b.S01,0) as R10,
isnull(a.P11,0) - isnull(b.S01,0) as R11,
isnull(a.P12,0) - isnull(b.S01,0) as R12
from
(select sYear ,DepartmentID,CommodityID,
sum(case when sMOnth =1 then isnull(quantity,0) else 0 end) as P01,
sum(case when sMOnth =2 then isnull(quantity,0) else 0 end) as P02,
sum(case when sMOnth =3 then isnull(quantity,0) else 0 end) as P03,
sum(case when sMOnth =4 then isnull(quantity,0) else 0 end) as P04,
sum(case when sMOnth =5 then isnull(quantity,0) else 0 end) as P05,
sum(case when sMOnth =6 then isnull(quantity,0) else 0 end) as P06,
sum(case when sMOnth =7 then isnull(quantity,0) else 0 end) as P07,
sum(case when sMOnth =8 then isnull(quantity,0) else 0 end) as P08,
sum(case when sMOnth =9 then isnull(quantity,0) else 0 end) as P09,
sum(case when sMOnth =10 then isnull(quantity,0) else 0 end) as P10,
sum(case when sMOnth =11 then isnull(quantity,0) else 0 end) as P11,
sum(case when sMOnth =12 then isnull(quantity,0) else 0 end) as P12
from tSalePlan
group by sYear,DepartmentID,CommodityID
) as a
full join
(select year(sDate) as sYear ,DepartmentID,CommodityID,
sum(case when month(sDate) =1 then isnull(quantity,0) else 0 end) as S01,
sum(case when month(sDate) =2 then isnull(quantity,0) else 0 end) as S02,
sum(case when month(sDate) =3 then isnull(quantity,0) else 0 end) as S03,
sum(case when month(sDate) =4 then isnull(quantity,0) else 0 end) as S04,
sum(case when month(sDate) =5 then isnull(quantity,0) else 0 end) as S05,
sum(case when month(sDate) =6 then isnull(quantity,0) else 0 end) as S06,
sum(case when month(sDate) =7 then isnull(quantity,0) else 0 end) as S07,
sum(case when month(sDate) =8 then isnull(quantity,0) else 0 end) as S08,
sum(case when month(sDate) =9 then isnull(quantity,0) else 0 end) as S09,
sum(case when month(sDate) =10 then isnull(quantity,0) else 0 end) as S10,
sum(case when month(sDate) =11 then isnull(quantity,0) else 0 end) as S11,
sum(case when month(sDate) =12 then isnull(quantity,0) else 0 end) as S12
from tSaleOrder
group by year(sDate),DepartmentID,CommodityID) as b
on a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityID
order by a.sYear,a.DepartmentID,a.CommodityID

但是结果比如:
年份  部门ID  商品ID 1  2  3  4  5  6  7  8  9  10  11  12
----------------------------------------------------------------------------------
2007 A0001 G0001 236 0 0 0 0 0 0 0 0 0 0 0
2007 A0001 G0001 0 360 0 0 0 0 0 0 0 0 0 0
2007 A0001 G0001 0 0 120 0 0 0 0 0 0 0 0 0
2007 A0001 G0001 0 0 0 360 0 0 0 0 0 0 0   0
---------------------------------------------------------------------------------------
DumDum 2008-09-09
  • 打赏
  • 举报
回复
计划里面有的可能销售里面一次也没有销售过,而销售里面有的但是可能又没有做计划,所以两个表的数据是都需要的,inner join达不到要求 ̄ ̄!
中国风 2008-09-09
  • 打赏
  • 举报
回复
被分拆成多条数据
------------
一對多數據

改為union all連接兩個結果集
子陌红尘 2008-09-09
  • 打赏
  • 举报
回复
那就确认一下a.sYear=b.sYear 这部分外联接条件是否存在问题,注意字段类型的差异。
Garnett_KG 2008-09-09
  • 打赏
  • 举报
回复

你的tSaleOrder 、tSalePlan 的sDate是不是不止一年的数据量哦?

--加个 sDate between '20080101' AND '20081231' 的条件试试

select isnull(a.syear,b.syear) as syear,
isnull(a.DepartmentID,b.DepartmentID) as DepartmentID,
isnull(a.CommodityID,b.CommodityID) as CommodityID
from
(select sYear ,DepartmentID,CommodityID,
sum(case when sMOnth =1 then isnull(quantity,0) else 0 end) as P01,
sum(case when sMOnth =2 then isnull(quantity,0) else 0 end) as P02,
sum(case when sMOnth =3 then isnull(quantity,0) else 0 end) as P03,
sum(case when sMOnth =4 then isnull(quantity,0) else 0 end) as P04,
sum(case when sMOnth =5 then isnull(quantity,0) else 0 end) as P05,
sum(case when sMOnth =6 then isnull(quantity,0) else 0 end) as P06,
sum(case when sMOnth =7 then isnull(quantity,0) else 0 end) as P07,
sum(case when sMOnth =8 then isnull(quantity,0) else 0 end) as P08,
sum(case when sMOnth =9 then isnull(quantity,0) else 0 end) as P09,
sum(case when sMOnth =10 then isnull(quantity,0) else 0 end) as P10,
sum(case when sMOnth =11 then isnull(quantity,0) else 0 end) as P11,
sum(case when sMOnth =12 then isnull(quantity,0) else 0 end) as P12
from tSalePlan
group by sYear,DepartmentID,CommodityID
) as a
full join
(select year(sDate) as sYear ,DepartmentID,CommodityID,
sum(case when month(sDate) =1 then isnull(quantity,0) else 0 end) as S01,
sum(case when month(sDate) =2 then isnull(quantity,0) else 0 end) as S02,
sum(case when month(sDate) =3 then isnull(quantity,0) else 0 end) as S03,
sum(case when month(sDate) =4 then isnull(quantity,0) else 0 end) as S04,
sum(case when month(sDate) =5 then isnull(quantity,0) else 0 end) as S05,
sum(case when month(sDate) =6 then isnull(quantity,0) else 0 end) as S06,
sum(case when month(sDate) =7 then isnull(quantity,0) else 0 end) as S07,
sum(case when month(sDate) =8 then isnull(quantity,0) else 0 end) as S08,
sum(case when month(sDate) =9 then isnull(quantity,0) else 0 end) as S09,
sum(case when month(sDate) =10 then isnull(quantity,0) else 0 end) as S10,
sum(case when month(sDate) =11 then isnull(quantity,0) else 0 end) as S11,
sum(case when month(sDate) =12 then isnull(quantity,0) else 0 end) as S12
from tSaleOrder
where sDate between '20080101' AND '20081231'
group by year(sDate),DepartmentID,CommodityID) as b
on a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityID
order by a.sYear,a.DepartmentID,a.CommodityID



水族杰纶 2008-09-09
  • 打赏
  • 举报
回复
Inner join

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧