-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
A:
2, 1, SELECT a. a. b. 1, 2. B. the FROM TABLE1 A
LEFT the JOIN
(SELECT 1, 2, 3 FROM TABLE2 B) ON b. 3=a. 3
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
2:
2, 1, SELECT a. a. b. 1, 2. B. the FROM TABLE1 A
LEFT the JOIN
DBO. TABLE2 ON b. 3=a. 3
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Cited above is quite simple. That is aimed at this situation, as far as possible to connect the form first. Then do field selection. The small white in the first case above...
If I understand right. Then take a look at the following question is for this reason: (write down the first method statements)
I wrote more than a set of statements table-valued functions, but found that the statement in the function, reference function queries slowly. If the statement from the function call out rate in contrast increased.
If the problem is out in my statement SELECT too much..
And I make a copy of my statement table-valued functions more. Who can give a look at the little elder brother, problem is out of here. I put together the next statement with PROMPT FORMAT.
The USE of [EBAP_XC2019test];
GO
/* * * * * * Object: UserDefinedFunction [dbo] [CC_JOMB] Script Date: Tuesday 2020/7/14 10:17:47 * * * * * */
The SET ANSI_NULLS ON;
GO
The SET QUOTED_IDENTIFIER ON;
GO
ALTER the FUNCTION [dbo] [CC_JOMB]
(
@ ORDERID VARCHAR (40)
)
RETURNS the @ TABLE TABLE
(
Serial number SMALLINT IDENTITY (1, 1),
ORDERID VARCHAR (60),
The CUSTOMER VARCHAR (50),
ARTICLENO VARCHAR (50),
The 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
The BEGIN
INSERT INTO @ TABLE
(
ORDERID,
The CUSTOMER,
ARTICLENO.
The 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'
The SELECT G.O rderId,
Right ustomer,
G.A rticleno,
G.E mployee,
F.G roupname,
A. d. ETAILNAME,
A. ENGTH,
Aleem walji IDTH,
A.C NT,
A.S URFTID,
A.K MS,
A. d. ETAILPRICE,
B.K MS AS SKMS,
B.S URFTID AS SXBTM,
B. leng AS SXBL,
B. the width AS SXBW,
C.K MS AS XKMS,
C.s. URFTID AS XXBTM,
C.F leng AS XXBL,
C.F width AS XXBW,
H.Z HKD,
D.K 1,
2, D.K
3, D.K
D.K 4,
D.K 5,
Um participant 1
The FROM T_BOM_ItemDetail A
LEFT the JOIN
(
SELECT the AS WJ, B
SURFTID,
Fleng,
Fwidth,
KMS,
ROW_NUMBER () OVER (PARTITION BY B ORDER BY B) AS a ROW
The FROM
(
The SELECT Partposstr AS B,
Surftid,
Fleng,
Fwidth,
Kms
The FROM T_BOM_ItemDetail
XB WHERE Kms LIKE '%'
) A
B)
ON B.W J=Amy polumbo artposstr
AND B.R OW=1
LEFT the JOIN
(
SELECT the AS WJ, B
SURFTID,
Fleng,
Fwidth,
KMS,
ROW_NUMBER () OVER (PARTITION BY B ORDER BY B) AS a ROW
The FROM
(
The SELECT Partposstr AS B,
Surftid,
Fleng,
Fwidth,
Kms
The FROM T_BOM_ItemDetail
XB WHERE Kms LIKE '%'
) A
C)
ON C.W J=Amy polumbo artposstr
AND C.R OW=2
LEFT the JOIN
(
The 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
The FROM
(
SELECT Id,
OrderId,
WgName,
CASE
The WHEN ORZ IN (' 90 ', '90') THEN
IPY
The ELSE
The IPX
END AS IPX,
Assembly_Id,
ROW_NUMBER () OVER (PARTITION BY Assembly_Id ORDER BY IPX ASC) AS rids
The FROM T_BOM_Hole
The WHERE (
DIA='40'
The OR DIA='35'
)
JL AND WgName LIKE '%'
) A
The PIVOT
(
MAX (IPX)
FOR the rid (IN [1], [2], [3], [4], [5])
) p
GROUP BY ORDERID,
WgName,
Assembly_Id
D)
Da ssembly_Id=ON Anderson d
LEFT the JOIN
(SELECT Assembly_Id, IPX AS G1 FROM T_BOM_Hole WHERE WgName='ZHWZ') E
ON E.A ssembly_Id=Anderson d
LEFT the JOIN
(SELECT Id, Groupname, Order_Id FROM T_BOM_Item) F
ON F.I d=Amy polumbo RODUCT_ID
LEFT the JOIN
(SELECT Id, OrderId, Customer, Articleno, Employee FROM T_BOM_Order) G
ON G.I d=F.O rder_Id
LEFT the JOIN
(
The SELECT Partposstr,
CASE
The WHEN Kms LIKE '% ZHZJ THEN
Fwidth
END AS ZHKD
The FROM T_BOM_ItemDetail
WHERE Kms LIKE '% ZHZJ'
H)
ON H.P artposstr=Amy polumbo artposstr
WHERE G.O rderId=@ ORDERID
AND INFO1='MB';
The RETURN;
The END;
Thank you doubt dispels doubts...
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull