1.报价表格式如下,是按时间、地点、类别报单价,报价时间不确定,单价变动了才增加记录
报价时间 地点 类别 单价
2011-9-1 A仓 1001 100
2011-9-1 A仓 1002 120
2011-9-1 A仓 1003 150
2011-9-1 B仓 1001 105
2011-9-1 B仓 1002 125
2011-9-1 B仓 1003 155
2011-9-5 A仓 1001 110
2011-9-5 A仓 1002 130
2011-9-5 A仓 1003 160
2011-9-5 B仓 1001 115
2011-9-5 B仓 1002 135
2011-9-5 B仓 1003 165
2011-9-10……
2001-9-13……2.交易表格式如下
交易时间 地点 类别
2011-9-1 A仓 1001
2011-9-2 B仓 1003
2011-9-9 A仓 10023.所需结果如下,按交易时间的上一次报价为准交易时间 地点 类别 单价(现需确定这个数,括号内为应该取的数)
2011-9-1 A仓 1001 [100]
2011-9-2 B仓 1003 [155]
2011-9-9 A仓 1002 [130]
求一效率较高的语句,分不多谢谢了
报价时间 地点 类别 单价
2011-9-1 A仓 1001 100
2011-9-1 A仓 1002 120
2011-9-1 A仓 1003 150
2011-9-1 B仓 1001 105
2011-9-1 B仓 1002 125
2011-9-1 B仓 1003 155
2011-9-5 A仓 1001 110
2011-9-5 A仓 1002 130
2011-9-5 A仓 1003 160
2011-9-5 B仓 1001 115
2011-9-5 B仓 1002 135
2011-9-5 B仓 1003 165
2011-9-10……
2001-9-13……2.交易表格式如下
交易时间 地点 类别
2011-9-1 A仓 1001
2011-9-2 B仓 1003
2011-9-9 A仓 10023.所需结果如下,按交易时间的上一次报价为准交易时间 地点 类别 单价(现需确定这个数,括号内为应该取的数)
2011-9-1 A仓 1001 [100]
2011-9-2 B仓 1003 [155]
2011-9-9 A仓 1002 [130]
求一效率较高的语句,分不多谢谢了
select T2.交易时间,
T2.地点,
T2.类别,
'[' + cast(T1.单价 AS varchar) + ']' AS 单价
from 报价表 T1
inner join 交易表 T2
on T1.报价时间=T2.交易时间
and T1.地点=T2.地点
and T1.类别=T2.类别
b.地点,
b.类别,
'[ltrim(a.单价)]'as 单价
from 报价表 T1 ,交易表 T2
where
a.报价时间=b.交易时间 ---不知道这个条件是不是链接字段
and T1.地点=T2.地点
and T1.类别=T2.类别
你还是用最简单的select from where吧,还好理解一些
GO
/****** 对象: Table [dbo].[报价表] 脚本日期: 09/22/2011 09:39:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[报价表](
[时间] [nchar](50) NULL,
[地点] [nchar](50) NULL,
[类别] [nchar](50) NULL,
[单价] [int] NULL
) ON [PRIMARY]USE [CSDN]
GO
/****** 对象: Table [dbo].[交易表] 脚本日期: 09/22/2011 09:39:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[交易表](
[时间] [nchar](50) NULL,
[地点] [nchar](50) NULL,
[类别] [nchar](50) NULL
) ON [PRIMARY]
([时间]
,[地点]
,[类别]
,[单价])
select '2011-9-1', 'A仓', '1001', 100
union select '2011-9-1', 'A仓', '1002', 120
union select '2011-9-1', 'A仓', '1003', 150
union select '2011-9-1', 'B仓', '1001', 105
union select '2011-9-1', 'B仓', '1002', 125
union select '2011-9-1', 'B仓', '1003', 155
union select '2011-9-5', 'A仓', '1001', 110
union select '2011-9-5', 'A仓', '1002', 130
union select '2011-9-5', 'A仓', '1003', 160
union select '2011-9-5', 'B仓', '1001', 115
union select '2011-9-5', 'B仓', '1002', 135
union select '2011-9-5', 'B仓', '1003', 165INSERT INTO [CSDN].[dbo].[交易表]
([时间]
,[地点]
,[类别])
select '2011-9-1', 'A仓', '1001'
union select '2011-9-2', 'B仓', '1003'
union select '2011-9-9', 'A仓', '1002'
(
select
B.[时间] AS [B时间],B.[地点],B.[类别],A.[单价],A.[时间] AS [A时间]
from
[报价表] as A
,[交易表] AS B
WHERE
A.[地点]=B.[地点]
AND
A.[类别]=B.[类别]
AND
B.[时间]>=A.[时间]
) AS C
,
(select H.[地点],H.[类别],MAX([E时间])AS MX
from
(
select
F.[时间] AS [F时间],F.[地点],F.[类别],E.[单价],E.[时间] AS [E时间]
from
[报价表] As E
,[交易表] AS F
WHERE
E.[地点]=F.[地点]
AND
E.[类别]=F.[类别]
AND
F.[时间]>=E.[时间]
) AS H
group By H.[地点],H.[类别]) AS G
WHERE
C.[地点]=G.[地点]
AND
C.[类别]=G.[类别]
AND
C.[A时间]=G.MX一种笨方法 见笑了
create table ta (报价时间 datetime,地点 varchar (50),类别 varchar (50),单价 float)
insert into ta select '2011-9-1', 'A仓', 1001, 100
insert into ta select '2011-9-1', 'A仓', 1002, 120
insert into ta select '2011-9-1', 'A仓', 1003, 150
insert into ta select '2011-9-1', 'B仓', 1001, 105
insert into ta select '2011-9-1', 'B仓', 1002, 125
insert into ta select '2011-9-1', 'B仓', 1003, 155
insert into ta select '2011-9-5', 'A仓', 1001, 110
insert into ta select '2011-9-5', 'A仓', 1002, 130
insert into ta select '2011-9-5', 'A仓', 1003, 160
insert into ta select '2011-9-5', 'B仓', 1001, 115
insert into ta select '2011-9-5', 'B仓', 1002, 135
insert into ta select '2011-9-5', 'B仓', 1003, 165
if OBJECT_ID ('taa') is not null drop table taa
create table taa (交易时间 datetime, 地点 varchar (50), 类别 varchar (50))
insert into taa select '2011-9-1', 'A仓', '1001'
insert into taa select '2011-9-2', 'B仓', '1003'
insert into taa select '2011-9-9', 'A仓', '1002'SELECT * FROM TA
SELECT * FROM TAASELECT b.交易时间,b.地点,b.类别,a.单价 from ta a
right join taa b
on a.报价时间=b.交易时间 and a.地点=b.地点 and a.类别=b.类别drop table ta
drop table taa
create table ta (报价时间 datetime,地点 varchar (50),类别 varchar (50),单价 float)
insert into ta select '2011-9-1', 'A仓', 1001, 100
insert into ta select '2011-9-1', 'A仓', 1002, 120
insert into ta select '2011-9-1', 'A仓', 1003, 150
insert into ta select '2011-9-1', 'B仓', 1001, 105
insert into ta select '2011-9-1', 'B仓', 1002, 125
insert into ta select '2011-9-1', 'B仓', 1003, 155
insert into ta select '2011-9-5', 'A仓', 1001, 110
insert into ta select '2011-9-5', 'A仓', 1002, 130
insert into ta select '2011-9-5', 'A仓', 1003, 160
insert into ta select '2011-9-5', 'B仓', 1001, 115
insert into ta select '2011-9-5', 'B仓', 1002, 135
insert into ta select '2011-9-5', 'B仓', 1003, 165
if OBJECT_ID ('taa') is not null drop table taa
create table taa (交易时间 datetime, 地点 varchar (50), 类别 varchar (50))
insert into taa select '2011-9-1', 'A仓', '1001'
insert into taa select '2011-9-2', 'B仓', '1003'
insert into taa select '2011-9-9', 'A仓', '1002'SELECT * FROM TA
SELECT * FROM TAASELECT b.交易时间,b.地点,b.类别,a.单价 from ta a
right join taa b
on a.报价时间=b.交易时间 and a.地点=b.地点 and a.类别=b.类别drop table ta
drop table taa
create table ta (报价时间 datetime,地点 varchar (50),类别 varchar (50),单价 float)
insert into ta select '2011-9-1', 'A仓', 1001, 100
insert into ta select '2011-9-1', 'A仓', 1002, 120
insert into ta select '2011-9-1', 'A仓', 1003, 150
insert into ta select '2011-9-1', 'B仓', 1001, 105
insert into ta select '2011-9-1', 'B仓', 1002, 125
insert into ta select '2011-9-1', 'B仓', 1003, 155
insert into ta select '2011-9-5', 'A仓', 1001, 110
insert into ta select '2011-9-5', 'A仓', 1002, 130
insert into ta select '2011-9-5', 'A仓', 1003, 160
insert into ta select '2011-9-5', 'B仓', 1001, 115
insert into ta select '2011-9-5', 'B仓', 1002, 135
insert into ta select '2011-9-5', 'B仓', 1003, 165
if OBJECT_ID ('taa') is not null drop table taa
create table taa (交易时间 datetime, 地点 varchar (50), 类别 varchar (50))
insert into taa select '2011-9-1', 'A仓', '1001'
insert into taa select '2011-9-2', 'B仓', '1003'
insert into taa select '2011-9-9', 'A仓', '1002'SELECT * FROM TA
SELECT * FROM TAASELECT b.交易时间,b.地点,b.类别,a.单价 from ta a
right join taa b
on a.报价时间=b.交易时间 and a.地点=b.地点 and a.类别=b.类别drop table ta
drop table taa
--感觉除了年月日 ,应该还有时分等时间的,单单只是天的话感觉不是很精确,所以加上了时间
--创建测试数据
create table 报价表(报价时间 datetime,地点 varchar(20),类别 int,单价 money)
go
insert into 报价表(报价时间,地点,类别,单价)
select '2011-09-01 09:13:07.780','A仓',1001,100 union all
select '2011-09-01 10:13:07.780','A仓',1002,120 union all
select '2011-09-01 11:13:07.780','A仓',1003,150 union all
select '2011-09-01 12:13:07.780','B仓',1001,105 union all
select '2011-09-01 13:13:07.780','B仓',1002,125 union all
select '2011-09-01 14:13:07.780','B仓',1003,155 union all
select '2011-09-05 09:13:07.780','A仓',1001,110 union all
select '2011-09-05 10:13:07.780','A仓',1002,130 union all
select '2011-09-05 11:13:07.780','A仓',1003,160 union all
select '2011-09-05 12:13:07.780','B仓',1001,115 union all
select '2011-09-05 13:13:07.780','B仓',1003,135 union all
select '2011-09-05 14:13:07.780','B仓',1003,165
go
create table 交易表(交易时间 datetime,地点 varchar(20),类别 int)
go
insert into 交易表(交易时间,地点,类别)
select '2011-9-1 09:23:07.780','A仓',1001 union all
select '2011-9-2 09:23:07.780','B仓',1003 union all
select '2011-9-9 09:23:07.780','A仓',1002---查询
select b.交易时间,b.地点,b.类别,a.单价
from 交易表 b
join 报价表 a
on
a.地点=b.地点
and a.类别=b.类别
and b.交易时间>a.报价时间
join (
select min(时间差) 最小时间差,交易时间
from(
select datediff(mi,a.报价时间,b.交易时间) 时间差,a.报价时间,b.交易时间,b.地点,b.类别
from 交易表 b
join 报价表 a
on
a.地点=b.地点
and a.类别=b.类别
and b.交易时间>a.报价时间) c--一次交易前可能有多次报价,得出交易时间和报价时间的时间差
group by 交易时间) d --找出最小时间差也即是最近一次的报价时间差
on b.交易时间=d.交易时间
and datediff(mi,a.报价时间,b.交易时间)=d.最小时间差--查询结果
交易时间 地点 类别 单价
2011-09-01 09:23:07.780 A仓 1001 100.00
2011-09-02 09:23:07.780 B仓 1003 155.00
2011-09-09 09:23:07.780 A仓 1002 130.00
create table t1
(
shijian datetime,
didian varchar(10),
leibie varchar(4),
danjia int
)
insert into t1
select '2011-9-1', 'A仓', '1001', 100 union all
select '2011-9-1', 'A仓', '1002', 120 union all
select '2011-9-1', 'A仓', '1003', 150 union all
select '2011-9-1', 'B仓', '1001', 105 union all
select '2011-9-1', 'B仓', '1002', 125 union all
select '2011-9-1', 'B仓', '1003', 155 union all
select '2011-9-5', 'A仓', '1001', 110 union all
select '2011-9-5', 'A仓', '1002', 130 union all
select '2011-9-5', 'A仓', '1003', 160 union all
select '2011-9-5', 'B仓', '1001', 115 union all
select '2011-9-5', 'B仓', '1002', 135 union all
select '2011-9-5', 'B仓', '1003', 165
create table t2
(
shijian datetime,
didian varchar(10),
leibie varchar(4)
)
insert into t2
select '2011-9-1', 'A仓', '1001' union all
select '2011-9-2', 'B仓', '1003' union all
select '2011-9-9', 'A仓', '1002'
select * from t1
select * from t2;with aaa as
(select t2.shijian as shijian1,t2.didian,t2.leibie,t1.shijian as shijian2,t1.danjia
from t2 inner join t1 on t1.shijian<=t2.shijian and t1.leibie=t2.leibie and t1.didian=t2.didian)
select shijian1 as [交易时间],didian as [地点],leibie as [类别],'['+cast(danjia as varchar)+']' as [单价]
from aaa where not exists (select 1 from aaa as a where a.shijian1=aaa.shijian1 and a.didian=aaa.didian
and a.leibie=aaa.leibie and a.shijian2>aaa.shijian2)
谢谢各位的回答,可能我说得不够清楚,交易时间不一定等于报价时间,比如2011-9-1、2011-9-5、2011-9-10这几天有报价,那么2011-9-1至2011-9-4都是按2011-9-1的报价计算,我现在用的是笨方法,类似GOGODIY大大的NOT EXISTS,感觉执行效率可能太低了,getdate123大大的这种 min 时间差 应该效率要高吧,各位再给点意见
a.报价时间=b.交易时间 不是的,实际的数据是还有时分秒的,等于的可能性基本是0,应该说是a.报价时间<=b.交易时间 ,而且会有很多重复值,是取这中间的最大时间
可能是格式的原因,看半天才理解,应该是跟刚刚 getdate123大在的 min 时间差,也就 MAX时间