关于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这两个字段匹配的相似的查询没有问题，就这个不知道为什么不行￣￣！
...全文
257 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
关于SQL的学习记录（十、多表连接查询）
多表连接查询 数据存储在多表的原因： 为什么不用一个表存储数据，非要使用多个表呢，下面通过例子介绍其原因。 假设有如下结构的关系。 当一名学生考完某课后，其信息就会在student表中以一条记录的形式存储。下面列出student关系所存在的问题。 (1）数据冗余。某个学生可能考了多门课程，有多个成绩，所以该生的“学号”、“姓名”“性别”和“来源地”就会有多次重复,请参考表10.1所示。 (2）更新异常。由于数据冗余，如果要更改“张三”的来源地为“福建省”时，必须要更改多条记录，一旦遗忘了更改某条记录，“张
MySQL数据库案例实战教程：数据类型、语法与高级查询详解
这条 SQL 查询语句使用了左外连接（Left Outer Join），将表 websites（别名为 w）和 access_log（别名为 a）进行连接，并使用了 ifnull 函数来处理可能出现的 NULL 值。相比之下，指定具体的列名可以让数据库引擎更有效地执行查询，提高查询效率。子查询是指在 SQL 查询中嵌套使用的查询，将内部查询的结果作为外部查询的数据源之一。使用*通配符可能会返回多个不必要的列，包括一些不需要的敏感信息或者关联表中的数据，增加了数据传输的开销，并且也增加了处理结果集的复杂度。
套接字通信(C/C++ 多线程)----基于线程池的并发服务器
基于linux下的高并发服务器开发（第四章）- 多线程实现并发服务器_呵呵哒(￣▽￣)"的博客-CSDN博客https://blog.csdn.net/weixin_41987016/article/details/132026417?spm=1001.2014.3001.5501手写线程池 - C语言版 - 笔记总结_呵呵哒(￣▽￣)"的博客-CSDN博客https://blog.csdn.net/weixin_41987016/article/details/131926834?spm=1001.201
IT 软件开发最常用英语单词总汇
IT 软件开发最常用英语单词总汇前言：ABCDEFGHIJKLMNOPQRSTUVWX 前言： 各位同胞们，相信你们肯定会有和我一样的苦恼——（学习新知识，代码报错了）英语貌似看不懂呀(￣y▽,￣)╭ ，稳住，别慌！！！当当当当，这是我整理的在IT软件开发中最常用的英语单词，希望能够帮助到大家！O(∩_∩)O A abstract 抽象的 abstract base class (ABC)抽象...
SQL进阶命令，进阶教程，W3C总结
SQL进阶教程 本文总结W3CSchool上SQL的所有进阶命令与教程，基础教程的总结在上一篇，喜欢的朋友可以留着备用，谢谢 一、JOIN关键字 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。 1、Join 和 Key 有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。 数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这...
疑难问题

22,300

社区成员

121,734

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章