选择B列中第一个出现的值以对应于A列中的值SQL

Question

我有点拘泥于SQL。我有两张表(A(原因&(B( 原因类别。原因是一个充满非营利组织的表格，原因类别包含它们相应的"原因"；原因类别"；。我遇到的问题是每个原因有多个原因类别，但是，我只想检索第一个列出的原因类别，这样就只有一个原因行和一个类别。

当前状态

**Cause**
Zidisha
Feeding America
NAACP

这就是我离开时得到的加入两张表

**Cause**             **Category** 
Zadisha           Economic Empowerment
Feeding America   Financial Support
Feeding America   Goods/ Service
NAACP             Human Rights
NAACP             Education

理想状态

**Cause**             **Category** 
Zadisha           Economic Empowerment
Feeding America   Financial Support
NAACP             Human Rights

我已经尝试过使用first_value和partion来隔离每个原因的单个类别。

SELECT c.id AS cid
, c.name AS cause
, cc.name as cause_cat
, first_value(cc.name) over (partition by cid order by cause_cat nulls last) as primary_cause_category
FROM causes c
LEFT JOIN cause_category_rel rel
ON foreign_id = rel.cause_id
LEFT JOIN cause_categories cc
ON rel.cause_category_id = cc.id
WHERE cause_cat is not null
GROUP BY cid, cause, cause_cat

结果是：

**Cause**             **Category**         **Primary Category**
Zadisha           Economic Empowerment.     Economic Empowerment
Feeding America   Financial Support         Financial Support
Feeding America   Goods/ Service            Financial Support
NAACP             Human Rights              Human Rights
NAACP             Education                 Human Rights

有人对如何得到这个结果有什么建议吗？我也可以回答任何澄清的问题。感谢

Answer 1

我不清楚您的模式在上面的示例数据之外是什么，即您是否有任何表ID或其他元素。然而，严格根据您的样本数据，您可以使用窗口函数并按照类别名称的字母顺序来获得第一条记录(如下所示(。如果你不想使用类别名称，如果你只是想获得任何随机的单个类别来压平数据，你可以将其切换到ORDER BY NEWID()来动态生成随机ID。

drop table if exists #cause
drop table if exists #causeCategory
create table #cause (causeName varchar(20))
create table #causeCategory (causeName varchar(20), category varchar(50))
insert into #cause
values
('Zidisha'),
('Feeding America'),
('NAACP')
insert into #causeCategory
values
('Zidisha',           'Economic Empowerment'),
('Feeding America',   'Financial Support'),
('Feeding America',   'Goods/ Service'),
('NAACP' ,            'Human Rights'),
('NAACP',             'Education')
;with cte
as (
SELECT 
c.causeName,
cc.category,
row_number() over (partition by c.causeName order by cc.category) as rn
FROM #cause c
LEFT JOIN #causeCategory cc
ON c.causeName = cc.causeName
GROUP BY c.causeName, cc.category
)
select causeName, category
from cte
where rn = 1

Answer 2

试试这个。

SELECT  c.id AS cid
,c.name AS cause
,(
SELECT TOP 1
cc.name
FROM   cause_category_rel AS rel
LEFT OUTER JOIN cause_categories AS cc ON rel.cause_category_id = cc.id
WHERE  c.foreign_id = rel.cause_id
AND cause_cat IS NOT NULL
ORDER BY
cause_cat
) AS primary_cause_category
FROM    causes AS c;