迭代来自临时表的派生值，以便它的值可以用于使用 for 循环的 where 条件
我可以从派生表中获取每个项目的总数，如下所示:
I can get the total for each of the items from a derived table like so:
declare @laneNum int
declare @startDate date = '2019-02-07'
declare @class int = 1
declare @id int
if OBJECT_ID('tempdb..#tempLaneNumber') IS NOT NULL
drop table [#tempLaneNumber]
create table #tempLaneNumber
(
LANE_NUMBER INT NULL
)
INSERT INTO #tempLaneNumber (LANE_NUMBER)
SELECT DISTINCT EXIT_LANE
FROM [dbo].[TOLL]
ORDER BY EXIT_LANE DESC
select l.LANE_NUMBER, COUNT(*)
from [dbo].[TOLL] t
inner join #tempLaneNumber l on t.EXIT_LANE = l.LANE_NUMBER
where convert(date, TRXN_DTIME) = @startDate
GROUP BY l.LANE_NUMBER
但我现在需要的是遍历每个派生值，以便我可以在语句中使用它，其中每个结果都可以放在变量中.这是我在当前代码中得到的......
But what I need now is to iterate through each of the derived values so I can use it in a statement where each result can be placed in a variable. This is what I get in my current code...
我需要将 LANE_NUMBER 4 放入 x4 变量，将 LANE_NUMBER 6 放入 x6 变量，依此类推.我如何获得它?
I need to put LANE_NUMBER 4 into x4 variable and LANE_NUMBER 6 into x6 variable and so forth. How do I get to it?
编辑
declare @laneNum int
declare @startDate date = '2019-02-07'
declare @class int = 1
declare @id int
if OBJECT_ID('tempdb..#tempLaneNumber') IS NOT NULL
drop table [#tempLaneNumber]
create table #tempLaneNumber
(
LANE_NUMBER INT NULL
)
INSERT INTO #tempLaneNumber (LANE_NUMBER)
SELECT DISTINCT EXIT_LANE
FROM [dbo].[TOLL]
ORDER BY EXIT_LANE DESC
;WITH CTE AS
(
select l.LANE_NUMBER, COUNT(*) CT
from [dbo].[TOLL] t
inner join #tempLaneNumber l on t.EXIT_LANE = l.LANE_NUMBER
where convert(date, TRXN_DTIME) = @startDate
GROUP BY l.LANE_NUMBER
)
SELECT * FROM CTE where LANE_NUMBER = 4
这是对的，但问题是我需要对值4"或6"或7"进行硬编码.这个样本有 4 个结果，所以没关系.但是如果我有 10 个或更多呢?
This is about right but the problem is I would need to hardcode the value "4" or "6" or "7". This sample has 4 results so it's okay. but what if I have 10 or more?
推荐答案
如果你想稍后使用结果，你可以使用如下临时表.
If you want to use the result later on you can use temp table like following.
create table #Results
(
LANE_NUMBER INT NULL,
[Count_LN] INT
)
INSERT INTO #Results
select l.LANE_NUMBER, COUNT(*) CT
from [dbo].[TOLL] t
inner join #tempLaneNumber l on t.EXIT_LANE = l.LANE_NUMBER
where convert(date, TRXN_DTIME) = @startDate
GROUP BY l.LANE_NUMBER
如果你想在下一个语句中立即使用输出，你可以像下面那样使用 CTE.
If you want to use the output immediately in the next statement, you can go for CTE like following.
;WITH CTE AS
(
select l.LANE_NUMBER, COUNT(*) CT
from [dbo].[TOLL] t
inner join #tempLaneNumber l on t.EXIT_LANE = l.LANE_NUMBER
where convert(date, TRXN_DTIME) = @startDate
GROUP BY l.LANE_NUMBER
)
SELECT * FROM CTE --USER YOUR CTE HERE
我无法完全理解您的要求，无论出于何种原因，如果您想迭代表并将每一行的列值存储到变量中，您可以尝试如下.
I am not able to understand your requirement fully, by any reason if you want to iterate the table and store every row's column value into a variable, you can try like following.
create table #Results
(
LANE_NUMBER INT NULL,
[Count_LN] INT
)
INSERT INTO #Results
select l.LANE_NUMBER, COUNT(*) CT
from [dbo].[TOLL] t
inner join #tempLaneNumber l on t.EXIT_LANE = l.LANE_NUMBER
where convert(date, TRXN_DTIME) = @startDate
GROUP BY l.LANE_NUMBER
declare @ln int
declare @ct int
While (Select Count(*) From #Results) > 0
Begin
select top 1 @ln = LANE_NUMBER, @ct = [Count_LN] from #Results
-- Use the variable @ln and @ct. For example, if you want to call a sp
-- exec call_someothersp @ln,@ct
Delete From #Results Where LANE_NUMBER = @ln and [Count_LN]=@ct
End
相关文章