有三个表,Porducts(产品表),InStorages(入库表)和TheStorages(出库表)Porducts表中数据为
id PorductsCode
1 111
2 222
3 333InStorages表中数据为
id PorductsCode InStoragesNumber InStoragesTime
1 111 100 2011-1-1
2 111 20 2011-1-2
3 222 45 2011-1-1
4 222 55 2011-1-3
TheStorages表中数据为
id PorductsCode TheStoragesNumber TheStoragesTime
1 111 60 2011-1-2
2 111 220 2011-1-3
3 333 85 2011-1-1
4 222 15 2011-1-3入库表和出库表中分别有入库记录和出库记录,我需要将这三张表联合查询出来,
结果显示为:
id PorductsCode 入库数量 出库数量 时间
1 111 100 0 2011-1-1
2 111 20 60 2011-1-2
3 222 45 0 2011-1-1
4 222 55 15 2011-1-3
5 333 0 85 2011-1-1 查询为空的字段都用0表示
id PorductsCode
1 111
2 222
3 333InStorages表中数据为
id PorductsCode InStoragesNumber InStoragesTime
1 111 100 2011-1-1
2 111 20 2011-1-2
3 222 45 2011-1-1
4 222 55 2011-1-3
TheStorages表中数据为
id PorductsCode TheStoragesNumber TheStoragesTime
1 111 60 2011-1-2
2 111 220 2011-1-3
3 333 85 2011-1-1
4 222 15 2011-1-3入库表和出库表中分别有入库记录和出库记录,我需要将这三张表联合查询出来,
结果显示为:
id PorductsCode 入库数量 出库数量 时间
1 111 100 0 2011-1-1
2 111 20 60 2011-1-2
3 222 45 0 2011-1-1
4 222 55 15 2011-1-3
5 333 0 85 2011-1-1 查询为空的字段都用0表示
解决方案 »
- 有一张学生选课表,需要选出同时选择语文和数学的学生的姓名,需去重,麻烦帮忙
- 请问以下sql语句该怎么写?
- 关于SQL6.5的远程备份!!!!
- 数据库订阅发布之合并复制问题
- "错误1222:已超过了锁请求超时时段"是什么意思
- 关于ntext想在一个使用了group by 的结果中,我知道语法不可能,不管怎样能实现就行。[邹建大哥进]
- where dateadd(day,1,getdate())=cast('2009-01-05' as datetime)
- 数据库问题
- 怎样在调试的时候捕捉存储过程所执行的内容呢?
- 急急急(提一个update的问题)!!!
- 关于return的问题
- 如何以任意指定的顺序查找数据
AS
(SELECT PorductsCode,InStoragesTime AS Time
FROM InStorages
UNION
SELECT PorductsCode,TheStoragesTime AS Time
FROM TheStorages)SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS id,TT.PorductsCode,ISNULL(B.instoragesnumber,0) AS '入库数量',ISNULL(C.TheStoragesNumber,0) AS '出库数量' ,CONVERT(VARCHAR(10),TT.time,120) AS '时间'
FROM TT
LEFT JOIN InStorages B ON B.InStoragesTime = TT.time AND b.PorductsCode = TT.PorductsCode
left JOIN TheStorages C ON C.TheStoragesTime = TT.time AND c.PorductsCode = TT.PorductsCode/*
id PorductsCode 入库数量 出库数量 时间
-------------------- ------------ ----------- ----------- ----------
1 111 100 0 2011-01-01
2 111 20 60 2011-01-02
3 111 0 220 2011-01-03
4 222 45 0 2011-01-01
5 222 55 15 2011-01-03
6 333 0 85 2011-01-01(6 行受影响)
*/
查询出来的TT要怎么生成一个新表呢?
我还要用这生产的数据来重新生成一张表。
2 111 220 2011-1-3 这个数据不显示吗
AS
(SELECT PorductsCode,InStoragesTime AS Time
FROM InStorages
UNION
SELECT PorductsCode,TheStoragesTime AS Time
FROM TheStorages)select *
into New_table
from TT
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS id,TT.PorductsCode,ISNULL(B.instoragesnumber,0) AS '入库数量',ISNULL(C.TheStoragesNumber,0) AS '出库数量' ,CONVERT(VARCHAR(10),TT.time,120) AS '时间'
FROM (SELECT PorductsCode,InStoragesTime AS Time
FROM InStorages
UNION all
SELECT PorductsCode,TheStoragesTime AS Time
FROM TheStorages)TT
LEFT JOIN InStorages B ON B.InStoragesTime = TT.time AND b.PorductsCode = TT.PorductsCode
left JOIN TheStorages C ON C.TheStoragesTime = TT.time AND c.PorductsCode = TT.PorductsCode