问题描述
这是否可能在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
如果需要的话,哟您可以使用获取数据进入另一个表格供将来使用。
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 - 比较两行中的值(循环)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!