我先列列舉兩個情況.
--------------------------------------------------------------------------------------------
一:
SELECT A.1,A.2,B.1,B.2 FROM TABLE1 A
LEFT JOIN
(SELECT 1,2,3 FROM TABLE2 B) ON B.3=A.3
--------------------------------------------------------------------------------------------
二:
SELECT A.1,A.2,B.1,B.2 FROM TABLE1 A
LEFT JOIN
DBO.TABLE2 ON B.3=A.3
--------------------------------------------------------------------------------------------
上面列舉的情況是比較簡單的.也就是針對這種情況,盡量先將表格連接.然后再做欄位選擇.小白一直用的上面第一種情況.....
如果上面的我理解的對的話.那么看一下下面的問題是不是因為這個原因:(用第一種方法寫出來的陳述句)
我寫了一套多陳述句表值函式,但是發現陳述句在函式內,參考函式查詢速度很慢.如果把陳述句從函式內調出速度相比較之下有所提升.
這個問題是不是就是出在我陳述句SELECT 太多.而造成的.
另外我把我的多陳述句表值函式復制上來.哪位小哥哥能給看一下,問題是不是出在這里. 我把陳述句用PROMPT FORMAT整理了下.
USE [EBAP_XC2019test];
GO
/****** Object: UserDefinedFunction [dbo].[CC_JOMB] Script Date: 2020/7/14 星期二 10:17:47 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [dbo].[CC_JOMB]
(
@ORDERID VARCHAR(40)
)
RETURNS @TABLE TABLE
(
序號 SMALLINT IDENTITY(1, 1),
ORDERID VARCHAR(60),
CUSTOMER VARCHAR(50),
ARTICLENO VARCHAR(50),
EMPLOYEE VARCHAR(50),
GROUPNAME VARCHAR(200),
DETAILNAME VARCHAR(40),
LENGTH FLOAT,
WIDTH FLOAT,
CNT FLOAT,
SURFTID VARCHAR(40),
KMS VARCHAR(50),
DETAILPRICE FLOAT,
SKMS VARCHAR(50),
SXBTM VARCHAR(50),
SXBL VARCHAR(50),
SXBW VARCHAR(50),
XKMS VARCHAR(50),
XXBTM VARCHAR(50),
XXBL VARCHAR(50),
XXBW VARCHAR(50),
ZHKD VARCHAR(50),
K1 VARCHAR(50),
K2 VARCHAR(50),
K3 VARCHAR(50),
K4 VARCHAR(50),
K5 VARCHAR(50),
G1 VARCHAR(50)
)
AS
BEGIN
INSERT INTO @TABLE
(
ORDERID,
CUSTOMER,
ARTICLENO,
EMPLOYEE,
GROUPNAME,
DETAILNAME,
LENGTH,
WIDTH,
CNT,
SURFTID,
KMS,
DETAILPRICE,
SKMS,
SXBTM,
SXBL,
SXBW,
XKMS,
XXBTM,
XXBL,
XXBW,
ZHKD,
K1,
K2,
K3,
K4,
K5,
G1
)
--declare @orderid varchar(30)
--set @orderid='GHHH20060005V'
SELECT G.OrderId,
G.Customer,
G.Articleno,
G.Employee,
F.Groupname,
A.DETAILNAME,
A.LENGTH,
A.WIDTH,
A.CNT,
A.SURFTID,
A.KMS,
A.DETAILPRICE,
B.KMS AS SKMS,
B.SURFTID AS SXBTM,
B.Fleng AS SXBL,
B.Fwidth AS SXBW,
C.KMS AS XKMS,
C.SURFTID AS XXBTM,
C.Fleng AS XXBL,
C.Fwidth AS XXBW,
H.ZHKD,
D.K1,
D.K2,
D.K3,
D.K4,
D.K5,
E.G1
FROM T_BOM_ItemDetail A
LEFT JOIN
(
SELECT B AS WJ,
SURFTID,
Fleng,
Fwidth,
KMS,
ROW_NUMBER() OVER (PARTITION BY B ORDER BY B) AS ROW
FROM
(
SELECT Partposstr AS B,
Surftid,
Fleng,
Fwidth,
Kms
FROM T_BOM_ItemDetail
WHERE Kms LIKE '%XB'
) A
) B
ON B.WJ = A.Partposstr
AND B.ROW = 1
LEFT JOIN
(
SELECT B AS WJ,
SURFTID,
Fleng,
Fwidth,
KMS,
ROW_NUMBER() OVER (PARTITION BY B ORDER BY B) AS ROW
FROM
(
SELECT Partposstr AS B,
Surftid,
Fleng,
Fwidth,
Kms
FROM T_BOM_ItemDetail
WHERE Kms LIKE '%XB'
) A
) C
ON C.WJ = A.Partposstr
AND C.ROW = 2
LEFT JOIN
(
SELECT Assembly_Id,
ORDERID,
WgName,
MAX([1]) AS K1,
MAX([2]) AS K2,
MAX([3]) AS K3,
MAX([4]) AS K4,
MAX([5]) AS K5
FROM
(
SELECT Id,
OrderId,
WgName,
CASE
WHEN ORZ IN ( '90', '-90' ) THEN
IPY
ELSE
IPX
END AS IPX,
Assembly_Id,
ROW_NUMBER() OVER (PARTITION BY Assembly_Id ORDER BY IPX ASC) AS rid
FROM T_BOM_Hole
WHERE (
DIA = '40'
OR DIA = '35'
)
AND WgName LIKE 'JL%'
) A
PIVOT
(
MAX(IPX)
FOR rid IN ([1], [2], [3], [4], [5])
) p
GROUP BY ORDERID,
WgName,
Assembly_Id
) D
ON D.Assembly_Id = A.Id
LEFT JOIN
(SELECT Assembly_Id, IPX AS G1 FROM T_BOM_Hole WHERE WgName = 'ZHWZ') E
ON E.Assembly_Id = A.Id
LEFT JOIN
(SELECT Id, Groupname, Order_Id FROM T_BOM_Item) F
ON F.Id = A.PRODUCT_ID
LEFT JOIN
(SELECT Id, OrderId, Customer, Articleno, Employee FROM T_BOM_Order) G
ON G.Id = F.Order_Id
LEFT JOIN
(
SELECT Partposstr,
CASE
WHEN Kms LIKE '%ZHZJ' THEN
Fwidth
END AS ZHKD
FROM T_BOM_ItemDetail
WHERE Kms LIKE '%ZHZJ'
) H
ON H.Partposstr = A.Partposstr
WHERE G.OrderId = @ORDERID
AND INFO1 = 'MB';
RETURN;
END;
謝謝解惑..........
uj5u.com熱心網友回復：
請問該函式的應用場景是什么? 給個SQL.uj5u.com熱心網友回復：
場景是指的？uj5u.com熱心網友回復：
場景是指的。uj5u.com熱心網友回復：
場景是指的？uj5u.com熱心網友回復：
請提供呼叫這個函式的SQL陳述句.uj5u.com熱心網友回復：
SELECT * FROM JOMB('XXX')
uj5u.com熱心網友回復：
應該是
SELECT * FROM CC_JOMB('XXX')
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/17882.html
標籤：疑難問題
上一篇：求指點下
下一篇：后臺監控系統