VBA（Excel）vs SQL - 比较两行中的值（循环） [英] VBA (Excel) vs SQL - Comparing values in two rows (loop)

问题描述

这是否可能在SQL中？这似乎是一个简单的概念。 （我正在使用SQL Server 2005）

循环遍历SQL表中的所有行 - （这是我的vba excel脚本，但我裸体）
比较列m中的值如果一行= 65，下一个等于120增加j，并在另一列（列q）中打印该值。

  Sub InsertProductionCycle（）
 
 Dim LR As Long 
 Dim j As Integer 
 
j = 1 
 LR = Range（G& ; Rows.Count）.End（xlUp）.Row 
 
对于i = 1到LR步骤1 
 
单元格（i，Q）。Value = j 
 
如果单元格（i，M）。值= 65和单元格（i + 1，M）。值= 190然后
j = j + 1 
 End If 
 Next i 
 
 End Sub 
  

SQL专家的任何想法？我可以在SQL中执行此操作吗？
我的想法是：
也许循环是一个伸展，（我可以运行在一个sql作业），但你可以比较行和插入另一列中的j循环值。这是我被卡住的地方。

-Andrea

解决方案

是一个有趣的一个！可能有一种更有效的方式来执行此操作，但是您可以在单个基于集合的语句中执行，而不需要循环或游标。

假设表模拟您的数据，看起来像这样（其中我基本上是你的行号）：

 code> CREATE TABLE MyTable（i int，M int）
  

我使用自我加入匹配 i 行与 i + 1 行，并使用 COUNT Q 列。

 ; WITH data AS 
 SELECT ai，aM，bM as NextM 
，CASE WHEN aM = 65 AND bM = 190 THEN 1 ELSE 0 END AS shouldIncreaseQ 
 FROM MyTable a 
 LEFT OUTER JOIN MyTable b 
 ON ai + 1 = bi 
）
 SELECT data.M，data.NextM 
，（SELECT COUNT（*）+ 1 FROM data AS ref 
 WHERE ref。 shouldIncreaseQ = 1 AND ref.i< = data.i）as Q 
 FROM data 
  

如果需要的话，哟您可以使用 SELECT INTO 获取数据进入另一个表格供将来使用。

Is this possible in SQL? It seems like a simple concept. (I'm using SQL Server 2005)

Loop through all the rows in my SQL table - (this is my vba excel script but bare with me) compare the values in column m if one row =65 and the next equals 120 increase j, and print that value in another column (column q)

Sub InsertProductionCycle() 

Dim LR As Long 
Dim j As Integer 

j = 1 
LR = Range("G" & Rows.Count).End(xlUp).Row 

    For i = 1 To LR Step 1 

        Cells(i, "Q").Value = j 

        If Cells(i, "M").Value = 65 And Cells(i + 1, "M").Value = 190 Then 
            j = j + 1 
        End If 
    Next i 

End Sub 

Any thoughts from the SQL experts? Can I do this in SQL? My thoughts about this is: Perhaps the loop is a bit of a stretch, (I can run that in a sql job), but can you compare the rows and insert the j loop value in another column. Thats where I'm stuck.

-Andrea

解决方案

This was a fun one! There may be a more efficient way to do this, but you can do it in a single set-based statement without the need for loops or cursors.

Assuming a table that models your data that looks something like this (where i is basically your row number):

CREATE TABLE MyTable (i int, M int)

I used a self join to match i rows with i+1 rows and used a COUNT to figure out the Q column.

;WITH data AS (
    SELECT a.i, a.M, b.M as NextM
        , CASE WHEN a.M = 65 AND b.M = 190 THEN 1 ELSE 0 END AS shouldIncreaseQ
    FROM MyTable a
       LEFT OUTER JOIN MyTable b
          ON a.i + 1 = b.i
)
SELECT data.M, data.NextM
   , (SELECT COUNT(*) + 1 FROM data AS ref 
      WHERE ref.shouldIncreaseQ = 1 AND ref.i <= data.i) as Q
FROM data

If need be, you could use SELECT INTO to get the data into another table for future use.

这篇关于VBA（Excel）vs SQL - 比较两行中的值（循环）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！