可查询历史变化单价表设计

可查询历史变更单价表设计？
不同用户设定不同单价，我是这样设计的：

单价表(单号,建立日期，客户)
单价明细表(单号，品名，单价，生效日，失效日)

我的设想是每次修改或新增单价就在单价表建立新的表单，
这样单价表里就有单价变更历史记录了,

问题：

订单录入时要根据客户搜索该客户的当前单价，但单价表里同一客户同一产品的单价记录可能好几条，如何查询出当前单价呢？看别的软件单价表是这样设计的，有生效日和失效日，就是不知怎么用，不知大家类似单价表是如何设计的？



[最优解释]

CREATE TABLE test (id INT ,begindate DATE,enddate date)
INSERT INTO test
SELECT 1,'2012-11-21','2012-11-22'
UNION ALL 
SELECT 1,'2012-11-23','2012-12-22'
UNION ALL 
SELECT 1,'2012-12-23','2999-12-31'
UNION ALL 
SELECT 2,'2012-11-21','2999-12-31'
SELECT * FROM test 

/*
id          begindate  enddate
----------- ---------- ----------
1           2012-11-21 2012-11-22
1           2012-11-23 2012-12-22
1           2012-12-23 2999-12-31
2           2012-11-21 2999-12-31
*/

DECLARE @date DATETIME 
SET @date='2012-11-30'
SELECT * FROM test WHERE @date BETWEEN begindate AND enddate

/*
id          begindate  enddate
----------- ---------- ----------
1           2012-11-23 2012-12-22
2           2012-11-21 2999-12-31
*/

其实我的意思是想你的表设计成这样，不知道你看懂没
[其他解释]
单价表(单号,建立日期，客户，生效日，失效日)
单价明细表(单号，品名，单价)
这样会不会好点，查询当前单价  就找getdate between  生效日 and 失效日。
[其他解释]

if OBJECT_ID('单价表') is not null
drop table 单价表
go
create table 单价表
(
单号 varchar(10),
建立日期 datetime,
客户 varchar(10)
)
go
insert 单价表
select '10001','2012-10-06 18:24:32','U001' union all
select '10002','2012-10-14 09:32:53','U001'
go

if OBJECT_ID('单价明细表')is not null
drop table 单价明细表
go
create table 单价明细表
(
单号 varchar(10),
品名 varchar(10),
单价 numeric(8,2),
生效日 datetime,
失效日 datetime
)
go
insert 单价明细表
select '10001','test01',18.50,'2012-09-30','2012-10-07' union all
select '10001','test02',19.50,'2012-10-30','2012-11-07' union all
select '10002','test01',25.50,'2012-11-08','2012-11-30' union all
select '10002','test02',18.50,'2012-12-01','2012-12-05' union all
select '10001','test01',24.50,'2012-12-06','2012-12-31'
go


select
a.*,
b.单价,
b.生效日,
b.失效日
from
单价表 a
inner join
单价明细表 b
on 
a.单号=b.单号
where
a.建立日期 between b.生效日 and b.失效日
/*
单号建立日期客户单价生效日失效日
100012012-10-06 18:24:32.000U00118.502012-09-30 00:00:00.0002012-10-07 00:00:00.000
*/


我觉得没什么问题

------其他解决方案--------------------

为什么不弄个触发器呢，把每次修改新增的数据时把数据插入到另一个表，这样可以确保主表都是唯一性。而且查看历史记录也很方便啊。
[其他解释]
我想确认这种单价表 sql表设计方式 是否正确，请问各位单价表都是怎么设计的？
[其他解释]
你这个设计是可以的，只需要传入需要查询的日期，然后between 生效日 and 失效日就可以了。但是注意几点：
1、每个单价的最后一条数据的失效日期最好硬性写定一个值，如2999-12-31。即永不过期，这样在以后使用的时候很有好处。
2、如果你的日期只精确到天数，那么切记同一个单价，多条数据的失效日要在下一条数据的生效日前一天。不然between and 的时候会有重复数据
[其他解释]

引用:

你这个设计是可以的，只需要传入需要查询的日期，然后between 生效日 and 失效日就可以了。但是注意几点：
1、每个单价的最后一条数据的失效日期最好硬性写定一个值，如2999-12-31。即永不过期，这样在以后使用的时候很有好处。
2、如果你的日期只精确到天数，那么切记同一个单价，多条数据的失效日要在下一条数据的生效日前一天。不然between and 的时候会……

我的记录是这样的，默认情况下生效日必填项，失效日是空值，只有出现同一产品不同时期单价不同时，前一条记录才会填写失效日，我的查询语句如下：

 --生效日不为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where 生效日 <= getdate() and 生效日+1 >= getdate()
   union all
 --生效日为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where b.生效日 is null and
          生效日 = (
             select max(生效日)
 from 单价表 as t1 inner join 单价名表 as t2 on t1.单号 = t2.单号
 where t1.客户 = a.客户 and t2.品名 = b.品名
    )

我这样写好像可以实现，就是单价关系重大，自己太菜，不确定这样是否可行、可靠？

[其他解释]
生效日期既然为必填，那怎么可能为空呢？然后失效日期我建议还是设定一个大值。没新增一条数据，就做截断操作，你这个写法不好
[其他解释]

引用:

生效日期既然为必填，那怎么可能为空呢？然后失效日期我建议还是设定一个大值。没新增一条数据，就做截断操作，你这个写法不好

--生效日不为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where 生效日 <= getdate() and 生效日+1 >= getdate()
   union all
 --生效日为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where b.生效日 is null and
          失效日 = (
                 select max(生效日)
                 from 单价表 as t1 inner join 单价名表 as t2 on t1.单号 = t2.单号
                 where t1.客户 = a.客户 and t2.品名 = b.品名
                )

搞错了，蓝色部分应为失效日

[其他解释]

引用:

引用:
生效日期既然为必填，那怎么可能为空呢？然后失效日期我建议还是设定一个大值。没新增一条数据，就做截断操作，你这个写法不好

--生效日不为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where 生效日……

我给你的思路是我以前在银行做系统的时候，记录账号每天的变更情况时使用的。比较合适你的这个情况。
[其他解释]
又搞错了，蓝色部分应为失效日

--生效日不为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where 生效日 <= getdate() and 生效日+1 >= getdate()
   union all
 --生效日为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where b.失效日 is null and
          生效日 = (
                 select max(生效日)
                 from 单价表 as t1 inner join 单价名表 as t2 on t1.单号 = t2.单号
                 where t1.客户 = a.客户 and t2.品名 = b.品名
                )



[其他解释]

引用:

引用:引用:
生效日期既然为必填，那怎么可能为空呢？然后失效日期我建议还是设定一个大值。没新增一条数据，就做截断操作，你这个写法不好

--生效日不为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 a……

您的意思是我这个代码效率太差是吗？

 select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where b.生效日 is null and
          失效日 = (
                 select max(生效日)
                 from 单价表 as t1 inner join 单价名表 as t2 on t1.单号 = t2.单号
                 where t1.客户 = a.客户 and t2.品名 = b.品名
                )

[其他解释]

引用:

SQL code?123456789101112131415161718192021222324252627282930CREATE TABLE test (id INT ,begindate DATE,enddate date)INSERT INTO testSELECT 1,'2012-11-21','2012-11-22'UNION ALL SELECT 1,'20……

哦~  明白了！ 这样的话，失效日也要设为必填项，默认日期设为很大很大，若出现以下单价

1           2012-11-21 2012-11-22
1           2012-11-23 2012-12-22
1           2012-12-23 2999-12-31

就调整失效日，是这样吗？
[其他解释]
你观察一下，同一个ID，它的顺序是连续的。这样能保证一个传入的日期参数必然只有一条数据。而不会导致数据重复
[其他解释]

引用:

你观察一下，同一个ID，它的顺序是连续的。这样能保证一个传入的日期参数必然只有一条数据。而不会导致数据重复

您的方法我试了，但有一个问题：
如果失效日设为2999-12-31的话，这样同一个品名每修改一次单价必须要修改同品名上一次单价的失效日，这样如果一次变更300个品名的单价，那这300个品名上次单价的失效日都要修改，工作量太大？
[其他解释]

引用:

引用:
你观察一下，同一个ID，它的顺序是连续的。这样能保证一个传入的日期参数必然只有一条数据。而不会导致数据重复

您的方法我试了，但有一个问题：
如果失效日设为2999-12-31的话，这样同一个品名每修改一次单价必须要修改同品名上一次单价的失效日，这样如果一次变更300个品名的单价，那这300个品名上次单价的失效日都要修改，工作量太大？

对都要update，单是这个没问题的，银行300万账号都这样做，你那300个.....
[其他解释]

引用:

DBA_Huangzj  谢谢您！

这个update应该是单价变更时用触发器变更失效日的值：生效日-1天的值对吧？

还有，我的代码也可以实现，就是效率不高吗？还是有其他隐患？谢谢！
 


SQL code?



1234567891011121314

--生效日不为空    select a.客户, b.品名, b.单价, b.生效日    from 单价表 ……

我当时没用触发器，在程序里面实现的。不过你可以考虑用。下一条数据的生效日是你前端程序传入的，然后把上一条的失效时间截断成为传入日期-1.你那个语句目测不高效，用exists或者表关联，而且生效日有可能有多个，用=号会报错。
[其他解释]
DBA_Huangzj  谢谢您！

这个update应该是单价变更时用触发器变更失效日的值：生效日-1天的值对吧？

还有，我的代码也可以实现，就是效率不高吗？还是有其他隐患？谢谢！
 

--生效日不为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where 生效日 <= getdate() and 生效日+1 >= getdate()
   union all
 --生效日为空
   select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号
   where b.失效日 is null and
          生效日 = (
                 select max(生效日)
                 from 单价表 as t1 inner join 单价名表 as t2 on t1.单号 = t2.单号
                 where t1.客户 = a.客户 and t2.品名 = b.品名
                )

[其他解释]

引用:

我当时没用触发器，在程序里面实现的。不过你可以考虑用。下一条数据的生效日是你前端程序传入的，然后把上一条的失效时间截断成为传入日期-1.你那个语句目测不高效，用exists或者表关联，而且生效日有可能有多个，用=号会报错。 

而且生效日有可能有多个，用=号会报错

 select a.客户, b.品名, b.单价, b.生效日
   from 单价表 as a inner join 单价明细表 as b on a.单号 = b.单号

   where b.失效日 is null and
          生效日 = (
                 select max(生效日)
                 from 单价表 as t1 inner join 单价名表 as t2 on t1.单号 = t2.单号
                 where t1.客户 = a.客户 and t2.品名 = b.品名
                )
同一个产品怎么会有多个生效日呢？ 我取得是最大值啊！



[其他解释]
不懂，单价还有单价明细？

[其他解释]

引用:

引用:引用:
我当时没用触发器，在程序里面实现的。不过你可以考虑用。下一条数据的生效日是你前端程序传入的，然后把上一条的失效时间截断成为传入日期-1.你那个语句目测不高效，用exists或者表关联，而且生效日有可能有多个，用=号会报错。 

而且生效日有可能有多个，用=号会报错

 se……

请问用 exists 如何修改呀！ 谢谢啊
[其他解释]

引用:

引用:
我当时没用触发器，在程序里面实现的。不过你可以考虑用。下一条数据的生效日是你前端程序传入的，然后把上一条的失效时间截断成为传入日期-1.你那个语句目测不高效，用exists或者表关联，而且生效日有可能有多个，用=号会报错。 

而且生效日有可能有多个，用=号会报错

 select a.客户, b.品名, b.单价, b.生效日
   ……

这个前提就是你的时间段要连续且不能重复。我觉得exists会比较好。
[其他解释]
看看我的触发器行不行：

if exists(select 1 from inserted) and not exists(select 1 from deleted) --insert
update 单价明细表
set 失效日 = t.生效日 - 1
from 单价表 a join 单价明细表 b on a.单号 = b.单号
                       join (select t1.客户, t2.品名, t2.生效日, t2.单号 from 单价表 t1 join inserted t2 on t1.单号 = t2.单号) t
                             on a.客户 = t.客户 and b.品名 = t.品名
        where a.单号 <> t.单号 and 失效日 is null

以上触发器 在新增单价表明细表示会更新该品名同一客户上笔单价失效日，

请问如果修改生效日时更新上笔单价的失效日触发器怎么写呢？

如：上笔单价表失效日为：2012-11-10
    当前单价的生效日为：2012-11-21
    如果修改当前单价生效日为：2012-11-20 那上笔单价失效日应变更为2012-11-19
[其他解释]

引用:

引用:
引用:引用:
我当时没用触发器，在程序里面实现的。不过你可以考虑用。下一条数据的生效日是你前端程序传入的，然后把上一条的失效时间截断成为传入日期-1.你那个语句目测不高效，用exists或者表关联，而且生效日有可能有多个，用=号会报错。 

而且生效日有可能有多个，……

算了，也不是不行，问题是要你弄懂我的思路，然后看看是否合适你
[其他解释]
你要放到触发器里面？那个我不熟哦

查看更多 下一篇