单价历史查询问题 select * from tb where abs(datediff(day,实施日期,'2008-9-30')) = (select min(abs(datediff(day,实施日期,'2008-9-30'))) from tb) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select * from tb a where 实施日期<'2008-9-30' and not exists(select 1 from tb where 材料编号=a.材料编号 and 实施日期>a.实施日期 and 实施日期<'2008-9-30') create table tb(材料编号 int, 单价 decimal(18,4) , 实施日期 datetime)insert into tb values(15 ,.4300 , '2008-10-01 00:00:00') insert into tb values(17 ,.2700 , '2008-10-01 00:00:00') insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00') insert into tb values(24 ,.6500 , '2008-09-01 00:00:00') insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00') insert into tb values(26 ,.5700 , '2008-10-01 00:00:00') insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00') insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00') insert into tb values(158 ,188.0000, '2008-10-01 00:00:00') insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00') insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00') insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00') insert into tb values(242 ,.1980 , '2008-10-01 00:00:00') insert into tb values(243 ,.1870 , '2008-10-01 00:00:00') insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')goselect * from tb where abs(datediff(day,实施日期,'2008-9-30')) = (select min(abs(datediff(day,实施日期,'2008-9-30'))) from tb)drop table tb /*材料编号 单价 实施日期 ----------- -------------------- ------------------------------------------------------ 15 .4300 2008-10-01 00:00:00.00017 .2700 2008-10-01 00:00:00.00026 .5700 2008-10-01 00:00:00.00027 4.2000 2008-10-01 00:00:00.000156 2.1000 2008-10-01 00:00:00.000158 188.0000 2008-10-01 00:00:00.000159 26.0000 2008-10-01 00:00:00.000242 .1980 2008-10-01 00:00:00.000243 .1870 2008-10-01 00:00:00.000283 .4200 2008-10-01 00:00:00.000(所影响的行数为 10 行)*/ --如果是按照材料编号分组来查.create table tb(材料编号 int, 单价 decimal(18,4) , 实施日期 datetime)insert into tb values(15 ,.4300 , '2008-10-01 00:00:00') insert into tb values(17 ,.2700 , '2008-10-01 00:00:00') insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00') insert into tb values(24 ,.6500 , '2008-09-01 00:00:00') insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00') insert into tb values(26 ,.5700 , '2008-10-01 00:00:00') insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00') insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00') insert into tb values(158 ,188.0000, '2008-10-01 00:00:00') insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00') insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00') insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00') insert into tb values(242 ,.1980 , '2008-10-01 00:00:00') insert into tb values(243 ,.1870 , '2008-10-01 00:00:00') insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')goselect m.* from tb m ,(select 材料编号, min(abs(datediff(day,实施日期,'2008-9-30'))) cnt from tb group by 材料编号) nwhere m.材料编号 = n.材料编号 and abs(datediff(day,m.实施日期,'2008-9-30')) = n.cntdrop table tb /*材料编号 单价 实施日期 ----------- -------------------- ------------------------------------------------------ 15 .4300 2008-10-01 00:00:00.00017 .2700 2008-10-01 00:00:00.00022 1.3000 2008-09-01 00:00:00.00024 .6400 2008-09-02 00:00:00.00026 .5700 2008-10-01 00:00:00.00027 4.2000 2008-10-01 00:00:00.000156 2.1000 2008-10-01 00:00:00.000158 188.0000 2008-10-01 00:00:00.000159 26.0000 2008-10-01 00:00:00.000176 1.4640 2008-09-01 00:00:00.000226 1.8000 2008-09-01 00:00:00.000242 .1980 2008-10-01 00:00:00.000243 .1870 2008-10-01 00:00:00.000283 .4200 2008-10-01 00:00:00.000(所影响的行数为 14 行)*/ declare @d datetimeset @d='2008-09-30'select * from tb where 实施日期 between @d-1 and @d+1 create table tb(材料编号 int, 单价 decimal(18,4) , 实施日期 datetime)insert into tb values(15 ,.4300 , '2008-10-01 00:00:00') insert into tb values(17 ,.2700 , '2008-10-01 00:00:00') insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00') insert into tb values(24 ,.6500 , '2008-09-01 00:00:00') insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00') insert into tb values(26 ,.5700 , '2008-10-01 00:00:00') insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00') insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00') insert into tb values(158 ,188.0000, '2008-10-01 00:00:00') insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00') insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00') insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00') insert into tb values(242 ,.1980 , '2008-10-01 00:00:00') insert into tb values(243 ,.1870 , '2008-10-01 00:00:00') insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')goselect * from tb a where 实施日期<'2008-9-30' and not exists(select 1 from tb where 材料编号=a.材料编号 and 实施日期>a.实施日期)drop table tb 材料编号 单价 实施日期----------- --------------------------------------- -----------------------22 1.3000 2008-09-01 00:00:00.00024 0.6400 2008-09-02 00:00:00.000176 1.4640 2008-09-01 00:00:00.000226 1.8000 2008-09-01 00:00:00.000(4 行受影响) create table tb(材料编号 int, 单价 decimal(18,4) , 实施日期 datetime)insert into tb values(15 ,.4300 , '2008-10-01 00:00:00') insert into tb values(17 ,.2700 , '2008-10-01 00:00:00') insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00') insert into tb values(24 ,.6500 , '2008-09-01 00:00:00') insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00') insert into tb values(26 ,.5700 , '2008-10-01 00:00:00') insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00') insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00') insert into tb values(158 ,188.0000, '2008-10-01 00:00:00') insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00') insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00') insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00') insert into tb values(242 ,.1980 , '2008-10-01 00:00:00') insert into tb values(243 ,.1870 , '2008-10-01 00:00:00') insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')goselect * from (select * ,rn=row_number()over(partition by 材料编号 order by 实施日期 desc ) from tb where 实施日期<'2008-09-30') a where rn=1 drop table tb /*22 1.3000 2008-09-01 00:00:00.000 124 0.6400 2008-09-02 00:00:00.000 1176 1.4640 2008-09-01 00:00:00.000 1226 1.8000 2008-09-01 00:00:00.000 1*/ 10楼满足,而且是最快的,不过需要sql2005 create table tb(材料编号 int, 单价 decimal(18,4) , 实施日期 datetime)insert into tb values(15 ,.4300 , '2008-10-01 00:00:00') insert into tb values(17 ,.2700 , '2008-10-01 00:00:00') insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00') insert into tb values(22 ,1.5000 , '2008-09-01 00:00:00') insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00') insert into tb values(26 ,.5700 , '2008-10-01 00:00:00') insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00') insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00') insert into tb values(158 ,188.0000, '2008-10-01 00:00:00') insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00') insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00') insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00') insert into tb values(242 ,.1980 , '2008-10-01 00:00:00') insert into tb values(243 ,.1870 , '2008-10-01 00:00:00') insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')goselect * from (select * from tb a where 实施日期<'2008-9-30' and not exists(select 1 from tb where 材料编号=a.材料编号 and 实施日期>a.实施日期)) twhere not exists (select 1 from tb where 材料编号=t.材料编号 and 单价>t.单价)drop table tb 材料编号 单价 实施日期----------- --------------------------------------- -----------------------22 1.5000 2008-09-01 00:00:00.00024 0.6400 2008-09-02 00:00:00.000176 1.4640 2008-09-01 00:00:00.000226 1.8000 2008-09-01 00:00:00.000(4 行受影响)取较大的单价,较小的单价那边改为小于号即可。 我用的是SQL Server 2000 8楼答案的最为合适 关于表名和列名全是字母和数据序号的疑问,这样做有什么优点? 插入不重复信息到别的表? SQL2005定时批量访问通过WEB处理数据,应如何做? 请教个游标的问题? 一个用SQL奇怪的问题 ACCESS文件转换成CSV文件,然后再导入SQL 审核失败 关于sql查询的问题~~求各位大大帮忙啊~~ SQLSERVER VDI恢复数据库错误 sql server数据库恢复问题? 数据统计问题 关于完整性备份和完整并异性备份未明确的问题
select * from tb a where 实施日期<'2008-9-30' and not exists(select 1 from tb where 材料编号=a.材料编号 and 实施日期>a.实施日期 and 实施日期<'2008-9-30')
insert into tb values(15 ,.4300 , '2008-10-01 00:00:00')
insert into tb values(17 ,.2700 , '2008-10-01 00:00:00')
insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00')
insert into tb values(24 ,.6500 , '2008-09-01 00:00:00')
insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00')
insert into tb values(26 ,.5700 , '2008-10-01 00:00:00')
insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00')
insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00')
insert into tb values(158 ,188.0000, '2008-10-01 00:00:00')
insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00')
insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00')
insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00')
insert into tb values(242 ,.1980 , '2008-10-01 00:00:00')
insert into tb values(243 ,.1870 , '2008-10-01 00:00:00')
insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')
goselect * from tb where abs(datediff(day,实施日期,'2008-9-30')) =
(select min(abs(datediff(day,实施日期,'2008-9-30'))) from tb)drop table tb /*
材料编号 单价 实施日期
----------- -------------------- ------------------------------------------------------
15 .4300 2008-10-01 00:00:00.000
17 .2700 2008-10-01 00:00:00.000
26 .5700 2008-10-01 00:00:00.000
27 4.2000 2008-10-01 00:00:00.000
156 2.1000 2008-10-01 00:00:00.000
158 188.0000 2008-10-01 00:00:00.000
159 26.0000 2008-10-01 00:00:00.000
242 .1980 2008-10-01 00:00:00.000
243 .1870 2008-10-01 00:00:00.000
283 .4200 2008-10-01 00:00:00.000(所影响的行数为 10 行)
*/
insert into tb values(15 ,.4300 , '2008-10-01 00:00:00')
insert into tb values(17 ,.2700 , '2008-10-01 00:00:00')
insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00')
insert into tb values(24 ,.6500 , '2008-09-01 00:00:00')
insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00')
insert into tb values(26 ,.5700 , '2008-10-01 00:00:00')
insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00')
insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00')
insert into tb values(158 ,188.0000, '2008-10-01 00:00:00')
insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00')
insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00')
insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00')
insert into tb values(242 ,.1980 , '2008-10-01 00:00:00')
insert into tb values(243 ,.1870 , '2008-10-01 00:00:00')
insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')
goselect m.* from tb m ,
(select 材料编号, min(abs(datediff(day,实施日期,'2008-9-30'))) cnt from tb group by 材料编号) n
where m.材料编号 = n.材料编号 and abs(datediff(day,m.实施日期,'2008-9-30')) = n.cntdrop table tb /*
材料编号 单价 实施日期
----------- -------------------- ------------------------------------------------------
15 .4300 2008-10-01 00:00:00.000
17 .2700 2008-10-01 00:00:00.000
22 1.3000 2008-09-01 00:00:00.000
24 .6400 2008-09-02 00:00:00.000
26 .5700 2008-10-01 00:00:00.000
27 4.2000 2008-10-01 00:00:00.000
156 2.1000 2008-10-01 00:00:00.000
158 188.0000 2008-10-01 00:00:00.000
159 26.0000 2008-10-01 00:00:00.000
176 1.4640 2008-09-01 00:00:00.000
226 1.8000 2008-09-01 00:00:00.000
242 .1980 2008-10-01 00:00:00.000
243 .1870 2008-10-01 00:00:00.000
283 .4200 2008-10-01 00:00:00.000(所影响的行数为 14 行)
*/
set @d='2008-09-30'
select * from tb where 实施日期 between @d-1 and @d+1
insert into tb values(15 ,.4300 , '2008-10-01 00:00:00')
insert into tb values(17 ,.2700 , '2008-10-01 00:00:00')
insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00')
insert into tb values(24 ,.6500 , '2008-09-01 00:00:00')
insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00')
insert into tb values(26 ,.5700 , '2008-10-01 00:00:00')
insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00')
insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00')
insert into tb values(158 ,188.0000, '2008-10-01 00:00:00')
insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00')
insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00')
insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00')
insert into tb values(242 ,.1980 , '2008-10-01 00:00:00')
insert into tb values(243 ,.1870 , '2008-10-01 00:00:00')
insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')
goselect * from tb a where 实施日期<'2008-9-30'
and not exists(select 1 from tb where 材料编号=a.材料编号 and 实施日期>a.实施日期)drop table tb 材料编号 单价 实施日期
----------- --------------------------------------- -----------------------
22 1.3000 2008-09-01 00:00:00.000
24 0.6400 2008-09-02 00:00:00.000
176 1.4640 2008-09-01 00:00:00.000
226 1.8000 2008-09-01 00:00:00.000(4 行受影响)
insert into tb values(15 ,.4300 , '2008-10-01 00:00:00')
insert into tb values(17 ,.2700 , '2008-10-01 00:00:00')
insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00')
insert into tb values(24 ,.6500 , '2008-09-01 00:00:00')
insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00')
insert into tb values(26 ,.5700 , '2008-10-01 00:00:00')
insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00')
insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00')
insert into tb values(158 ,188.0000, '2008-10-01 00:00:00')
insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00')
insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00')
insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00')
insert into tb values(242 ,.1980 , '2008-10-01 00:00:00')
insert into tb values(243 ,.1870 , '2008-10-01 00:00:00')
insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')
go
select * from (select * ,rn=row_number()over(
partition by 材料编号 order by 实施日期 desc ) from tb
where 实施日期<'2008-09-30') a where rn=1 drop table tb /*
22 1.3000 2008-09-01 00:00:00.000 1
24 0.6400 2008-09-02 00:00:00.000 1
176 1.4640 2008-09-01 00:00:00.000 1
226 1.8000 2008-09-01 00:00:00.000 1*/
10楼满足,而且是最快的,不过需要sql2005
insert into tb values(15 ,.4300 , '2008-10-01 00:00:00')
insert into tb values(17 ,.2700 , '2008-10-01 00:00:00')
insert into tb values(22 ,1.3000 , '2008-09-01 00:00:00')
insert into tb values(22 ,1.5000 , '2008-09-01 00:00:00')
insert into tb values(24 ,0.6400 , '2008-9-02 00:00:00')
insert into tb values(26 ,.5700 , '2008-10-01 00:00:00')
insert into tb values(27 ,4.2000 , '2008-10-01 00:00:00')
insert into tb values(156 ,2.1000 , '2008-10-01 00:00:00')
insert into tb values(158 ,188.0000, '2008-10-01 00:00:00')
insert into tb values(159 ,26.0000 , '2008-10-01 00:00:00')
insert into tb values(176 ,1.4640 , '2008-09-01 00:00:00')
insert into tb values(226 ,1.8000 , '2008-09-01 00:00:00')
insert into tb values(242 ,.1980 , '2008-10-01 00:00:00')
insert into tb values(243 ,.1870 , '2008-10-01 00:00:00')
insert into tb values(283 ,.4200 , '2008-10-01 00:00:00')
goselect *
from (select * from tb a where 实施日期<'2008-9-30'
and not exists(select 1 from tb where 材料编号=a.材料编号 and 实施日期>a.实施日期)) t
where not exists (select 1 from tb where 材料编号=t.材料编号 and 单价>t.单价)drop table tb 材料编号 单价 实施日期
----------- --------------------------------------- -----------------------
22 1.5000 2008-09-01 00:00:00.000
24 0.6400 2008-09-02 00:00:00.000
176 1.4640 2008-09-01 00:00:00.000
226 1.8000 2008-09-01 00:00:00.000(4 行受影响)取较大的单价,较小的单价那边改为小于号即可。