这个SQL能优化吗?

funsuzhou 2010-07-13 08:41:02
/*
目的:求TABLE1表中与XRECE表有相同CODE、CUST、NOUBA,且TDATE<=XRECE表的CUSTOMERISSUEDATE,且CUTANK<=XRECE表的CUTANK的最小PRICE
注意:说出来有点绕。
假设CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.CUSTOMERISSUEDATE AND CUTANK<=RE.CUTANK为范围1,必须在范围1内再找CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.CUSTOMERISSUEDATE的最大TDATE的记录,假设是范围2,然后必须在范围2里再找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的最大CUTANK的记录,假设是范围3,最后我要的是范围3里面的最小PRICE
*/
--以下是原来的代码,性能超低,不知能否改善一下?
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
...全文
132 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
funsuzhou 2010-07-13
  • 打赏
  • 举报
回复
13楼,我现在看我好像是有多余代码(上面我自己贴的),难道是我把自己给绕进去了?我当时为什么要加多余的代码,晕。
funsuzhou 2010-07-13
  • 打赏
  • 举报
回复
感谢13楼,也就是说以下代码是没必要的,是吗?:

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)
永生天地 2010-07-13
  • 打赏
  • 举报
回复
说得太绕
看看这个吧
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
  • 打赏
  • 举报
回复
[Quote]
假设CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.CUSTOMERISSUEDATE AND CUTANK<=RE.CUTANK为范围1,必须在范围1内再找CODE=RE.CODE AND CUST=RE.CUST AND NOUBA=RE.NOUBA AND TDATE<=RE.CUSTOMERISSUEDATE的最大TDATE的记录,假设是范围2,然后必须在范围2里再找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的最大CUTANK的记录,假设是范围3,最后我要的是范围3里面的最小PRICE
[/Quote]

考虑下CASE是否可以用...
funsuzhou 2010-07-13
  • 打赏
  • 举报
回复

--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

--结果:
/*
CODE CUST NOUBA TDATE CUTANK PRICE
------------------------- ------------ ------------ -------- --------------------------------------- ---------------------------------------
AB00001 CSH001U CWR002R 20100521 4000.00000000 1.40000000
AB00001 CSH001U CWR002R 20100521 4200.00000000 1.10000000
AB00001 CSH001U CWR002R 20100521 4500.00000000 1.20000000
AB00001 CSH001U CWR002R 20100521 4600.00000000 1.20000000
AB00001 CSH001U CWR002R 20100521 4600.00000000 1.30000000
AB00001 CSH001U CWR002R 20100521 4600.00000000 1.40000000

(6 行受影响)
*/
ChinaITOldMan 2010-07-13
  • 打赏
  • 举报
回复
learning
funsuzhou 2010-07-13
  • 打赏
  • 举报
回复
即相同CODE、CUST、NOUBA,且TDATE<=XRECE表里的CUSTOMERISSUEDATE的最大TDATE,且CUTANK<=XRECE表里的CUTANK的最大CUTANK的最小PRICE
主要是TDATE、CUTANK、PRICE必须在上个集合中再进一步筛选,这可能就是难点。
funsuzhou 2010-07-13
  • 打赏
  • 举报
回复
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)
,TDATE VARCHAR(8)
,CUTANK DECIMAL(28,8)
INSERT INTO XRECE
SELECT 'AB00001','CSH001U','CWR002R','20100525',4600

我希望得到的是1.2
永生天地 2010-07-13
  • 打赏
  • 举报
回复
看了一会儿,已经头晕了
SQL77 2010-07-13
  • 打赏
  • 举报
回复
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)
)
SQL77 2010-07-13
  • 打赏
  • 举报
回复
目的:求TABLE1表中与XRECE表有相同CODE、CUST、NOUBA,且TDATE<=XRECE表的CUSTOMERISSUEDATE,且CUTANK<=XRECE表的CUTANK的最小PRICE


根据你的描述,

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)
)
ask_chang 2010-07-13
  • 打赏
  • 举报
回复
给点实际的数据,好分析?
百年树人 2010-07-13
  • 打赏
  • 举报
回复
看起来确认比较绕,最好能提供表结构,测试数据,算法和你要的结果
Mr_Nice 2010-07-13
  • 打赏
  • 举报
回复
整理了一下代码,大家参考!

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

Mr_Nice 2010-07-13
  • 打赏
  • 举报
回复
子查询是会有更多的消耗的。

如果是2005 ,lz考虑用CTE看看。

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧