如何在变量SQL服务器中存储固定行值

I have a table which can have a maximum of 5 rows and minimum 1 row. Now I need to store these rows in different variable like @v1,@v2,@v3,@v4,@v5. How can I do it?

我有一个表，最多可以有5行，最少1行。现在我需要将这些行存储在不同的变量中，如@ v1，@ v2，@ v3，@ v4，@ v5。我该怎么做？

The Table has only 1 column custid.

该表只有1列custid。

CustId
100
200
300
400

If the table contain only 1 row then @v1 should have that value and rest can be null.

如果表只包含1行，那么@ v1应该具有该值，而rest可以为null。

5 个解决方案

#1

You can use the following query:

您可以使用以下查询：

SELECT @v1 = MAX(CASE WHEN rn = 1 THEN CustId END),
       @v2 = MAX(CASE WHEN rn = 2 THEN CustId END),
       @v3 = MAX(CASE WHEN rn = 3 THEN CustId END),
       @v4 = MAX(CASE WHEN rn = 4 THEN CustId END),
       @v5 = MAX(CASE WHEN rn = 5 THEN CustId END)
FROM (
   SELECT CustId, ROW_NUMBER() OVER (ORDER BY CustId) AS rn
   FROM mytable ) t

Using ROW_NUMBER you assign a distinct number to each record of your table. Then, using conditional aggregates in an outer query you can consume this number in order to set each separate variable.

使用ROW_NUMBER为表的每条记录分配一个不同的数字。然后，在外部查询中使用条件聚合，您可以使用此数字来设置每个单独的变量。

If there are less than 5 rows, the corresponding variables will be set to NULL.

如果少于5行，则相应的变量将设置为NULL。

SQL Fiddle Demo

SQL小提琴演示

#2

If you have SQL Server 2012 or newer, you can try LAG() function.

如果您有SQL Server 2012或更高版本，则可以尝试LAG（）函数。

SELECT 
    @v1 = custID
  , @v2 = LAG(custID, 1) OVER (ORDER BY custID DESC)
  , @v3 = LAG(custID, 2) OVER (ORDER BY custID DESC)
  , @v4 = LAG(custID, 3) OVER (ORDER BY custID DESC)
  , @v5 = LAG(custID, 4) OVER (ORDER BY custID DESC)
FROM yourTable
ORDER BY CustID DESC

SQLFiddle Demo

SQLFiddle演示

#3

Try this..

尝试这个..

create table #tab
(
    custID int
)

insert into #tab
select 110 union all
select 120 union all
select 130 union all
select 140 union all
select 150

declare @v1 int,@v2 int, @v3 int, @v4 int, @v5 int

select @v1 = custID
from  #tab
order by custid 
OFFSET 0 row
FETCH  NEXT 1 ROW ONLY

select @v2 = custID
from  #tab
order by custid 
OFFSET 1 row
FETCH  NEXT 1 ROW ONLY

select @v3 = custID
from  #tab
order by custid 
OFFSET 2 row
FETCH  NEXT 1 ROW ONLY

select @v4 = custID
from  #tab
order by custid 
OFFSET 3 row
FETCH  NEXT 1 ROW ONLY


select @v5 = custID
from  #tab
order by custid 
OFFSET 4 row
FETCH  NEXT 1 ROW ONLY
select @v1,@v2,@v3,@v4,@v5

#4

we can achieve this even using PIVOT function and with the help Row_number

即使使用PIVOT函数和帮助Row_number，我们也可以实现这一点

declare @mytable  table  (CustId int)
insert into @mytable values
(100), (200), (300), (400),(500)


SELECT  [1] as V1, [2]as V2, [3]as V3, [4]as V4, [5]as V5
FROM
(SELECT CustId,ROW_NUMBER() OVER (ORDER BY CustId) AS value
    FROM @mytable ) AS SourceTable
PIVOT
(
max(CustId)
FOR value  IN ([1], [2], [3], [4],[5])
) AS PivotTable

#5

Just for fun, one brutal approach:

只是为了好玩，一个野蛮的方法：

Select @v1 = t1.CustID,
       @v2 = oa2.CustID, 
       @v3 = oa3.CustID, 
       @v4 = oa4.CustID, 
       @v5 = oa5.CustID
from Table t1
outer apply(select top 1 CustID from Table t2 where t2.CustID not in(t1.CustID) order by CustID) oa2
outer apply(select top 1 CustID from Table t3 where t3.CustID not in(t1.CustID, oa2.CustID) Order by CustID) oa3
outer apply(select top 1 CustID from Table t4 where t4.CustID not in(t1.CustID, oa2.CustID, oa3.CustID) Order by CustID) oa4
outer apply(select top 1 CustID from Table t5 where t5.CustID not in(t1.CustID, oa2.CustID, oa3.CustID, oa4.CustID) Order by CustID) oa5

#1