Return list values with function

2019-09-16 13:01发布

问题:

I am trying to write the query and is working fine with the one card_nr. Example:

Declare @card_nr as nvarchar(50)

set @card_nr = '704487442952000472'

select 
    a.times, b.times 
from 
    (select 
         stat_id, times 
     from info 
     where card_nr = @card_nr) as a 
left outer join 
    (select 
         stat_id, times 
     from info 
     where card_nr = @card_nr) as b on a.stat_id = b.stat_id+1

And I got the correct result:

2014-02-04 11:20:00.000 NULL
2014-02-06 09:44:00.000 2014-02-04 11:20:00.000
2014-02-12 09:59:00.000 2014-02-06 09:44:00.000
2014-02-13 10:31:00.000 2014-02-12 09:59:00.000
2014-02-21 09:49:00.000 2014-02-13 10:31:00.000

I want to do it for every card_nr from a table list. So, first I have written the function:

alter FUNCTION tabletest
   ( @card_nr as nvarchar(50))
RETURNS smalldatetime 
AS BEGIN
    Declare @sqldata as smalldatetime

    select 
        @sqldata = b.times 
    from 
        (select 
             stat_id, times 
         from info where card_nr = @card_nr) as a 
    left outer join 
        (select 
             stat_id, times 
         from info where card_nr = @card_nr ) as b on a.stat_id = b.stat_id + 1

   RETURN @datas 
END

After the function I have tried select my query:

select name, times, dbo.tabletest(card_nr)
from dbo.info

And I got incorrect result:

User1   2014-02-04 11:20:00.000 2014-02-21 09:49:00
User1   2014-02-06 09:44:00.000 2014-02-21 09:49:00
User1   2014-02-12 09:59:00.000 2014-02-21 09:49:00
User1   2014-02-13 10:31:00.000 2014-02-21 09:49:00
User1   2014-02-21 09:49:00.000 2014-02-21 09:49:00
User2   2014-02-14 13:41:00.000 2014-02-28 12:20:00
User2   2014-02-24 11:46:00.000 2014-02-28 12:20:00
User2   2014-02-28 12:20:00.000 2014-02-28 12:20:00

I would like to get this:

User1   2014-02-04 11:20:00.000 NULL
User1   2014-02-06 09:44:00.000 2014-02-04 11:20:00.000
User1   2014-02-13 10:31:00.000 2014-02-12 09:59:00.000
User1   2014-02-13 10:31:00.000 2014-02-12 09:59:00.000
User1   2014-02-21 09:49:00.000 2014-02-13 10:31:00.000
User2   2014-02-14 13:41:00.000 NULL
User2   2014-02-24 11:46:00.000 2014-02-14 13:41:00.000
User2   2014-02-28 12:20:00.000 2014-02-24 11:46:00.000

回答1:

it should be possible to get the desired result using table info only:

 select name, times, 
 (select max(times) from info i2 where i2.times<i1.times and i1.name=i2.name and i2.card_nr=@card_nr ) 
 from info i1
 where  card_nr=@card_nr