sql - 如何在连接案例 SQL Server 上生成 1 行输出

Question

我的 SQL Server 数据库中有两个表。

电磁脉冲表...

ID NAME A
-- ---- -
01 Tony Y
02 Fred N

和组成员资格表 (GRP)...

ID GRP    START    FINISH   KEYSK
-- ------ -------- -------- -----
01   GRP1 01/01/15 31/01/15 00001
01   GRP2 01/02/15 28/02/15 00002
01   GRP3 01/03/15 30/04/15 00003
01   GRP2 01/15/15 31/12/99 00004
01   GRPA 01/01/15 28/02/15 00005
01   GRPB 01/03/15 31/03/15 00006
01   GRPC 01/14/15 30/04/15 00007
01   GRPB 01/15/15 31/12/99 00008
02  GRPII 01/01/15 28/02/15 00005
02 GRPIII 01/03/15 31/03/15 00006
02  GRPIV 01/14/15 30/04/15 00007
02   GRPV 01/15/15 31/12/99 00008

我正在尝试构建一个查询来生成以下输出，该输出将从上面选择行 00004 和 00008，因为它们的开始日期和结束日期是当前的...

NAME GRP123 GRPABC GRPROMAN
---- ------ ------ --------
Tony   GRP2   GRPB      N/A

但是我的查询返回以下内容...

NAME GRP123 GRPABC GRPROMAN
---- ------ ------ --------
Tony   GRP2    N/A      N/A
Tony    N/A   GRPB      N/A

这是我尝试的查询，我可能会遇到复杂的事情......

    select NAME, 
           "GRP123" = case 
                          when GRP123.GRP='GRP1' then 'GRP1' 
                          when GRP123.GRP='GRP2' then 'GRP2' 
                          when GRP123.GRP='GRP3' then 'GRP3' 
                          else 'N/A' end,
           "GRPABC" = case 
                          when GRPABC.GRP='GRP1' then 'GRP1' 
                          when GRPABC.GRP='GRP2' then 'GRP2' 
                          when GRPABC.GRP='GRP3' then 'GRP3' 
                          else 'N/A' end,
           "GRPROMAN" = case 
                          when GRPROMAN.GRP='GRPI' then 'GRPI' 
                          when GRPROMAN.GRP='GRPII' then 'GRPII' 
                          when GRPROMAN.GRP='GRPIII' then 'GRPIII' 
                          when GRPROMAN.GRP='GRPIV' then 'GRPIV' 
                          when GRPROMAN.GRP='GRPV' then 'GRPV' 
                          when GRPROMAN.GRP='GRPVI' then 'GRPVI' 
                          else 'N/A' end,
      from EMP
full outer join GRP as GRP123 on EMP.ID = GRP123.ID and GRP123.GRP in ('GRP1', 'GRP2', 'GRP3')
full outer join GRP as GRPABC on EMP.ID = GRPABC.ID and GRPABC.GRP in ('GRPA', 'GRPB', 'GRPC')
full outer join GRP as GRPROMAN on EMP.ID = GRPROMAN.ID and GRPROMAN.GRP in ('GRPI', 'GRPII', 'GRPIII', 'GRPIV', 'GRPV', 'GRPVI')
     where EMP.A = 'T'
  order by EMP.NAME
;

任何帮助/指导将不胜感激。

可选的改进输出可能是...

NAME GRP123 GRP123START GRPABC GRPABCSTART GRPROMAN GRPROMANSTART
---- ------ ----------- ------ ----------- -------- -------------
Tony   GRP2    01/15/15   GRPB    01/15/15      N/A           N/A

谢谢，AjN3806

Answer 1

您可以在每一列中使用子查询，如下所示:

SELECT
    NAME,
    GRP123 = ISNULL((
        SELECT TOP 1
            GRP
        FROM 
            GRP g
        WHERE 
            GRP IN (
                'GRP1', 'GRP2', 'GRP3')
            AND g.KEYSK IN ('00004', '00008')
            AND e.ID = g.ID 
    ), 'N/A'),
    GRPABC = ISNULL((
        SELECT TOP 1
            GRP
        FROM 
            GRP g
        WHERE 
            GRP IN (
                'GRPA', 'GRPB', 'GRPC')
            AND g.KEYSK IN ('00004', '00008')
            AND e.ID = g.ID 
    ), 'N/A'),
    GRPROMAN = ISNULL((
        SELECT TOP 1
            GRP
        FROM 
            GRP g
        WHERE 
            GRP IN (
                'GRPI', 'GRPII', 'GRPIII', 'GRPIV', 'GRPV', 'GRPVI')
            AND g.KEYSK IN ('00004', '00008')
            AND e.ID = g.ID 
    ), 'N/A')
FROM
    EMP e
WHERE
    e.A = 'Y'
ORDER BY
    e.NAME