你似乎想要:
不同的值group_name_code
和一个顺序的id
按每组的最小 id 排序group_name_code
.
Netezza 拥有DISTINCT
关键词,但不是DISTINCT ON ()(Postgres 功能) https://stackoverflow.com/a/7630564/939860:
https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_select.html https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_select.html
你可以:
SELECT DISTINCT group_name_code FROM group_table;
没有括号,则DISTINCT
关键字不需要括号。
但你不会得到顺序的id
你用这个来展示。
有“分析函数”,又称窗口函数:
https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/c_dbuser_overview_analytic_funcs.html https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/c_dbuser_overview_analytic_funcs.html
而且还有row_number()
:
https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_functions.html https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_functions.html
所以这应该有效:
SELECT row_number() OVER (ORDER BY min(id)) AS new_id, group_name_code
FROM group_table
GROUP BY group_name_code
ORDER BY min(id);
或者,如果 Netezza 不允许嵌套聚合函数和窗口函数,则使用子查询:
SELECT row_number() OVER (ORDER BY id) AS new_id, group_name_code
FROM (
SELECT min(id) AS id, group_name_code
FROM group_table
GROUP BY group_name_code
) sub
ORDER BY id;