x =时sql情况?那y =?结尾

Question

我对sql情况有一个问题，我想检查列值，如果返回true，我希望另一个列值更改，但是在'case'中，您可以仅检查一列并仅更改其值，我该如何使其工作？

select f.[film name],f.city,p.[participant name],
case rt.[type name]
when 'director' then 'director' else null
end 'role type'
from Films f 
join FilmParticipants fp on fp.filmID=f.filmID 
join Participants p on p.participantID=fp.participantID 
join RoleType rt on rt.roleID=fp.roleID
where f.city in(
select f.city
from Films f
group by f.city
having COUNT(*)>1)
order by f.city

它给出了此表：

film name   | city       | participate name | role type
Shrek       | London     | John             | null
Shrek       | London     | John             | null
Dragon      | London     | Rick             | null
Drago       | London     | Morty            | Director

现在，我希望每当"角色类型列"中的null时，"参与名称"列也将为null。

Answer 1

您可以在角色类型的同一方式时使用案例，并显示partecipant.name

      select f.[film name],f.city,
       case rt.rt.[type name]
           when 'director' then p.[participant name] else null end 'partecipant',
      case rt.[type name]
           when 'director' then 'director' else null
      end 'role type'
      from Films f 
      join FilmParticipants fp on fp.filmID=f.filmID 
      join Participants p on p.participantID=fp.participantID 
      join RoleType rt on rt.roleID=fp.roleID
      where f.city in(
        select f.city
        from Films f
        group by f.city
        having COUNT(*)>1)
      order by f.city

以删除null

      select [film name], city,  max(partecipant), max([role type])
      from (
      select f.[film name],f.city,
       case rt.rt.[type name]
           when 'director' then p.[participant name] else null end 'partecipant',
      case rt.[type name]
           when 'director' then 'director' else null
      end 'role type'
      from Films f 
      join FilmParticipants fp on fp.filmID=f.filmID 
      join Participants p on p.participantID=fp.participantID 
      join RoleType rt on rt.roleID=fp.roleID
      where f.city in(
        select f.city
        from Films f
        group by f.city
        having COUNT(*)>1)
      order by f.city ) t 
      group by [film name], city