分组值的区分id

Question

我想计算每个麻木中不同的id，并将它们存储在一个列中:试:

WITH T AS(
SELECT 
MAX(CASE WHEN LOGS like'CAR%' then REPLACE(LOGS,'CAR-','')end)as CAR,
MAX(CASE WHEN LOGS like 'MOT%' then REPLACE(LOGS,'MOT-','')end)as MOTO,
MAX(CASE WHEN LOGS like 'BICYCLE%' then REPLACE(LOGS,'BICYCLE-','')end)as BICYCLE,
MAX(CASE WHEN LOGS like 'SHIP%' then REPLACE(LOGS,'SHIP-','')end)as SHIP,
ID,
ORIG,
DATE_ID ,
NUMB,
STEPS 
from dbo.test
group by ORIG,DATE_ID,ID ,NUMB,STEPS
)
SELECT ID,ORIG,NUMB,STEPS,DATE_ID,CAR,MOTO,BICYCLE,SHIP,
(SELECT COUNT(DISTINCT ID) FROM dbo.test tp WHERE ORIG= '4567') as COUNTER
from t
where ORIG= '4567'
and NUMB in('1515','1921','2121')
GROUP BY ID,ORIGIN_URI,NUMB,STEPS,DATE_ID,CAR,MOTO,BICYCLE,SHIP

接收输出:

+----+--------------+--------+-------+----------+-------+------+---------+------+---------+
| ID |     ORIG     | NUMB | STEPS | DATE_ID  |  CAR  | MOTO | BICYCLE | SHIP | COUNTER |
+----+--------------+--------+-------+----------+-------+------+---------+------+---------+
|  1 |   4567       |   1515 |     1 | 20201010 | HONDA | NULL | NULL    | NULL |       3 |
|  1 |   4567       |   1515 |     2 | 20201010 | HONDA | NULL | NULL    | NULL |       3 |
|  1 |   4567       |   1515 |     3 | 20201010 | HONDA | NULL | NULL    | NULL |       3 |
|  2 |   4567       |   1921 |     1 | 20201111 | NULL  | KTM  | NULL    | NULL |       3 |
|  3 |   4567       |   2121 |     1 | 20201231 | NULL  | NULL | NULL    | BOAT |       3 |
|  3 |   4567       |   2121 |     2 | 20201231 | NULL  | NULL | NULL    | BOAT |       3 |
|  3 |   4567       |   2121 |     3 | 20201231 | NULL  | NULL | NULL    | BOAT |       3 |
|  3 |   4567       |   2121 |     4 | 20201231 | NULL  | NULL | NULL    | BOAT |       3 |
+----+--------------+--------+-------+----------+-------+------+---------+------+---------+

可以看到COUNTER列有不同id的计数但对于所有NUMB

我想输出这个:

+----+--------------+--------+-------+----------+-------+------+---------+------+---------+
| ID |     ORIG     | NUMB | STEPS | DATE_ID  |  CAR  | MOTO | BICYCLE | SHIP | COUNTER |
+----+--------------+--------+-------+----------+-------+------+---------+------+---------+
|  1 |   4567       |   1515 |     1 | 20201010 | HONDA | NULL | NULL    | NULL |       2 |
|  1 |   4567       |   1515 |     2 | 20201010 | HONDA | NULL | NULL    | NULL |       2 |
|  2 |   4567       |   1515 |     1 | 20201010 | HONDA | NULL | NULL    | NULL |       2 |
|  2 |   4567       |   1921 |     1 | 20201111 | NULL  | KTM  | NULL    | NULL |       1 |
|  3 |   4567       |   2121 |     1 | 20201231 | NULL  | NULL | NULL    | BOAT |       2 |
|  3 |   4567       |   2121 |     2 | 20201231 | NULL  | NULL | NULL    | BOAT |       2 |
|  3 |   4567       |   2121 |     3 | 20201231 | NULL  | NULL | NULL    | BOAT |       2 |
|  1 |   4567       |   2121 |     1 | 20201231 | NULL  | NULL | NULL    | BOAT |       2 |
+----+--------------+--------+-------+----------+-------+------+---------+------+---------+

1. 1515有2个id
2. 1921 has 1 id
3. 2121有2个id

我也尝试在(SELECT COUNT(DISTINCT ID) FROM dbo.test tp WHERE ORIG= '4567')内放置GROUP BYNUMB，但没有工作。

Answer 1

你似乎想要的是:

count(distinct steps) over (partition by orig, numb)

唉，SQL Server不支持count(distinct)与窗口函数。

令人高兴的是，有一个简单的解决方法(这回避了为什么不支持上述语法的问题):

(dense_rank() over (partition by orig, numb order by steps asc) +
dense_rank() over (partition by orig, numb order by steps desc) - 1
) as counter