34,593
社区成员
发帖
与我相关
我的任务
分享
CREATE VIEW vw_GetItemInfo
AS
SELECT a.*, b.价格
FROM A a,b b
where a.日期 BETWEEN b.生效日期 AND b.失效日期
AND a.物料编码=b.物料编码
GO
use PracticeDB
go
if OBJECT_ID('tb_a') is not null
drop table tb_a
go
create table tb_a (日期 date,物料编码 varchar(5),入库数量 int)
insert into tb_a
select '2010-5-12', '00', 100 union all
select '2010-5-16', '00', 400 union all
select '2010-06-01', '00', 300
if OBJECT_ID('tb_b') is not null
drop table tb_b
go
create table tb_b(物料编码 varchar(5),生效日期 date,失效日期 date,价格 numeric(5,1))
insert into tb_b
select '00', '2010-5-1', '2010-5-15', 2 union all
select '00', '2010-5-16', '2010-5-31', 2.5 union all
select '00', '2010-6-1', '2010-6-30', 3
create view v_test
as
select a.日期,a.物料编码,a.入库数量,b.价格
from tb_a a left join tb_b b on a.物料编码=b.物料编码 and a.日期 between b.生效日期 and b.失效日期
--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([日期] [datetime],[物料编码] [nvarchar](10),[入库数量] [int])
INSERT INTO [A]
SELECT '2010-5-12','00','100' UNION ALL
SELECT '2010-5-16','00','400' UNION ALL
SELECT '2010-06-01','00','300'
--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([物料编码] [nvarchar](10),[生效日期] [datetime],[失效日期] [datetime],[价格] [numeric](2,1))
INSERT INTO [B]
SELECT '00','2010-5-1','2010-5-15','2' UNION ALL
SELECT '00','2010-5-16','2010-5-31','2.5' UNION ALL
SELECT '00','2010-6-1','2010-6-30','3'
GO
--SELECT * FROM [A]
--SELECT * FROM [B]
-->SQL查询如下:
CREATE VIEW vw_GetItemInfo
AS
SELECT a.*, b.价格
FROM A a
JOIN b b
ON a.日期 BETWEEN b.生效日期 AND b.失效日期
AND a.物料编码=b.物料编码
GO
SELECT * FROM vw_GetItemInfo
/*
日期 物料编码 入库数量 价格
----------------------- ---------- ----------- ---------------------------------------
2010-05-12 00:00:00.000 00 100 2.0
2010-05-16 00:00:00.000 00 400 2.5
2010-06-01 00:00:00.000 00 300 3.0
(3 行受影响)
*/