sql - 使用 SQL/SQLAlchemy 过滤选定的列

Question

我有一个表格，其中包含案件中代表当事人的律师事务所的数据:

  case_id   | law_firm_id | party_type 
------------+-------------+------------
 2001300896 |         918 | Plaintiff
 2001300896 |        1927 | Plaintiff
 2001300896 |        1934 | Plaintiff
 2001300896 |       91653 | Defendant
 2001300896 |      245649 | Plaintiff
 2001300896 |     1534016 | Defendant
 2001311137 |         918 | Defendant
 2001311137 |       50823 | Plaintiff
 2001311137 |      257164 | Defendant
 2001311137 |     8055087 | Defendant

我想要 1 家律师事务所，比如说 ID 为 918 的律师事务所，作为 anchor ，并找出在这些案件中哪些律师事务所与 anchor 律师事务所处于同一方/相反方。换句话说，我想出去:

  case_id   | law_firm_id | party_type | anchored_party_type
------------+-------------+------------+--------------------
 2001300896 |         918 | Plaintiff  | Plaintiff
 2001300896 |        1927 | Plaintiff  | Plaintiff
 2001300896 |        1934 | Plaintiff  | Plaintiff
 2001300896 |       91653 | Defendant  | Plaintiff
 2001300896 |      245649 | Plaintiff  | Plaintiff
 2001300896 |     1534016 | Defendant  | Plaintiff
 2001311137 |         918 | Defendant  | Defendant
 2001311137 |       50823 | Plaintiff  | Defendant
 2001311137 |      257164 | Defendant  | Defendant
 2001311137 |     8055087 | Defendant  | Defendant

如何使用 SQL 或 SQLAlchemy 执行此操作？是否可以选择一个列，其中的值基于特定行的该列的值？例如。对于 case_id 2001300896，id 为 918 的律师事务所代表原告，因此对于具有该 case_id 的所有行，锚定方类型都是 Plaintiff。

Answer 1

这可以使用子查询来完成，以获取律师事务所的所有案例，然后像这样使用 case_id 重新加入:

WITH specific_law_firm_cases AS (
  SELECT 
    l.case_id,
    l.party_type
  FROM law_firm_cases l
  WHERE l.law_firm_id = 918
)
SELECT 
  specific_law_firm_cases.case_id,
  l.law_firm_id,
  l.party_type,
  specific_law_firm_cases.party_type AS anchored_party_type
FROM law_firm_cases l
INNER JOIN specific_law_firm_cases USING (case_id)

在这里测试:http://sqlfiddle.com/#!17/5672a6/3/0