sql - 如何在 SQL Server 中获取具有不同 P1 等级的多个团队的人员

Question

TABLE 1                                   Table 2(Team Ratings)

ID   Teamid       Team                  Teamid      Ratings
--------------------------              -------------------
1    TeamA        null                  TeamA       R1
2    Steve        TeamA                 TeamB       P1
3    Jacob        TeamA                 TeamC       Q1
4    Dierken      TeamA
5    TeamB        null
6    Julie        TeamB
7    Jacob        TeamB
8    Raki         null
9    Balmer       TeamC

任何人都可以帮助解决这个问题。我想从 Table1 中获取所有行，其人员条件是“团队 A 中具有 R1 评级的人员和不同团队中具有 P1 评级或任何其他评级的同一个人。

输出应该像

id    TEamid      Team      Ratings
------------------------------------
1     Jacob       TeamA        R1
2     Jacob       TeamB        P1

标签： sqlsql-server

Answer 1

这将是另一种选择：

CREATE TABLE Teams
(
    ID  INT IDENTITY PRIMARY KEY
    ,Teamid VARCHAR(50)
    ,Team   varchar(50)
)
GO

INSERT INTO TeamsRaitings
VALUES('TeamA','R1')
      ,('TeamB','P1')

INSERT INTO teams
VALUES('TeamA',nULL)
,('Steve','TeamA')
,('Jacob','TeamA')
,('Dierken','TeamC')
,('Steve','TeamA')
,('TeamB',nULL)
,('Julie','TeamB')
,('Jacob','TeamB')
,('Raki',NULL)
,('Balmer','TeamC')


;WITH TeamA AS (
SELECT a.ID
      ,a.Teamid
      ,a.Team
      ,  raiting = CASE WHEN TEAM IS NULL THEN NULL ELSE  'R1' END 
 FROM teams a
  WHERE A.Team = 'TeamA' OR A.Team IS NULL
 ),
 AnotherTEam AS (
SELECT  a.ID
      ,a.Teamid
      ,a.Team
      ,raiting = 'P1'
 FROM teams a
  WHERE A.Team <> 'TeamA'
   AND EXISTS (SELECT top 1 1 FROM TeamA where A.Teamid= TeamA.Teamid)
 )

 SELECT ID
        ,Teamid
        ,Team
        ,raiting

  FROM TeamA
  UNION all
  SELECT ID
        ,Teamid
        ,Team
        ,raiting
  FROM ANOTHERTEAM