时间戳

首页 > 解决方案 > 代码说明 - 请

sql - 代码说明 - 请

问题描述

抱歉 - 我希望你不介意我问这个问题，但我是 SQL 新手 - 有人可以评论这些行在这段代码中的含义：

select b.recipeuuid
      ,b.proditemuuid
      ,b.proditemvalueuuid
  into #rectemp
  from rec_recipe as a
       inner join rec_recipevalue as b
               on b.recipeuuid=a.recipeuuid
              and b.[value] in ('Green','Yellow')
       inner join rec_proditem as c
               on c.proditemuuid=b.proditemuuid
       inner join rec_proditemvalue as d
               on d.proditemuuid=c.proditemuuid
              and d.proditemvalueuuid=b.proditemvalueuuid
              and d.[name]='SetupType'
;

update a
   set a.[value]='1'
  from rec_recipevalue as a
       inner join #rectemp as b
               on b.recipeuuid=a.recipeuuid
              and b.proditemuuid=a.proditemuuid
              and b.proditemvalueuuid=a.proditemvalueuuid
 where a.[value] in ('Green','Yellow')
;

update a
   set a.[name]='Normal'
  from rec_proditemvalue as a
       inner join #rectemp as b
               on b.proditemuuid=a.proditemuuid
              and b.proditemvalueuuid=a.proditemvalueuuid
 where a.[name]='SetupType'
;

drop table #rectemp;

我理解这段代码的大致思路。它是关于如何更新（或更改）两个不同列的值：属性和颜色。在我使用适当的 UUID 加入它们之前，这两个项目位于两个不同的表中。仅当 Attribute = 'SetupType' 且 Color = 'Green' 或 'Yellow' 时，才应进行更新</p>

我想将这两个值更改为：

属性 = '正常' 和颜色 = '1'</p>

标签： sqlsql-servertsql

解决方案

select b.recipeuuid
      ,b.proditemuuid
      ,b.proditemvalueuuid
  into #rectemp
  from rec_recipe as a
       inner join rec_recipevalue as b
               on b.recipeuuid=a.recipeuuid
              and b.[value] in ('Green','Yellow')
       inner join rec_proditem as c
               on c.proditemuuid=b.proditemuuid
       inner join rec_proditemvalue as d
               on d.proditemuuid=c.proditemuuid
              and d.proditemvalueuuid=b.proditemvalueuuid
              and d.[name]='SetupType'
;
***#rectemp is a temp table. Data imported into the table***
update a
   set a.[value]='1'
  from rec_recipevalue as a
       inner join #rectemp as b
               on b.recipeuuid=a.recipeuuid
              and b.proditemuuid=a.proditemuuid
              and b.proditemvalueuuid=a.proditemvalueuuid
 where a.[value] in ('Green','Yellow')
;
  ***Column Value is updated to 1 based on Value is Green or yellow***
update a
   set a.[name]='Normal'
  from rec_proditemvalue as a
       inner join #rectemp as b
               on b.proditemuuid=a.proditemuuid
              and b.proditemvalueuuid=a.proditemvalueuuid
 where a.[name]='SetupType'
;
  *** Name column is updated to Normal ***
drop table #rectemp;
  *** Temp Table is dropped***

推荐阅读