sql，oracle，数据库

Question

我有两张桌子，我已经和他们在一起了。现在，我需要从连接的表2栏中选择一个列，如果案件中有被告和原告，那么它应该显示OK，如果只有被告那么def，原告然后pltf，如果没有，那么没有。

我基本上有这样的桌子：

Personid#    Case#       Role        CaseType
----------------------------------------------
cg902        CB190       Plaintiff   Civil
cg903        CB190       Defendant   Civil
cg904        CB191       Plaintiff   Civil
cg905        CB192       Defendant   Civil
cg906        CB193        none       Civil

我需要这个：

Case#       ANYCOLNAME    CaseType
----------------------------------
CB190       OK            Civil
cg904       PLTF          Civil
cg905       Def           Civil
cg906       None          Civil

我会非常感激的。

Answer 1

用表名替换table_name

此查询将解决您的需求：

select distinct y.case#,
case 
when x.role='Plaintiff' and y.cnt=1 then 'PLTF'
when x.role='Defendant' and y.cnt=1 then 'Def'
when x.role='none' and y.cnt=1 then 'NONE'
when y.cnt=2 then
  case
  when exists(select 1 from TABLE_NAME where role='Plaintiff' and CASE#=y.case#) and
  exists(select 1 from TABLE_NAME where role='Defendant' and CASE#=y.case#)
  then 'OK'
  end
end 
from 
(select case#,
count(case#) as cnt
from TABLE_NAME
group by case#
order by case#) y, TABLE_NAME x
where x.case#=y.case#
order by y.case#
;

Answer 2

类似这样的内容(包括固定情况和personID问题)：

SQL> with test (ccase, crole) as
  2    (select 'cb190', 'plaintiff' from dual union
  3     select 'cb190', 'defendant' from dual union
  4     select 'cb191', 'plaintiff' from dual union
  5     select 'cb192', 'defendant' from dual union
  6     select 'cb193', 'none'      from dual
  7    ),
  8  inter as
  9    (select ccase, min(crole) minrole, max(crole) maxrole
 10     from test
 11     group by ccase
 12    )
 13  select ccase,
 14    case when minrole <> maxrole then 'ok'
 15         when minrole =  maxrole and minrole = 'plaintiff' then 'pltf'
 16         when minrole =  maxrole and minrole = 'defendant' then 'def'
 17         else 'none'
 18    end anycolname
 19  from inter
 20  order by ccase;

CCASE ANYC
----- ----
cb190 ok
cb191 pltf
cb192 def
cb193 none

SQL>