只是尝试编写一个简单的查询来获取具有最小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
发布于 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_NUM
发布于 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)
上尝试索引。这涵盖了子查询,尽管仍然需要扫描所有索引记录。
发布于 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
https://stackoverflow.com/questions/56531561
复制相似问题