我如何在Sql Server中使用光标来选择数据？ [英] How I Used Cursor In Sql Server To Select Data ?
问题描述
这样的表格： -
table like this :-
Car_No , Job_no , current_dis , prev_dist , Oil_type , Value
------- ------ ----------- --------- -------- -----
1234 1 10000 7000 type1 200
1234 2 15000 10000 type1 230
1234 3 17000 15000 type3 215
我想选择Car_no = 1234，最后移动10000公斤;
添加了代码块 - OriginalGriff [/ edit]
i want select Car_no = 1234 which moved in last 10000 kilo;
[edit]Code block added - OriginalGriff[/edit]
推荐答案
根据您添加到帖子中的评论，您似乎想要计算累积距离并仅报告对于任何特定的汽车，总行程距离超过10,000英里的行。
（如果不是这种情况，则对此解决方案发表评论，我将删除它）。
例如：如果我创建表
Based on the comment you added to your post it would appear that you want to work out cumulative distances and only report the rows that send the total journey distance over 10,000 miles for any specific car.
(If this is not the case then comment on this solution and I will remove it).
For example: If I create a table
create table Table1
(
Car_No int,
Job_no int,
current_dis bigint,
prev_dist bigint,
Oil_type varchar(25),
[Value] int
)并插入以下数据
insert into Table1 values
(1234, 1, 10000, 7000 , 'type1', 200),
(1234, 2, 15000, 10000, 'type1', 230),
(1234, 3, 17000, 15000, 'type3', 215),
(1234, 4, 10000, 1000 , 'type1', 200),
(1234, 5, 15000, 10000, 'type1', 230),
(1234, 6, 17000, 15000, 'type3', 215),
(1234, 7, 17000, 15000, 'type3', 215)然后你想报告Job_no = 3和5的数据，基于在下面的逻辑
Then you would want to report the data for Job_no = 3 and 5, based on the following "logic"
Job_No 1 distance = 3000 PLUS
Job_No 2 distance = 5000 PLUS
Job_No 3 distance = 2000
(total = 10000. Capture Job_No 3 and reset the counter)
Job_No 4 distance = 9000 PLUS
Job_No 5 distance = 5000
(total = 14000 which is > 10000. Capture Job_No 5 and reset the counter)
Job_No 6 distance = 2000 PLUS
Job_No 7 distance = 2000
total = 4000 which is < 10000. Ignore the data)
游标通常不是一个好主意，并且通常有使用它们的方法。但是因为这是一个如此随意的分组，我真的想不出没有光标可以实现的任何方式 - 呈现给你的其他解决方案是基于每个工作中的行进距离 - 再次，如果我误解了你是什么尝试做，评论这个解决方案，我将删除它。
您可以通过集成使用来缓解游标（锁定表，性能）的一些不好的事情临时表格，例如
Cursors are usually not a good idea, and there are usually ways around using them. But because this is such an arbitrary grouping I really can't think of any way it could be achieved without a cursor - the other solutions presented to you are based on the distance travelled within each Job - Again, if I have misunderstood what you are trying to do, comment on this solution and I will remove it.
You can mitigate some of the bad things about cursors (locking tables, performance) by integrating the use of temporary tables e.g.
-- Create a temporary table to hold the results
IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results
CREATE TABLE #results (Job_no int)
-- Populate another temporary table with some data for a specific car
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
SELECT
Job_no, current_dis - prev_dist as diff
INTO #temp
FROM Table1 T1
WHERE Car_No = 1234
-- Declare the variables for the cursor reads
DECLARE @Job int
DECLARE @diff bigint
-- Declare a variable for the cumulative total
DECLARE @cumDist bigint = 0
-- Declare the cursor on the temporary data table
DECLARE MyCursor CURSOR FOR
SELECT Job_No, Diff FROM #temp ORDER BY Job_no
OPEN MyCursor
FETCH NEXT FROM MyCursor into @job, @diff
-- Start looping through the table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cumDist = @cumDist + @diff
-- Check to see if we have the cumulative distance required
IF @cumDist >= 10000
BEGIN
Insert INTO #results VALUES (@job)
SET @cumDist = 0
END
FETCH NEXT FROM MyCursor into @job, @diff
END
CLOSE MyCursor
DEALLOCATE MyCursor
SELECT d.*
FROM #results r
INNER JOIN Table1 d ON r.Job_no = d.Job_no请注意，我m只使用光标临时表中需要的数据，并且还有一个非常薄的结果表。它只是最终的INNER JOIN，它返回所需的所有其他数据。结果：
Note that I'm only using the data I need in the temporary table for the cursor and also have a very thin "results" table. It's only the final INNER JOIN that returns all of the other data required. Results:
1234 3 17000 15000 type3 215
1234 5 15000 10000 type1 230
你提到你想要在一个水晶报告，所以我建议将所有这些都放入一个存储过程，将车号（可能是累积里程）作为参数
You've mentioned that you want to use this in a Crystal Report so I would suggest putting all of this into a Stored Procedure that takes the car number (and possibly the cumulative miles) as a parameter
首先请清除您的问题或使用改进问题小部件编辑你的问题并提供更好的信息。
简单查询：
SELECT Car_NO FROM table其中current_dist - prev_dist = 10000
它将以表格格式返回。
如果您想在CURSOR中使用任何其他场景
First of all please clear your question or Use the Improve question" widget to edit your question and provide better information.
Simple Query :
SELECT Car_NO FROM table where current_dist - prev_dist = 10000
it will return in table format.
if you want to use in CURSOR for any other scenrio
DECLARE Select_ CURSOR FOR
SELECT Car_NO FROM table where current_dist - prev_dist = 10000
OPEN Select_
declare @Car_NO varchar(15)
FETCH NEXT FROM Select_ into @Car_NO
WHILE @@FETCH_STATUS = 0
BEGIN
Print @Car_NO
FETCH NEXT FROM Select_ into @Car_NO
END
CLOSE Select_
DEALLOCATE Select_
这篇关于我如何在Sql Server中使用光标来选择数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
