可查询历史变更单价表设计？

Question

不同用户设定不同单价，我是这样设计的：单价表(单号,建立日期，客户)
单价明细表(单号，品名，单价，生效日，失效日)我的设想是每次修改或新增单价就在单价表建立新的表单，
这样单价表里就有单价变更历史记录了,问题：订单录入时要根据客户搜索该客户的当前单价，但单价表里同一客户同一产品的单价记录可能好几条，如何查询出当前单价呢？看别的软件单价表是这样设计的，有生效日和失效日，就是不知怎么用，不知大家类似单价表是如何设计的？

Answer 1

单价表(单号,建立日期，客户，生效日，失效日)
单价明细表(单号，品名，单价)
这样会不会好点，查询当前单价  就找getdate between  生效日 and 失效日。

Answer 2

我想确认这种单价表 sql表设计方式 是否正确，请问各位单价表都是怎么设计的？

Answer 3

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

Answer 4

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'
goif 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.失效日
/*
单号 建立日期 客户 单价 生效日 失效日
10001 2012-10-06 18:24:32.000 U001 18.50 2012-09-30 00:00:00.000 2012-10-07 00:00:00.000
*/
我觉得没什么问题

Answer 5

我的记录是这样的，默认情况下生效日必填项，失效日是空值，只有出现同一产品不同时期单价不同时，前一条记录才会填写失效日，我的查询语句如下： --生效日不为空
   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.品名
    )我这样写好像可以实现，就是单价关系重大，自己太菜，不确定这样是否可行、可靠？

Answer 6

--生效日不为空
   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.品名
                )搞错了，蓝色部分应为失效日

Answer 7

又搞错了，蓝色部分应为失效日--生效日不为空
   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.品名
                )

Answer 8

您的意思是我这个代码效率太差是吗？
 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.品名
                )

Answer 9

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
*/
其实我的意思是想你的表设计成这样，不知道你看懂没

Answer 10

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

Answer 11

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.品名
                )

Answer 12

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

Answer 13

而且生效日有可能有多个，用=号会报错 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.品名
                )
同一个产品怎么会有多个生效日呢？ 我取得是最大值啊！

Answer 14

这个前提就是你的时间段要连续且不能重复。我觉得exists会比较好。

Answer 15

请问用 exists 如何修改呀！ 谢谢啊

Answer 16

看看我的触发器行不行：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