sql - 在 T-SQL 中的一列中聚合列（或添加新行）

Question

在我在 SQL Server (T-SQL) 中的查询中，我想要 2 列作为结果：eventsID和T_E_Discriminatorio_ID.

这是我的查询：

select 
    e.id eventsID,
    case 
       when DisabilityID is not null 
          then (select T_E_Discriminatorio_ID 
                from t_e_discriminatorio ted, Disability d 
                where ted.Dettaglio_T_E_Discriminatorio = d.Name
                  and DisabilityID = d.Id)
    end T_E_Discriminatorio_ID,
    case 
       when EthnicityId is not null 
          then (select T_E_Discriminatorio_ID 
                from t_e_discriminatorio ted, Ethnicity e 
                where ted.Dettaglio_T_E_Discriminatorio = e.Name
                  and EthnicityId = e.Id)
    end T_E_Discriminatorio_ID
from
    events e 
inner join 
    DiscriminatoryAct d on e.id = d.EventId;

结果是：

eventsID T_E_Discriminatorio_ID T_E_Discriminatorio_ID
62            8                        NULL
63            8                        7    
64            NULL                     7        
65            7                        6    

我希望聚合 T_E_Discriminatorio_ID 具有：

62 8
63 8
63 7
64 7
65 7
65 6

当有 2 个 T_E_Discriminatorio_ID 时，插入具有相同 events_id 的新行。

架构是这样的：

t_e_discriminatorio:
T_E_Discriminatorio_ID 
T_E_Discriminatorio 
Dettaglio_T_E_Discriminatorio <---> fk to Name on table (Disability or Ethnicity or Gender or Religion or SexualOrientation) table 

DiscriminatoryAct:
Id
CrimeId
EventId   <---> fk to id on events table
Note
OSCECategoryId
DiscriminatoryArea
DisabilityId  <---> fk to id on Disability table
EthnicityId <---> fk to id on Ethnicity table
GenderId <---> fk to id on Gender table
ReligionId <---> fk to id on Religion table
SexualOrientationId <---> fk to id on SexualOrientation table

Disability (like Ethnicity or Gender or Religion or SexualOrientation)
Id <--> fk to DisabilityId on DiscriminatoryAct
Name <--> fk to Dettaglio_T_E_Discriminatorio on t_e_discriminatorio

我怎样才能做到这一点？

感谢和问候

标签： sqlsql-serveraggregate

Answer 1

您可以使用将 2 个 case 语句组合在一起的 UNION。根据您向我们展示的架构，以下内容应为您提供所需的结果：

select * from (
select
    disc_a.event_ID as Event_ID,
    ev.T_E_Discriminatorio_ID as T_E_Discriminatorio_ID 
from DiscriminatoryAct disc_a
inner join Disability d on disc_a.DisabilityId = d.ID
inner join Events ev on ev.event_ID = disc_a.event_ID

UNION

select
    disc_a.event_ID as Event_ID,
    ev.T_E_Discriminatorio_ID as T_E_Discriminatorio_ID 
from DiscriminatoryAct disc_a
inner join Ethnicity e on disc_a.EthnicityId = e.Id
inner join Events ev on ev.event_ID = disc_a.event_ID
) a
order by a.Event_ID