从子查询

Question

我想从这个子查询返回的第二个项目返回第二项：

set GrowerNumber =(select top 1 tea_no 
    from ktda_file 
    where ktda_file.fosa_acno=customer.fosa_acno) 

Answer 1

SELECT * 
FROM customer 
OUTER APPLY (
    SELECT 
     Item1 = MAX(CASE WHEN t.RowNum = 1 THEN t.tea_no END), 
     Item2 = MAX(CASE WHEN t.RowNum = 2 THEN t.tea_no END) 
    FROM (
     SELECT tea_no, RowNum = ROW_NUMBER() OVER (ORDER BY tea_no) 
     FROM ktda_file 
     WHERE ktda_file.fosa_acno = customer.fosa_acno 
    ) t 
    WHERE RowNum < 3 
) t2 

Answer 2

也许这将帮助你：

(select tea_no from (select row_number() over (order by tea_no asc) as rowID, tea_no from ktda_file where ktda_file.fosa_acno=customer.fosa_acno)x where rowID = 2) 

Answer 3

嗨，你需要做的是这样这（如伊万所说，你没有在你的查询中订购，那么第二个是什么？）尽管如此，假设它是tea_no ...

GrowerNumber = (Select tea_no from (select row_number() over (order by tea_no) as row 
    from ktda_file where ktda_file.fosa_acno=customer.fosa_acno) as orderedlist 
    where row = 2) 

Answer 4

GrowerNumber = (
    select top 1 
    tea_no 
    from 
    (
     select top 2 
     tea_no 
     from 
     ktda_file 
     where ktda_file.fosa_acno=customer.fosa_acno 
    ) as a 
    order by 
    tea_no desc 
) 

Answer 5

试试这个

WITH CTE AS 
( 
SELECT top 2 tea_no from ktda_file 
WHERE ktda_file.fosa_acno=customer.fosa_acno 
ORDER BY tea_no ASC 
)  
SELECT TOP 1 tea_no FROM CTE ORDER BY tea_no DESC