34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE XRECE_HCSZ_C_PRICE(
CODE VARCHAR(10),
CUST VARCHAR(10),
NOUBA VARCHAR(10),
TDATE VARCHAR(10),
CUTANK DECIMAL(28,8),
PRICE DECIMAL(28,8))
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090115',3000.00000000,5.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090115',4000.00000000,6.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090115',4000.00000000,5.50000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090109',2000.00000000,4.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101', 0.00000000,0.07700000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101', 0.00000000,1.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101',1000.00000000,2.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101',1000.00000000,3.00000000)
CREATE TABLE XRECE(
CODE VARCHAR(10),
CUST VARCHAR(10),
NOUBA VARCHAR(10),
TDATE VARCHAR(10),
CUTANK DECIMAL(28,8))
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090101',0
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090101',1000
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090110',1000
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090115',3500
GO
SELECT RE.CODE,RE.CUST,RE.NOUBA,RE.TDATE,RE.CUTANK,
REP.PRICE
FROM XRECE re ,XRECE_HCSZ_C_PRICE rep
where re.CODE=rep.code and re.Cust=rep.cust and re.NOUBA=rep.NOUBA
and re.TDATE=rep.TDATE and re.CUTANK=rep.CUTANK
/*
(1 行受影响)
(1 行受影响)
CODE CUST NOUBA TDATE CUTANK PRICE
---------- ---------- ---------- ---------- --------------------------------------- ---------------------------------------
AB50052 CHT001U CDP001U 20090101 0.00000000 0.07700000
AB50052 CHT001U CDP001U 20090101 0.00000000 1.00000000
AB50052 CHT001U CDP001U 20090101 1000.00000000 2.00000000
AB50052 CHT001U CDP001U 20090101 1000.00000000 3.00000000
(4 行受影响)
*/
CREATE TABLE XRECE_HCSZ_C_PRICE(
CODE VARCHAR(10),
CUST VARCHAR(10),
NOUBA VARCHAR(10),
TDATE VARCHAR(10),
CUTANK DECIMAL(28,8),
PRICE DECIMAL(28,8))
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090115',3000.00000000,5.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090115',4000.00000000,6.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090115',4000.00000000,5.50000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090109',2000.00000000,4.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101', 0.00000000,0.07700000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101', 0.00000000,1.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101',1000.00000000,2.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101',1000.00000000,3.00000000)
CREATE TABLE XRECE(
CODE VARCHAR(10),
CUST VARCHAR(10),
NOUBA VARCHAR(10),
TDATE VARCHAR(10),
CUTANK DECIMAL(28,8))
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090101',0
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090101',1000
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090110',1000
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090115',3500
GO
SELECT
RE.CODE,RE.CUST,RE.NOUBA,RE.TDATE,RE.CUTANK,
ISNULL((SELECT
TOP 1 PRICE
FROM
XRECE_HCSZ_C_PRICE
WHERE
CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.TDATE AND CUTANK<=RE.CUTANK
AND
TDATE= (SELECT MAX(TDATE) FROM XRECE_HCSZ_C_PRICE
WHERE CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.TDATE)
AND
CUTANK=(SELECT MAX(CUTANK) FROM XRECE_HCSZ_C_PRICE
WHERE CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.TDATE AND CUTANK<=RE.CUTANK)
ORDER BY
TDATE DESC,CUTANK DESC,PRICE),0) AS PRICE
FROM
XRECE RE
GO
/*
CODE CUST NOUBA TDATE CUTANK PRICE
---------- ---------- ---------- ---------- ------------------------------ ------------------------------
AB50052 CHT001U CDP001U 20090101 .00000000 .07700000
AB50052 CHT001U CDP001U 20090101 1000.00000000 2.00000000
AB50052 CHT001U CDP001U 20090110 1000.00000000 .00000000
AB50052 CHT001U CDP001U 20090115 3500.00000000 5.00000000
*/
DROP TABLE XRECE_HCSZ_C_PRICE,XRECE
GO
CREATE TABLE XRECE_HCSZ_C_PRICE(
CODE VARCHAR(10),
CUST VARCHAR(10),
NOUBA VARCHAR(10),
TDATE VARCHAR(10),
CUTANK DECIMAL(28,8),
PRICE DECIMAL(28,8))
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090115',3000.00000000,5.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090115',4000.00000000,6.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090115',4000.00000000,5.50000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090109',2000.00000000,4.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101', 0.00000000,0.07700000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101', 0.00000000,1.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101',1000.00000000,2.00000000)
INSERT INTO XRECE_HCSZ_C_PRICE VALUES('AB50052','CHT001U','CDP001U','20090101',1000.00000000,3.00000000)
CREATE TABLE XRECE(
CODE VARCHAR(10),
CUST VARCHAR(10),
NOUBA VARCHAR(10),
TDATE VARCHAR(10),
CUTANK DECIMAL(28,8))
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090101',0
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090101',1000
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090110',1000
INSERT INTO XRECE SELECT 'AB50052','CHT001U','CDP001U','20090115',3500
GO
SELECT
RE.CODE,RE.CUST,RE.NOUBA,RE.TDATE,RE.CUTANK,
ISNULL((SELECT
TOP 1 PRICE
FROM
XRECE_HCSZ_C_PRICE
WHERE
CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.TDATE AND CUTANK<=RE.CUTANK
ORDER BY
TDATE DESC,CUTANK DESC,PRICE),0) AS PRICE
FROM
XRECE RE
GO
/*
CODE CUST NOUBA TDATE CUTANK PRICE
---------- ---------- ---------- ---------- ------------------------------ ------------------------------
AB50052 CHT001U CDP001U 20090101 .00000000 .07700000
AB50052 CHT001U CDP001U 20090101 1000.00000000 2.00000000
AB50052 CHT001U CDP001U 20090110 1000.00000000 2.00000000
AB50052 CHT001U CDP001U 20090115 3500.00000000 5.00000000
*/
-- 0这个取值的规则没分析清楚
DROP TABLE XRECE_HCSZ_C_PRICE,XRECE
GO
SELECT
RE.CODE,RE.CUST,RE.NOUBA,RE.TDATE,RE.CUTANK,
ISNULL(SELECT
TOP 1 PRICE
FROM
XRECE_HCSZ_C_PRICE
WHERE
CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.TDATE
ORDER BY
TDATE DESC,CUTANK DESC,PRICE),0) AS PRICE
FROM
XRECE RE
SELECT RE.CODE,RE.CUST,RE.NOUBA,RE.TDATE,RE.CUTANK,
REP.PRICE
FROM XRECE re ,XRECE_HCSZ_C_PRICE rep
where re.CODE=rep.code and re.Cust=rep.cust and re.NOUBA=rep.NOUBA
and re.TDATE=rep.TDATE and re.CUTANK=rep.CUTANK
SELECT RE.CODE,RE.CUST,RE.NOUBA,RE.TDATE,RE.CUTANK,
REP.PRICE
FROM re ,rep
where re.CODE=rep.code and re.Cust=rep.cust and re.NOUBA=rep.NOUBA
and re.TDATE=rep.TDATE and re.CUTANK=rep.CUTANK