在线等，关于两表的连接 较难的问题，请教高手

hksl 2008-07-05 12:13:38
A采购表
billdate,billno,inputtime, companyID,prodID,qty
2008-7-1,10001,2008-7-1 12:00:51,101,1,100
2008-7-2,10002,2008-7-2 11:01:06,101,1,49
2008-7-2,10003,2008-7-2 12:01:27,101,1,43
2008-7-3,10004,2008-7-3 12:01:55,101,1,230

B表 销售汇总表

billdate,companyID,prodID,qty
2008-7-1,101,1,20
2008-7-2,101,1,40
2008-7-3,101,1,100


现在需要得到这样的报表
A.billdate,A.billno,companyID,prodID,A.qty,B.qty
2008-7-1,10001,101,1,100,20
2008-7-2,10002,101,1,49,0
2008-7-2,10003,101,1,43,40
2008-7-3,10004,101,1,230,100
...全文
142 18 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
happysophie 2008-07-08
  • 打赏
  • 举报
 回复
奇怪，怎么发两次？
happysophie 2008-07-08
  • 打赏
  • 举报
 回复
hery的写法很值得学习！
根据hery的写法，获得9楼要求。
select coalesce(a.billdate,b.billdate) as billdate,a.billno,inputtime,coalesce(a.companyID,b.companyID) as companyID,coalesce(a.prodID,b.prodID),isnull(a.qty,0) as a_qty,isnull(b.qty,0) as b_qty

from @a a 

full outer join @b b on a.billdate=b.billdate and a.companyID=b.companyID and a.prodID=b.prodID

WHERE NOT EXISTS(

           SELECT 1 FROM @A T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID] 

           AND T.[inputtime]>A.[inputtime])



union all

select coalesce(a.billdate,b.billdate) as billdate,a.billno,inputtime,coalesce(a.companyID,b.companyID) as companyID,coalesce(a.prodID,b.prodID),isnull(a.qty,0) as a_qty,0

from @a a 

full outer join @b b on a.billdate=b.billdate and a.companyID=b.companyID and a.prodID=b.prodID

WHERE EXISTS(SELECT 1 FROM @A T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID] 

           AND T.[inputtime]>A.[inputtime])



order by billdate,inputtime


happysophie 2008-07-08
  • 打赏
  • 举报
 回复
hery的写法很值得学习！
根据hery的写法，获得9楼要求。
select coalesce(a.billdate,b.billdate) as billdate,a.billno,inputtime,coalesce(a.companyID,b.companyID) as companyID,coalesce(a.prodID,b.prodID),isnull(a.qty,0) as a_qty,isnull(b.qty,0) as b_qty

from @a a 

full outer join @b b on a.billdate=b.billdate and a.companyID=b.companyID and a.prodID=b.prodID

WHERE NOT EXISTS(

           SELECT 1 FROM @A T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID] 

           AND T.[inputtime]>A.[inputtime])



union all

select coalesce(a.billdate,b.billdate) as billdate,a.billno,inputtime,coalesce(a.companyID,b.companyID) as companyID,coalesce(a.prodID,b.prodID),isnull(a.qty,0) as a_qty,0

from @a a 

full outer join @b b on a.billdate=b.billdate and a.companyID=b.companyID and a.prodID=b.prodID

WHERE EXISTS(SELECT 1 FROM @A T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID] 

           AND T.[inputtime]>A.[inputtime])



order by billdate,inputtime


hero_girl 2008-07-08
  • 打赏
  • 举报
 回复
呵呵，进来MARK下，免得以后什么都不懂，哈哈
pt1314917 2008-07-05
  • 打赏
  • 举报
 回复 


--> 测试数据: @A

declare @A table (billdate datetime,billno int,inputtime datetime,companyID int,prodID int,qty int)

insert into @A

select '2008-7-1',10001,'2008-7-1 12:00:51',101,1,100 union all

select '2008-7-2',10002,'2008-7-2 11:01:06',101,1,49 union all

select '2008-7-2',10003,'2008-7-2 12:01:27',101,1,43 union all

select '2008-7-3',10004,'2008-7-3 12:01:55',101,1,230

--> 测试数据: @B

declare @B table (billdate datetime,companyID int,prodID int,qty int)

insert into @B

select '2008-7-1',101,1,20 union all

select '2008-7-2',101,1,40 union all

select '2008-7-3',101,1,100



select a.billdate,a.billno,a.companyid,a.prodid,

a.qty,isnull(b.qty,0) from @A a left join @b b

on a.billdate=b.billdate 

and not exists(select 1 from @a where billdate=a.billdate and inputtime>a.inputtime)
hksl 2008-07-05
  • 打赏
  • 举报
 回复
今天可能采购多次
但是我的销售一天汇总一次，放在最后一次采购上
这样我可以做一个出入库的明细，但是要求是
如果销售是每天的汇总

这是一般的进销存

就是每天可能有采购，或有销售，但是销售数据会比较多，但是采购一般一天或几天才采购一次
「已注销」 2008-07-05
  • 打赏
  • 举报
 回复
销售报表与采购报表是一对多的关系,成品只一个,但采购购物料有多个,你的逻辑不对.除非你是做贸易公司的
-晴天 2008-07-05
  • 打赏
  • 举报
 回复
凭什么把
2008-7-2,101,1,40
放到
2008-7-2,10003,101,1,43,40
而不是放到
2008-7-2,10002,101,1,49,0
呢?
逻辑不对!
itymx 2008-07-05
  • 打赏
  • 举报
 回复
hery2002 很强呀 咱能不能建个群啊 兄弟们好联系啊
hksl 2008-07-05
  • 打赏
  • 举报
 回复
已经处理
先谢谢各位了
结贴
horizonlin 2008-07-05
  • 打赏
  • 举报
 回复
a表跟B表没关 系
fcuandy 2008-07-05
  • 打赏
  • 举报
 回复 
declare @A table (billdate datetime,billno int,inputtime datetime,companyID int,prodID int,qty int)

insert into @A

select '2008-7-1',10001,'2008-7-1 12:00:51',101,1,100 union all

select '2008-7-2',10002,'2008-7-2 11:01:06',101,1,49 union all

select '2008-7-2',10003,'2008-7-2 12:01:27',101,1,43 union all

select '2008-7-5',10004,'2008-7-3 12:01:55',101,1,230

--> 测试数据: @B

declare @B table (billdate datetime,companyID int,prodID int,qty int)

insert into @B

select '2008-7-1',101,1,20 union all

select '2008-7-2',101,1,40 union all

select '2008-7-3',101,1,100 union all

select '2008-7-4',101,1,66 union all

select '2008-7-6',101,1,57 



select isnull(A.billdate,b.billdate) billdate,A.billno,isnull(a.companyID,b.companyid),isnull(a.prodID,b.prodid),isnull(A.qty,0),isnull(B.qty,0) from @a a

full join @b b

	on a.billdate=b.billdate and a.prodid=b.prodid

	order by billdate
hanjs 2008-07-05
  • 打赏
  • 举报
 回复
full join？？
hksl 2008-07-05
  • 打赏
  • 举报
 回复
A采购表
billdate,billno,inputtime, companyID,prodID,qty
2008-7-1,10001,2008-7-1 12:00:51,101,1,100
2008-7-2,10002,2008-7-2 11:01:06,101,1,49
2008-7-2,10003,2008-7-2 12:01:27,101,1,43
2008-7-5,10004,2008-7-3 12:01:55,101,1,230

B表 销售汇总表

billdate,companyID,prodID,qty
2008-7-1,101,1,20
2008-7-2,101,1,40
2008-7-3,101,1,100
2008-7-4,101,1,66
2008-7-6,101,1,57


现在需要得到这样的报表
A.billdate,A.billno,companyID,prodID,A.qty,B.qty
2008-7-1,10001,101,1,100,20
2008-7-2,10002,101,1,49,0
2008-7-2,10003,101,1,43,40
2008-7-3,null, 101,1,0, 100
2008-7-4,null, 101,1,0, 66
2008-7-5,10004,101,1,230,0
2008-7-6,null,101,1,0, 57
hksl 2008-07-05
  • 打赏
  • 举报
 回复
刚还中情况
hksl 2008-07-05
  • 打赏
  • 举报
 回复
非常感谢
我试试先
hery2002 2008-07-05
  • 打赏
  • 举报
 回复
pt的简洁,
我刚才也按照你那种思路try了一下,
但是没有得到正确结果,
。。。。。。。
估计是哪里写错了....
hery2002 2008-07-05
  • 打赏
  • 举报
 回复 
-->生成测试数据

 

declare @A采购表 table([billdate] Datetime,[billno] int,[inputtime] datetime,[companyID] int,[prodID] int,[qty] int)

Insert @A采购表

select '2008-7-1',10001,N'2008-7-1 12:00:51',101,1,100 union all

select '2008-7-2',10002,N'2008-7-2 11:01:06',101,1,49 union all

select '2008-7-2',10003,N'2008-7-2 12:01:27',101,1,43 union all

select '2008-7-3',10004,N'2008-7-3 12:01:55',101,1,230

--Select * from @A采购表



-->生成测试数据

 

declare @B销售汇总表 table([billdate] Datetime,[companyID] int,[prodID] int,[qty] int)

Insert @B销售汇总表

select '2008-7-1',101,1,20 union all

select '2008-7-2',101,1,40 union all

select '2008-7-3',101,1,100

--Select * from @B销售汇总表



SELECT * FROM (

SELECT A.[billdate],A.[billno],A.[inputtime],A.[companyID],A.[prodID],A.[QTY] AS QTY_A,B.[QTY] AS QTY_B FROM  @A采购表 A  

LEFT JOIN @B销售汇总表 B ON B.[billdate] =A.[billdate] AND B.[companyID] =A.[companyID] AND B.[prodID]= A.[prodID]

WHERE NOT EXISTS(

           SELECT 1 FROM @A采购表 T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID] 

           AND T.[inputtime]>A.[inputtime]

           )

UNION ALL

SELECT A.[billdate],A.[billno],A.[inputtime],A.[companyID],A.[prodID],A.[QTY], 0 FROM @A采购表 A

WHERE EXISTS(SELECT 1 FROM @A采购表 T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID] 

           AND T.[inputtime]>A.[inputtime])

) T

ORDER BY 1,2

/*

billdate                billno      inputtime               companyID   prodID      QTY_A       QTY_B

----------------------- ----------- ----------------------- ----------- ----------- ----------- -----------

2008-07-01 00:00:00.000 10001       2008-07-01 12:00:51.000 101         1           100         20

2008-07-02 00:00:00.000 10002       2008-07-02 11:01:06.000 101         1           49          0

2008-07-02 00:00:00.000 10003       2008-07-02 12:01:27.000 101         1           43          40

2008-07-03 00:00:00.000 10004       2008-07-03 12:01:55.000 101         1           230         100

*/
Oracle疑难解答集
以前用Oracle的时候收集的一些疑难解答,曾经帮我解决了好多问题,拿出来分享...
成为编程高手的22条军规
这篇文档交给你怎么成为一名编程高手。很不错。
怎样学好单片机 郭天祥
郭天祥经过自己的学习经历向各位爱好单片机的学生推荐的
开发板介绍，学习方法
51单片机
mysql vfp_请教高手：关于VFP与MYSQL数据库连接问题
展开全部先自己扔块砖头出来。归纳如下：故障现象 : 无法连接 mysql错62616964757a686964616fe78988e69d8331333361326364误信息1 ：ERROR 1045 (28000): Access denied for user 'usera'@'localhost' (using password:YES)错误信息2 ：ERROR 1045 (28000)...
应用实例

27,582

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章