Question

所有人,

我从几张服务器桌上取得了成果,我需要知道,获得分类记录的最佳方式是什么。

我有以下记录;用户名称和单元编号作为父母记录,然后就每个单元都有多个子单元的儿童记录。问题一是单元记录与每个分单元的记录有关系。因此,我从我的储存程序得到以下记录:

NAME            Module  Sub-module
Derek Kenney    1       1
Derek Kenney    1       2
Derek Kenney    1       3
Derek Kenney    1       4
Derek Kenney    1       5

我想的是:

NAME            Module  Sub-module
Derek Kenney    1       1
                        2
                        3
                        4
                        5

我认为,我的问题属于我下面的阐述。

ALTER PROCEDURE [dbo].[uspGetReportData]

AS
BEGIN
    SELECT first_name, last_name, email, country_id, T_User_Info.user_id, T_Modules_Viewed.module_id as  modules viewed , T_Modules_Completed.module_id as  modules completed 
     FROM T_User_Info inner join T_Modules_Viewed on T_User_Info.user_id = T_Modules_Viewed.user_id left outer join T_Modules_Completed
 on T_Modules_Viewed.user_id = T_Modules_Completed.user_id
END

也许《反歧视法》有办法。获取结果的网络使我想到,我不知道。希望得到任何帮助。

Derek Kenney

Answer 1

试图这样做:

DECLARE @YourData table (YourName varchar(20), Module char(1), SubModule char(1))

INSERT INTO @YourData VALUES ( Derek Kenney ,1,1)
INSERT INTO @YourData VALUES ( Derek Kenney ,1,2)
INSERT INTO @YourData VALUES ( Derek Kenney ,1,3)
INSERT INTO @YourData VALUES ( Derek Kenney ,1,4)
INSERT INTO @YourData VALUES ( Derek Kenney ,1,5)
INSERT INTO @YourData VALUES ( Derek Kenney ,2,1)
INSERT INTO @YourData VALUES ( Derek Kenney ,2,2)
INSERT INTO @YourData VALUES ( Derek Kenney ,2,3)
INSERT INTO @YourData VALUES ( Joe Smith    ,1,1)
INSERT INTO @YourData VALUES ( Joe Smith    ,2,1)
INSERT INTO @YourData VALUES ( Joe Smith    ,2,2)


SELECT
    CASE WHEN NameRank=1 /*OR NameModuleRank=1*/ THEN YourName ELSE    END AS YourName
        ,CASE NameModuleRank WHEN 1 THEN Module ELSE    END AS Module
        ,SubModule
    FROM (SELECT
              YourName,Module,SubModule
                  ,ROW_NUMBER() OVER(partition by YourName ORDER BY YourName) AS NameRank
                  ,ROW_NUMBER() OVER(partition by YourName,Module ORDER BY YourName,Module) AS NameModuleRank
              FROM @YourData
         ) dt

OUTPUT:

YourName             Module SubModule
-------------------- ------ ---------
Derek Kenney         1      1
                            2
                            3
                            4
                            5
                     2      1
                            2
                            3
Joe Smith            1      1
                     2      1
                            2

(11 row(s) affected)

如果你想要的话,你就能够在单元改变时展示你的民族。

友情链接