select a.InDate date,a.MaterialID,b.MamterialName,a.inno,a.inqty,null outno,null outqty from 表2 a join 表1 b on a.MaterialID=b.MaterialID
union all
select a.outDate date,a.MaterialID,b.MamterialName,null,null,a.outno,a.outqty from 表3 a join 表1 b on a.MaterialID=b.MaterialID
union all
select a.outDate date,a.MaterialID,b.MamterialName,null,null,a.outno,a.outqty from 表3 a join 表1 b on a.MaterialID=b.MaterialID
解决方案 »
- sql 里怎么把varchar转成datetime格式
- SQLserver 中怎样用 T-SQL语句插入新一列???求解?
- 请教i++存储过程
- 求存储过程要求 可以修改状态的查询语句
- 简单 sql 语句 求变量@date=f200801 to 本月
- 大数据读取时,页面超卡,什么原因?
- 为什么我检索ID列最大值时最大是99?明明有超过100的数!
- sql记录操作日志吗?
- [DBNETLIB][ConnectionOpen (Connect()).]SQL Server 不存在或拒绝访问。
- SQL Server 2000作为网络数据库服务器的问题
- .jpu 文件是什么!(教育部的软件)
- bcp 问题
(case when 表2.InDate is not null then 表2.InDate case when 表3.OutDate is not null then 表3.OutDate end ) as [date],
表1.MaterialID,表1.MaterialName , 表2.Inno,表2.InQty,表3.outNo,表3.outqty
from 表1
left join 表2 on 表1.MaterialID = 表2.MaterialID
left join 表3 on 表1.MaterialID = 表3.MaterialID
Unionselect outDate as date ,MaterialID ,'null' as InNo ,'null' as InQty, outno ,outqty from table3 left join table1 on table3.MaterialID = table1.MaterialID
insert into @a values( 1 ,'test')declare @b table ( MaterialID int, InNo int, InDate datetime, InQty int)
insert into @b values( 1, 11, '2003-03-06' , 250)declare @c table(MaterialID int, OutNo int, OutDate datetime, OutQty int)
insert into @c values( 1 , 21 , '2003-03-07' , 3000)select b.*,c.outno,c.outqty ,a.amterialname
from @b b left join @a a on a.materialid=b.materialid
left join @c c on b.indate=c.outdate and b.materialid=c.materialid
union
select c.materialid,b.inno,c.outdate as indate,b.inqty,c.outno,c.outqty,a.amterialname
from @c c left join @a a on a.materialid=c.materialid
left join @b b on b.indate=c.outdate and b.materialid=c.materialid
insert into @a values( 1 ,'test')declare @b table ( MaterialID int, InNo int, InDate datetime, InQty int)
insert into @b values( 1, 11, '2003-03-06' , 250)declare @c table(MaterialID int, OutNo int, OutDate datetime, OutQty int)
insert into @c values( 1 , 21 , '2003-03-07' , 3000)select a.InDate date,a.MaterialID,b.MamterialName,a.inno,a.inqty,null outno,null outqty from @b a join @a b on a.MaterialID=b.MaterialID
union all
select a.outDate date,a.MaterialID,b.MamterialName,null,null,a.outno,a.outqty from @c a join @a b on a.MaterialID=b.MaterialID
date MaterialID MamterialName inno inqty outno outqty
------------------------------------------------------ ----------- -------------------------------------------------- ----------- ----------- ----------- -----------
2003-03-06 00:00:00.000 1 test 11 250 NULL NULL
2003-03-07 00:00:00.000 1 test NULL NULL 21 3000(所影响的行数为 2 行)