SQL if 语句有两个表

Question

给定以下表格：

表对象

id    Name   rating
1     Megan      9
2     Irina     10
3     Vanessa    7
4     Samantha   9
5     Roxanne    1
6     Sonia      8

交换表

id   swap_proposalid   counterpartyid
1                 4                2
2                 3                2

每个人都想要十个。我想为 Irina 列出一个可能的交换列表，其中 id 4 和 3 不会出现，因为命题已经存在。

输出1

id   Name    rating
1     Megan      9
5     Roxanne    1
6     Sonia      8

谢谢

Answer 1

这应该可以解决问题：

SELECT o.id, o.Name, o.rating 
FROM objects o
LEFT JOIN swap s on o.id = s.swap_proposalid
WHERE s.id IS NULL
AND o.Name != 'Irina'

Answer 2

这有效

SELECT mt2.ID, mt2.Name, mt2.Rating
FROM [MyTable] mt2  -- Other Candidates
   , [MyTable] mt1 -- Candidate / Subject (Irina)
WHERE mt2.ID NOT IN 
  (
    SELECT st.swap_proposalid
    FROM SwapTable st
    WHERE
      st.counterpartyid = mt1.ID
  )
AND mt1.ID <> mt2.ID -- Don't match Irina with Irina
AND mt1.Name = 'Irina' -- Find other swaps for Irina

——测试数据

CREATE TABLE MyTable
(
  ID INT, 
  Name VARCHAR(100),
  Rating INT
)
GO
CREATE TABLE SwapTable
(
  ID INT,
  swap_proposalid INT,
  counterpartyid INT
)
GO

INSERT INTO MyTable VALUES(1     ,'Megan',       9)
INSERT INTO MyTable VALUES(2     ,'Irina',      10)
INSERT INTO MyTable VALUES(3     ,'Vanessa',    7)
INSERT INTO MyTable VALUES(4     ,'Samantha',   9)
INSERT INTO MyTable VALUES(5     ,'Roxanne',    1)
INSERT INTO MyTable VALUES(6     ,'Sonia',      8)

INSERT INTO SwapTable(ID, swap_proposalid, counterpartyid)
VALUES (1, 4, 2)
INSERT INTO SwapTable(ID, swap_proposalid, counterpartyid)
VALUES (1, 3, 2)

Answer 3

猜测逻辑涉及识别除最高评级对象之外的对象，除了具有最高评级对象的命题，例如（使用示例 DDL 和 @nonnb 善意发布的数据）：

WITH ObjectHighestRated
     AS 
     (      
      SELECT ID
        FROM MyTable 
       WHERE Rating = (
                       SELECT MAX(T.Rating)
                         FROM MyTable T
                      )
     ),
     PropositionsForHighestRated
     AS
     (
      SELECT swap_proposalid AS ID
        FROM SwapTable
       WHERE counterpartyid IN (SELECT ID FROM ObjectHighestRated)
     ), 
     CandidateSwappersForHighestRated
     AS
     (
      SELECT ID
        FROM MyTable 
      EXCEPT 
      SELECT ID
        FROM ObjectHighestRated
      EXCEPT 
      SELECT ID
        FROM PropositionsForHighestRated                    
     )
SELECT * 
  FROM MyTable
 WHERE ID IN (SELECT ID FROM CandidateSwappersForHighestRated);