問題描述

我可以從派生表中獲取每個(gè)項(xiàng)目的總數(shù)，如下所示:

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

但我現(xiàn)在需要的是遍歷每個(gè)派生值，以便我可以在語句中使用它，其中每個(gè)結(jié)果都可以放在變量中.這是我在當(dāng)前代碼中得到的......

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

這是對(duì)的，但問題是我需要對(duì)值4"或6"或7"進(jìn)行硬編碼.這個(gè)樣本有 4 個(gè)結(jié)果，所以沒關(guān)系.但是如果我有 10 個(gè)或更多呢?

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?

推薦答案

如果你想稍后使用結(jié)果，你可以使用如下臨時(shí)表.

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

如果你想在下一個(gè)語句中立即使用輸出，你可以像下面那樣使用 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

我無法完全理解您的要求，無論出于何種原因，如果您想迭代表并將每一行的列值存儲(chǔ)到變量中，您可以嘗試如下.

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

這篇關(guān)于迭代來自臨時(shí)表的派生值，以便它的值可以用于使用 for 循環(huán)的 where 條件的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！