结合像行基于一列的子字符串?

Question

我有一个零件表，看起来像这样:

Thing410105322101053231010532484625111846251188462518556897101

使用COUNT()窗口函数:

WITH cte AS (
SELECT *,
COUNT(*) OVER (PARTITION BY Part, LEFT(PartNum, 6), Thing1, Thing2, Thing3, Thing4) counter1,
COUNT(*) OVER (PARTITION BY Part, LEFT(PartNum, 6)) counter2
FROM tablename
)
SELECT DISTINCT
Part,
CASE WHEN counter1 > 1 AND counter1 = counter2 THEN LEFT(PartNum, 6) ELSE PartNum END PartNum,
Thing1, Thing2, Thing3, Thing4 
FROM cte;

您可以使用窗口函数来确定应该组合的内容。我想我可以把它们合并成一个比较:

select (case when min_thingee = max_thingee and cnt > 1
then left(partnum, 6) else partnum
end) as partnum,
min(thing1) as thing1, min(thing2) as thing2,
min(thing3) as thing3, min(thing4) as thing4
from (select t.*,
min(concat(thing1, '|', thing2, '|', thing3, '|', thing4) over (partition by left(partnum, 6)) as min_thingee,
max(concat(thing1, '|', thing2, '|', thing3, '|', thing4) over (partition by left(partnum, 6)) as max_thingee,
count(*) over (partition by left(partnum, 6)) as cnt
from t
) t
group by (case when min_thingee = max_thingee and cnt > 1
then left(partnum, 6) else partnum
end);

如果您真的想使用dense_rank，这里有一种方法。

基础统计学告诉我们一组相等数字的标准差等于0。这意味着一旦我们有了每个left(partnum,6)的秩，我们就可以强制执行这样的条件，即我们只折叠那些只有一个唯一秩且至少有两行的行组(stdev对单个值的结果是null,<> 0)。注意partition by子句，看看排名是如何计算的

with cte as
(select *, dense_rank() over (order by part, left(partnum,6), thing1, thing2, thing3, thing4) as rnk
from my_table)
select distinct 
part,
case when stdev(rnk) over (partition by part, left(partnum,6)) = 0 then left(partnum,6) else partnum end as partnum,
thing1,
thing2,
thing3,
thing4
from cte;