在单个查询中更新和选择(续) [英] update and select in a single query(continued)

问题描述

我在我的

更新:在结果集中添加了 Sponsor_Name.

I asked the same question in my previous post but could not explain my requirement properly and hence did not get the desired result. I want to do something like this:

There are 14 levels in total.Each level can have any number of users. Thank you for the answers provided in the previous post, I tried them but none gave me the desired result. Please give me an idea how to start with this. As of now my stored procedure looks like this:

ALTER PROCEDURE [dbo].[UserTransac] 
@SponsorId varchar(20),
@UserId varchar(20),
@SponsorName varchar(50),
@Level int=1

AS
BEGIN
if not exists(select User_Id from UserTransaction)
    insert into UserTransaction(Sponsor_Id,User_Id,Level_No,Sponsor_Name) values(@SponsorId,@UserId,@Level,@SponsorName)
else
    insert into UserTransaction(Sponsor_Id,User_Id,Level_No,Sponsor_Name) values(@SponsorId,@UserId,@Level,@SponsorName)
    insert into UserTransaction(Sponsor_Id,User_Id,Level_No,Sponsor_Name) values(@SponsorId,@UserId,@Level+1,@SponsorName)

END

The insert statement goes on till level 14.
After executing the stored procedure my table would look something like this:

Any useful links or answers where I can get an idea how to go about with this would help. Thank you.

UPDATE:

Consider the last two records 11 and 12. For both the records sponsor Id is coming as RL9115. I want all the levelsto be displayed -ie something like this:

Hope this is somewhat clear to understand what I want to do

解决方案

The query below should handle getting the desired output with a CTE:

DECLARE @TransactionId INT = 11;

WITH levels AS (
    SELECT
        root.Sponsor_Id,
        root.Sponsor_Name,
        root.User_Id,
        root.Level_No
    FROM UserTransaction root
    WHERE root.Transaction_Id = @TransactionId
    UNION ALL
    SELECT 
        s.Sponsor_Id,
        s.Sponsor_Name,
        l.User_Id,
        Level_No = l.Level_No + 1
    FROM UserTransaction s
        INNER JOIN levels l ON l.Sponsor_Id = s.User_Id
)
SELECT DISTINCT *
FROM levels
ORDER BY Level_No;

Validated the answer using this SQL Fiddle.

UPDATE: Added Sponsor_Name to result set.

这篇关于在单个查询中更新和选择(续)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！