34,575
社区成员
发帖
与我相关
我的任务
分享
AND TDATE= (SELECT MAX(TDATE) FROM TABLE1
WHERE CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA
AND TDATE<=RE.CUSTOMERISSUEDATE)
AND CUTANK=(SELECT MAX(CUTANK) FROM TABLE1
WHERE CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA
AND TDATE=(SELECT MAX(TDATE) FROM TABLE1 WHERE CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.CUSTOMERISSUEDATE)
AND CUTANK<=RE.CUTANK)
SELECT ISNULL(
(select top 1 price from TABLE1
where CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA
AND TDATE<=RE.CUSTOMERISSUEDATE and CUTANK<=RE.CUTANK
order by TDATE desc,CUTANK desc,price)
,0)
FROM XRECE RE
--6楼,你好:
--你的代码测试结果如下:
/*
TABLE1表结构(无主键):
CODE VARCHAR(25)
CUST VARCHAR(12)
NOUBA VARCHAR(12)
TDATE VARCHAR(8)
CUTANK DECIMAL(28,8)
PRICE DECIMAL(28,8)
XRECE表结构(SORDER、EDA为主键):
SORDER VARCHAR(18)
EDA INT
CODE VARCHAR(25)
CUST VARCHAR(12)
NOUBA VARCHAR(12)
CUSTOMERISSUEDATE VARCHAR(8)
CUTANK DECIMAL(28,8)
......
*/
--测试数据:
CREATE TABLE TABLE1(
CODE VARCHAR(25)
,CUST VARCHAR(12)
,NOUBA VARCHAR(12)
,TDATE VARCHAR(8)
,CUTANK DECIMAL(28,8)
,PRICE DECIMAL(28,8))
INSERT INTO TABLE1
SELECT 'AB00001','CSH001U','CWR002R','20100501',5000,1.1 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100511',5000,1.1 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100511',6000,1.1 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100511',6000,1.3 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100521',5000,1.1 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100521',5500,1.1 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100521',5500,1.25 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100521',4000,1.4 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100521',4200,1.1 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100521',4500,1.2 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100521',4600,1.2 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100521',4600,1.3 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100521',4600,1.4 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100601',5000,1.1 UNION
SELECT 'AB00001','CSH001U','CWR002R','20100601',5000,1.1
CREATE TABLE XRECE(
SORDER VARCHAR(18)
,EDA INT
,CODE VARCHAR(25)
,CUST VARCHAR(12)
,NOUBA VARCHAR(12)
,CUSTOMERISSUEDATE VARCHAR(8)
,CUTANK DECIMAL(28,8))
INSERT INTO XRECE
SELECT 'RR123456',0,'AB00001','CSH001U','CWR002R','20100525',4600
--我希望得到的是1.2
SELECT A.* FROM TABLE1 A
WHERE EXISTS
(
SELECT 1 FROM XRECE C WHERE
CODE=A.CODE AND CUST=A.CUST AND NOUBA=A.NOUBA AND A.TDATE<=C.CUSTOMERISSUEDATE
AND A.CUTANK<=
(SELECT MIN(CUTANK) FROM XRECE D WHERE CODE=C.CODE AND CUST=C.CUST AND NOUBA=C.NOUBA AND A.TDATE<=D.CUSTOMERISSUEDATE)
)
DROP TABLE TABLE1,XRECE
SELECT A.* FROM TABLE1 A
WHERE EXISTS
(
SELECT 1 FROM XRECE C WHERE
CODE=A.CODE AND CUST=A.CUST AND NOUBA=A.NOUBA AND A.TDATE<=C.CUSTOMERISSUEDATE
AND CUTANK<=
(SELECT MIN(CUTANK) FROM XRECE D WHERE CODE=C.CODE AND CUST=C.CUST AND NOUBA=C.NOUBA AND A.TDATE<=D.CUSTOMERISSUEDATE)
)
SELECT ISNULL(( SELECT TOP 1
PRICE
FROM TABLE1
WHERE CODE = RE.CODE
AND CUST = RE.CUST
AND NOUBA = RE.NOUBA
AND TDATE <= RE.CUSTOMERISSUEDATE
AND CUTANK <= RE.CUTANK
AND TDATE = ( SELECT MAX(TDATE)
FROM TABLE1
WHERE CODE = RE.CODE
AND CUST = RE.CUST
AND NOUBA = RE.NOUBA
AND TDATE <= RE.CUSTOMERISSUEDATE
)
AND CUTANK = ( SELECT MAX(CUTANK)
FROM TABLE1
WHERE CODE = RE.CODE
AND CUST = RE.CUST
AND NOUBA = RE.NOUBA
AND TDATE = ( SELECT
MAX(TDATE)
FROM
TABLE1
WHERE
CODE = RE.CODE
AND CUST = RE.CUST
AND NOUBA = RE.NOUBA
AND TDATE <= RE.CUSTOMERISSUEDATE
)
AND CUTANK <= RE.CUTANK
)
ORDER BY TDATE DESC ,
CUTANK DESC ,
PRICE
), 0)
FROM XRECE RE