有一产品表PROtuce:
id,bianhao,name zongsuliang
有一销售表:Sell_T
ID,bianhao,name,xssl(销售数量).编号是唯一的。求每一个产品的剩余库存量视图:视图:如下字段:id(产品ID),bianhao,name,zongsulian,xssl,kcl(就是剩库存余量)
id,bianhao,name zongsuliang
有一销售表:Sell_T
ID,bianhao,name,xssl(销售数量).编号是唯一的。求每一个产品的剩余库存量视图:视图:如下字段:id(产品ID),bianhao,name,zongsulian,xssl,kcl(就是剩库存余量)
as
select a.*,b.xssl,a.zongsuliang-b.xssl as kcl
from PROtuce a
left join Sell_T b on a.id=b.id
AS
SELECT a.*, b.xssl, a.zongsuliang-ISNULL(b.xssl, 0) AS kcl
FROM PROtuce a
LEFT JOIN (
SELECT bianhao, SUM(xssl) xssl
FROM Sell_T
GROUP BY bianhao
) b
ON a.bianhao = b.bianhao
GO
SELECT dbo.PROtuce.Bianhao, dbo.PROtuce.id, dbo.PROtuce.name,
SUM(dbo.PROtuce.zongsuliang) AS zongsuliang, SUM(dbo.sell_t.xssl) AS xssl,
'zongsuliang-xssl' AS Kcl
FROM dbo.PROtuce INNER JOIN
dbo.sell_t ON dbo.PROtuce.Bianhao = dbo.sell_t.bianhao
GROUP BY dbo.PROtuce.Bianhao, dbo.PROtuce.id, dbo.PROtuce.name
CREATE VIEW dbo.Produce_View
AS
SELECT dbo.PROtuce.Bianhao, dbo.PROtuce.id, dbo.PROtuce.name,
SUM(dbo.PROtuce.zongsuliang) AS zongsuliang, SUM(dbo.sell_t.xssl) AS xssl,
'zongsuliang-xssl' AS Kcl
FROM dbo.PROtuce LEFT OUTER JOIN
dbo.sell_t ON dbo.PROtuce.Bianhao = dbo.sell_t.bianhao
GROUP BY dbo.PROtuce.Bianhao, dbo.PROtuce.id, dbo.PROtuce.name
CREATE VIEW PROtuce_view
AS
SELECT a.*, b.xssl, a.zongsuliang-ISNULL(b.xssl, 0) AS kcl
FROM PROtuce a
JOIN (
SELECT bianhao, SUM(xssl) xssl
FROM Sell_T
GROUP BY bianhao
) b
ON a.bianhao = b.bianhao
GO
这个条件的话,能导致:
当销售表中没有记录时,不显示记录!这个错误
AS
SELECT a.*, b.xssl, a.zongsuliang-ISNULL(b.xssl, 0) AS kcl
FROM PROtuce a
JOIN (
SELECT bianhao, SUM(xssl) xssl
FROM Sell_T
GROUP BY bianhao
) b
ON a.bianhao = b.bianhao
GO
如果A中有三个产品,分别为abc
那么,B中如果只有a,b
问题 是这个c就没了,不显示了!
而你的只有一个 Join.
bianhao
Sell_T: bianhao这两个表的bianhao才可能相同。但是PROtuce表的记录条数,要大于Sell_T条数,当PROtuce多出来的条数也要在这个视图中显示!
id,bianhao,name zongsuliang
1 N1001 aaa 10
2 B2001 bbb 11
3 C3001 ccc 5
有一销售表:Sell_T
ID,bianhao,name,xssl(销售数量).
11 N1001 aaa 5
14 B2001 bbb 6
最终视图的结果为:id(产品ID),bianhao,name,zongsulian,xssl,kcl(就是剩库存余量)1 N1001 aaa 10 5, 5
2 B2001 bbb 11 5 6
3 C3001 ccc 5 0 5
IF OBJECT_ID('[PROtuce]') IS NOT NULL
DROP TABLE [PROtuce]
GO
CREATE TABLE [PROtuce] ([id] [int],[bianhao] [nvarchar](10),[name] [nvarchar](10),[zongsuliang] [int])
INSERT INTO [PROtuce]
SELECT '1','N1001','aaa','10' UNION ALL
SELECT '2','B2001','bbb','11' UNION ALL
SELECT '3','C3001','ccc','5'--> 生成测试数据表: [Sell_T]
IF OBJECT_ID('[Sell_T]') IS NOT NULL
DROP TABLE [Sell_T]
GO
CREATE TABLE [Sell_T] ([ID] [int],[bianhao] [nvarchar](10),[name] [nvarchar](10),[xssl] [int])
INSERT INTO [Sell_T]
SELECT '11','N1001','aaa','5' UNION ALL
SELECT '14','B2001','bbb','6'
GO
--SELECT * FROM [PROtuce]
--SELECT * FROM [Sell_T]-->SQL查询如下:
CREATE VIEW PROtuce_view
AS
SELECT a.*, ISNULL(b.xssl,0) xssl, a.zongsuliang-ISNULL(b.xssl, 0) AS kcl
FROM PROtuce a
LEFT JOIN (
SELECT bianhao, SUM(xssl) xssl
FROM Sell_T
GROUP BY bianhao
) b
ON a.bianhao = b.bianhao
GOSELECT * FROM PROtuce_view
/*
id bianhao name zongsuliang xssl kcl
----------- ---------- ---------- ----------- ----------- -----------
1 N1001 aaa 10 5 5
2 B2001 bbb 11 6 5
3 C3001 ccc 5 0 5(3 行受影响)
*/