Can view using a temporary table?-CodePudding

View of data needs to be the query result set, ranks according to the condition to update some value, finally return to update the result value,
What method can be realized in view of the above logic

CodePudding user response:

Thus lost the meaning of the view, the view function normally, if some of the data read once and the second will be read from the cache, speed is relatively fast, but if you want to define the data in the view, so not only lost the view of meaning, and the efficiency is not high, rather than such write functions or stored procedures to perform better

CodePudding user response:

 
The create view/view name 
As 
With t as 
(
[the original query] 
) 
The select list [field] - & gt; According to the condition update some ranks the value of the 
The from t - & gt; The results of a query set

CodePudding user response:

refer to the second floor started the first response:

 
The create view/view name 
As 
With t as 
(
[the original query] 
) 
The select list [field] - & gt; According to the condition update some ranks the value of the 
The from t - & gt; The results of a query set

, for example, for example, I this is what the previous query operation, first put the results in the new view, how to deal with the

SELECT 1 AS ColA, '001' AS ColB
INTO # t
The FROM dbo. Aaa

UPDATE # t SET ColA=100 WHERE ColB='001'

SELECT * FROM # t

DROP TABLE # t

CodePudding user response:

 create view view_aaa 
As 
Select a case when colB='001' then 100 else colA as colA 
, colB 
The from dbo. Aaa 
Go

CodePudding user response:

reference 4 floor gypsy song response:

 create view view_aaa 
As 
Select a case when colB='001' then 100 else colA as colA 
, colB 
The from dbo. Aaa 
Go

I just illustrate using a temporary table, is actually the
The SET @ LOWVALUE=https://bbs.csdn.net/topics/@LOWVALUE + '%'
SELECT a.F MOBILLNO AS work order code/* work order code */,
A.F MOENTRYSEQ AS ERP_MO_SEQ,
W.F NUMBER AS finished product coding,
A.F QTY AS plan number/quantity */* plan,
ROW_NUMBER () OVER (PARTITION BY FREPLACEGROUP, a.F BILLNO ORDER BY FREPLACEGROUP, a.F BILLNO, aa. FENTRYID) AS main ingredient,
W1. FNUMBER AS material code,
FNUMERATOR/FDENOMINATOR AS dosage/* */dosage,
In CASE the WHEN FISSUETYPE=7 THEN 0
The ELSE/* a2. FNOPICKEDQTY */aa. FSTDQTY
END AS net demand quantity,
In CASE the WHEN FISSUETYPE=7 THEN 0
The ELSE/* a2. FNOPICKEDQTY */aa. FMUSTQTY
END AS actual demand quantity,
Aa. FREPLACEGROUP/* will */,
Aa. FSEQ/* */,
Aa FENTRYID AS SEQ_ENTRY_CODE,
A.F ID,
A.F MOID,
A.F MOENTRYID,
A1. FPOSITIONNO
INTO # b
The FROM T_PRD_PPBOM a
INNER JOIN T_PRD_PPBOMENTRY aa ON a.F ID=aa. The FID
LEFT the JOIN T_PRD_PPBOMENTRY_C a1 ON a1. FENTRYID=aa. FENTRYID
LEFT the JOIN T_PRD_PPBOMENTRY_Q a2 ON a2. FENTRYID=aa. FENTRYID
LEFT the JOIN T_BD_MATERIAL w ON w.F MATERIALID=a.F MATERIALID
LEFT the JOIN T_BD_MATERIAL w1 ON w1. FMATERIALID=aa. FMATERIALID
WHERE 1=1
AND a.F MOBILLNO LIKE @ LOWVALUE
AND a.F MOENTRYSEQ=@ SEQ

SELECT the repair order coding AS ERP_MO,
The finished product coding AS PROD_ID,
Project number AS P_QTY,
The CASE whether the WHEN cooked!=1 THEN material code
The ELSE material code
END AS MTRL_ID,
CASE whether the WHEN cooked=1 THEN 'Y'
The ELSE 'N'
END AS IS_MAIN,
CASE whether the WHEN cooked=1 THEN material code
END AS MAIN_ID,
DOSAGE AS DOSAGE,
Net demand quantity AS NN_QTY,
Actual demand quantity AS TN_QTY,
FREPLACEGROUP/* will */,
FSEQ AS SEQ/* */,
FPOSITIONNO AS POINT_STR,
ERP_MO_SEQ/* production order line number */,
SEQ_ENTRY_CODE
INTO # bb
The FROM # b
WHERE 1=1
AND ((SELECT FSTATUS
The FROM T_PRD_MO g
INNER JOIN T_PRD_MOENTRY gg ON g.F ID=gg. FID
INNER JOIN T_PRD_MOENTRY_A gg_A ON gg_A. FENTRYID=gg. FENTRYID
WHERE g.F ID=FMOID
AND gg. FENTRYID=FMOENTRYID
)=3
OR (SELECT FSTATUS
The FROM T_PRD_MO g
INNER JOIN T_PRD_MOENTRY gg ON g.F ID=gg. FID
INNER JOIN T_PRD_MOENTRY_A gg_A ON gg_A. FENTRYID=gg. FENTRYID
WHERE g.F ID=FMOID
AND gg. FENTRYID=FMOENTRYID
)=4
)
AND the repair order code LIKE @ LOWVALUE
AND ERP_MO_SEQ=@ SEQ

SELECT *
INTO # bb2
The FROM # bb
WHERE # bb. IS_MAIN='Y'

UPDATE # bb
SET # bb. MAIN_ID=arjun TRL_ID
The FROM # bb2 a
WHERE # bb. IS_MAIN='N'
AND # bb. FREPLACEGROUP=a.F REPLACEGROUP
AND # bb. ERP_MO=a.E RP_MO
AND # bb. ERP_MO_SEQ=a.E RP_MO_SEQ

SELECT *
The FROM # bb
WHERE 1=1 -- AND NN_QTY & gt; 0
AND ERP_MO LIKE @ LOWVALUE
AND ERP_MO_SEQ=@ SEQ

DROP TABLE # b
DROP TABLE # bb
DROP TABLE # bb2

CodePudding user response:

You this is more complex, is the best way to make statements and stored procedures after processing is completed, in the result list, users can view the results table directly,

If you have a little amount of data, can be in with multiple statements table-valued functions instead of,
But the efficiency is not high,

CodePudding user response:

Is to use stored procedures before, but now the system migration to the cloud, cannot access the database directly, can only do view documents (the underlying view) query,
So must be converted to the view,

CodePudding user response:

refer to 6th floor gypsy song response:

this is complicated, you is the best way to make statements and stored procedures after processing is completed, in the result list, users can view the results table directly,

If you have a little amount of data, can be in with multiple statements table-valued functions instead of,
But the efficiency is not high,

CodePudding user response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnull