求MSSQL查询语句表_货品资料
GoodsID GoodsName
001 苹果
002 橙
003 梨
表_进货
GoodsID Qty
001 10
001 20
002 10
002 20
003 20
003 30表_出货
GoodsID Qty
001 1
001 2
002 1
002 2
003 2
003 3表_存货
GoodsID Qty
001 27
002 27
003 45
以上四个表联合查询,要达到以下效果,请问查询语句怎么写?
查询结果
GoodsID GoodsName QtyIn(进货) QtyOut(出货) Qty(存货)
001 苹果 30 3 27
002 橙 30 3 27
003 梨 50 5 45
GoodsID GoodsName
001 苹果
002 橙
003 梨
表_进货
GoodsID Qty
001 10
001 20
002 10
002 20
003 20
003 30表_出货
GoodsID Qty
001 1
001 2
002 1
002 2
003 2
003 3表_存货
GoodsID Qty
001 27
002 27
003 45
以上四个表联合查询,要达到以下效果,请问查询语句怎么写?
查询结果
GoodsID GoodsName QtyIn(进货) QtyOut(出货) Qty(存货)
001 苹果 30 3 27
002 橙 30 3 27
003 梨 50 5 45
解决方案 »
- SQL11
- sp_grantlogin的用法请教,为何exec sp_grantlogin 'zhanggh\administrators' 错误呢?提示没有找到 Windows NT 用户或组 'zhanggh\administrators'。请再次检查该名称。
- 帮帮忙,这个查询语句怎么写?
- 数据库连接时而缓慢、时而迅速,补丁都打其了,仍然不得头绪,各位大虾帮忙。
- 向各位讨教一个问题?
- 怪问题:关于查询数据的疑问,求助
- sql server 2000存儲過程與powerbuilder數據窗口
- 请问在同1个库内2张表的同步更新问题。
- 怎样返回一个表的列数。
- 向高手讨叫
- 有没有办法查询出评论分书的数量,包括不存在的评论分书的数量,具体的看帖子
- ssiS中如何给 dataflow的数据源设置动态的sql脚本
A.GoodsID,
A.GoodsName,
B.Qty AS QtyIn,
C.Qty AS QtyOut,
B.Qty-C.Qty AS Qty
FROM 表_货品资料 AS A
JOIN(
SELECT
GoodsID,
SUM(Qty) AS Qty
FROM 表_进货
GROUP BY GoodsID
) AS B
ON A.GoodsID=B.GoodsID
JOIN(
SELECT
GoodsID,
SUM(Qty) AS Qty
FROM 表_出货
GROUP BY GoodsID
) AS C
ON A.GoodsID=C.GoodsID
A.GoodsID,
A.GoodsName,
B.Qty AS QtyIn,
C.Qty AS QtyOut,
D.Qty
FROM 表_货品资料 AS A
JOIN(
SELECT
GoodsID,
SUM(Qty) AS Qty
FROM 表_进货
GROUP BY GoodsID
) AS B
ON A.GoodsID=B.GoodsID
JOIN(
SELECT
GoodsID,
SUM(Qty) AS Qty
FROM 表_出货
GROUP BY GoodsID
) AS C
ON A.GoodsID=C.GoodsID
JOIN 表_存货 AS D
ON A.GoodsID=D.GoodsID
Select A.GoodsID, A.GoodsName ,IN.QtyIn AS '(进货)', OUT.QtyOut AS '(出货)' ,C.Qty AS '(存货)' From 表_货品资料 a inner join(select GoodsID,sum(Qty) as QTY FROM 表_进货 GROUP BY GOODSID) IN
ON A. GoodsID=IN. GoodsID
INNER JOIN (select GoodsID,sum(Qty) as QTY FROM 表_进货 GROUP BY GOODSID) OUT
ON A. GoodsID=OUT. GoodsID
INNER JOIN 表_存货 C
ON A.GoodsID=C. GoodsID
-- Author: liangCK 小梁
-- Date : 2008-11-18 13:01:07
---------------------------------
--> 生成测试数据: @表_货品资料
DECLARE @表_货品资料 TABLE (GoodsID VARCHAR(3),GoodsName VARCHAR(4))
INSERT INTO @表_货品资料
SELECT '001','苹果' UNION ALL
SELECT '002','橙' UNION ALL
SELECT '003','梨'
--> 生成测试数据: @表_进货
DECLARE @表_进货 TABLE (GoodsID VARCHAR(3),Qty INT)
INSERT INTO @表_进货
SELECT '001',10 UNION ALL
SELECT '001',20 UNION ALL
SELECT '002',10 UNION ALL
SELECT '002',20 UNION ALL
SELECT '003',20 UNION ALL
SELECT '003',30
--> 生成测试数据: @表_出货
DECLARE @表_出货 TABLE (GoodsID VARCHAR(3),Qty INT)
INSERT INTO @表_出货
SELECT '001',1 UNION ALL
SELECT '001',2 UNION ALL
SELECT '002',1 UNION ALL
SELECT '002',2 UNION ALL
SELECT '003',2 UNION ALL
SELECT '003',3
--> 生成测试数据: @表_存货
DECLARE @表_存货 TABLE (GoodsID VARCHAR(3),Qty INT)
INSERT INTO @表_存货
SELECT '001',27 UNION ALL
SELECT '002',27 UNION ALL
SELECT '003',45--SQL查询如下:--1
SELECT
A.GoodsID,
A.GoodsName,
B.Qty AS QtyIn,
C.Qty AS QtyOut,
B.Qty-C.Qty AS Qty
FROM @表_货品资料 AS A
JOIN(
SELECT
GoodsID,
SUM(Qty) AS Qty
FROM @表_进货
GROUP BY GoodsID
) AS B
ON A.GoodsID=B.GoodsID
JOIN(
SELECT
GoodsID,
SUM(Qty) AS Qty
FROM @表_出货
GROUP BY GoodsID
) AS C
ON A.GoodsID=C.GoodsID
--2
SELECT
A.GoodsID,
A.GoodsName,
B.Qty AS QtyIn,
C.Qty AS QtyOut,
D.Qty
FROM @表_货品资料 AS A
JOIN(
SELECT
GoodsID,
SUM(Qty) AS Qty
FROM @表_进货
GROUP BY GoodsID
) AS B
ON A.GoodsID=B.GoodsID
JOIN(
SELECT
GoodsID,
SUM(Qty) AS Qty
FROM @表_出货
GROUP BY GoodsID
) AS C
ON A.GoodsID=C.GoodsID
JOIN @表_存货 AS D
ON A.GoodsID=D.GoodsID/*
GoodsID GoodsName QtyIn QtyOut Qty
------- --------- ----------- ----------- -----------
001 苹果 30 3 27
002 橙 30 3 27
003 梨 50 5 45(3 行受影响)*/
select a.GoodsID,a.GoodsName,b.Qty as QtyIn,c.Qty as QtyOut,d.Qty
from 表_货品资料 a left join (select GoodsID,sum(Qty) as QTy from 表_进货 group by GoodsID)b
on a.GoodSid = B.GoodsID
left join (select GoodsID,sum(Qty) as QTy from 表_出货 group by GoodsID)c
on a.GoodSid = c.GoodsID
left join 表_存货 d on a.GoodsID = d.GoodsID
INSERT INTO #Good
SELECT '001','苹果' UNION ALL
SELECT '002','橙' UNION ALL
SELECT '003','梨'
create TABLE #GoodIn (GoodsID VARCHAR(3),Qty INT)
INSERT INTO #GoodIn
SELECT '001',10 UNION ALL
SELECT '001',20 UNION ALL
SELECT '002',10 UNION ALL
SELECT '002',20 UNION ALL
SELECT '003',20 UNION ALL
SELECT '003',30
create TABLE #GoodOut(GoodsID VARCHAR(3),Qty INT)
INSERT INTO #GoodOut
SELECT '001',1 UNION ALL
SELECT '001',2 UNION ALL
SELECT '002',1 UNION ALL
SELECT '002',2 UNION ALL
SELECT '003',2 UNION ALL
SELECT '003',3
create TABLE #GoodStorage (GoodsID VARCHAR(3),Qty INT)
INSERT INTO #GoodStorage
SELECT '001',27 UNION ALL
SELECT '002',27 UNION ALL
SELECT '003',45/*
GoodsID GoodsName QtyIn(进货) QtyOut(出货) Qty(存货)
001 苹果 30 3 27
002 橙 30 3 27
003 梨 50 5 45
*/select g3.GoodsID,g3.GoodsName,g3.Qty '进货',g4.Qty '出货',g5.Qty '存货' from
(select g1.GoodsID,g1.GoodsName,sum(g2.Qty) Qty from #Good g1 join #GoodIn g2
on g1.GoodsID=g2.GoodsID group by g1.GoodsID,g1.GoodsName) g3 join
(select GoodsID,sum(Qty) Qty from #GoodOut group by GoodsID) g4 on g4.GoodsID=g3.GoodsID
join (select GoodsID,sum(Qty) Qty from #GoodStorage group by GoodsID) g5 on g4.GoodsID=g5.GoodsID