按ID选择最小序号
按ID选择最小序号
提问于 2019-06-11 02:17:44
只是尝试编写一个简单的查询来获取具有最小CNTCT_SEQ_NUM的行(每个CONTACT_NAME )，其中CONTACT_NAME不为空。
下面是我写的内容：
SELECT VENDOR_ID, MIN(CNTCT_SEQ_NUM) AS CNTCT_SEQ_NUM , CONTACT_NAME
FROM PS_VENDOR_CNTCT
WHERE VENDOR_ID IN ('ERSUT', 'MOOREA')
AND CONTACT_NAME <> ''
GROUP BY CONTACT_NAME, VENDOR_ID当前结果：
VENDOR_ID CNTCT_SEQ_NUM CONTACT_NAME
ERSUT 19 V Smith
ERSUT 4 T Peterman
ERSUT 2 I GANCE
ERSUT 8 R FISHER
MOOREA 2 S DALY
MOOREA 4 B SLAUTEN
MOOREA 1 N BLAKELY预期结果为：
VENDOR_ID CNTCT_SEQ_NUM CONTACT_NAME
ERSUT 2 I GANCE
MOOREA 1 N BLAKELY回答 3
Stack Overflow用户
回答已采纳
发布于 2019-06-11 02:21:05
试试这个-
SELECT A.* FROM PS_VENDOR_CNTCT A
INNER JOIN
(
SELECT VENDOR_ID,MIN(CNTCT_SEQ_NUM) CNTCT_SEQ_NUM
FROM PS_VENDOR_CNTCT
GROUP BY VENDOR_ID
)B ON A.VENDOR_ID = B.VENDOR_ID
AND A.CNTCT_SEQ_NUM = B.CNTCT_SEQ_NUMStack Overflow用户
发布于 2019-06-11 02:20:45
相关子查询解决了这个问题：
select vc.*
from PS_VENDOR_CNTCT vc
where vc.CNTCT_SEQ_NUM = (select min(vc2.CNTCT_SEQ_NUM)
from PS_VENDOR_CNTCT vc2
where vc2.VENDOR_ID = vc.VENDOR_ID and
vc2.CONTACT_NAME <> ''
);为了提高性能，您可以在(VENDOR_ID, CONTACT_NAME, CNTCT_SEQ_NUM)上尝试索引。这涵盖了子查询，尽管仍然需要扫描所有索引记录。
Stack Overflow用户
发布于 2019-06-11 02:40:57
你似乎不需要MIN()，但窗口分析功能，如ROW_NUMBER()
SELECT DISTINCT Q.VENDOR_ID, Q.CONTACT_NAME, Q.CNTCT_SEQ_NUM
FROM
(
SELECT P.*,
ROW_NUMBER() OVER
(PARTITION BY VENDOR_ID ORDER BY CNTCT_SEQ_NUM) AS RN
FROM PS_VENDOR_CNTCT P
WHERE VENDOR_ID IN ('ERSUT', 'MOOREA')
AND CONTACT_NAME <> ''
) Q
WHERE Q.RN = 1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56531561
复制相关文章
相似问题