我正在努力想办法做这样的事。我有一个类似这样的sql表。我想添加一个列,其中列出了一年中每个产品拥有的servicer id的总数。在名为“服务年数”的表格中:
+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+
| Product ID | Jan Servicer ID | Feb Servicer ID | Mar Servicer ID | Apr Servicer ID | May Servicer ID | Jun Servicer ID | Jul Servicer ID | Aug Servicer ID | Sep Servicer ID | Oct Servicer ID | Nov Servicer ID | Dec Servicer ID | Years Servicer Count |
+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+
| ID-001-2015 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-02 | SRVR-ID-02 | 2 |
+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+
| ID-002-2015 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | 1 |
+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+
| ID-002-2016 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-01 | SRVR-ID-02 | SRVR-ID-02 | SRVR-ID-02 | SRVR-ID-02 | SRVR-ID-02 | SRVR-ID-02 | SRVR-ID-03 | SRVR-ID-03 | 3 |
+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+
我试着在一列中计算不同值的数量。任何朝着正确方向的轻推都是惊人的。
1条答案
按热度按时间xriantvc1#
一个选项使用
union all
在子查询中,取消将列拆分为行,然后count(distinct)
:如果netezza的嵌套太多,那么另一种方法是连接: