分组查询重复的数据的第一条数据
有这么一个需求,需要获取供应商名称相同的第一条数据,如图:
SELECT sp_vendor_id, account_name
FROM finance_sp_vendor_hexh
FROM finance_sp_vendor_hexh
需要不重复的查询供应商列表,取出id值最小的供应商(问题的关键是要获取到供应商Id)。
使用下面的方法
SELECT sp_vendor_id, account_name,RANK () OVER (PARTITION BY account_name ORDER BY sp_vendor_id ASC) AS a
FROM finance_sp_vendor_hexh
FROM finance_sp_vendor_hexh
注意到rank()的用法。上面程序的意思为按照account_name分组并对account_name排序
结果如下
然后取出所有a列为1的行即可。完成代码
代码
SELECT x.sp_vendor_id, x.account_name
FROM (SELECT *,RANK () OVER (PARTITION BY account_name ORDER BY sp_vendor_id ASC) AS a
FROM finance_sp_vendor_hexh WHERE CP_Type=0)x WHERE a=1
FROM (SELECT *,RANK () OVER (PARTITION BY account_name ORDER BY sp_vendor_id ASC) AS a
FROM finance_sp_vendor_hexh WHERE CP_Type=0)x WHERE a=1