在不使用GROUP BY的情况下标记重复的值MySQL [英] Mark duplicated values MySQL without using GROUP BY

问题描述

您能否在不将重复值分组的情况下帮助在其他列中标记重复值?

Can you please help to mark duplicated values in an additional column without grouping duplicated values?

查看我的示例数据(右侧示例示例我所拥有的以及我需要实现的目标):

See my example data (Example what I have and What I need to achieve on the right):

您会看到我的产品ID后缀为 E (电源)和 G (气体).某些产品ID重复:相同的产品ID-一个具有 E 的产品ID，另一个具有 G 的产品ID构成了双重产品.

As you can see I have Product ID with suffix E (Power) and G (Gas). Some Product IDs are duplicated: the same Product ID - one with E and the second one with G makes Dual Product.

仅具有 E 的产品ID使 Power_Only_product ，仅具有 G 的产品ID使 Gas_Only_product 成为同一产品带有 E 和 G 的ID构成双重产品.

Product ID only with E makes Power_Only_product, Product ID only with G makes Gas_Only_product, the same Product ID with E and G makes Dual Product.

棘手的事情是在正确的站点上添加一列，其中包含ID为Dual Product且ID为Power Only或Gas Only的信息.

The tricky thing is to add a column on the right site with information which ID is Dual Product and which one is Power Only or Gas Only.

您能在不对产品ID进行分组的情况下帮助我获得此列吗?先感谢您！帕维尔

Can you help me to get such column without grouping the Product IDs? Thank you in advance! Pawel

推荐答案

SELECT t1.*,
       CASE WHEN t2.CustomerAccount IS NOT NULL
            THEN 'Dual Product'
            WHEN t1.Product = 'Gas'
            THEN 'Gas Only'
            WHEN t1.Product = 'Power'
            THEN 'Power Only'
            ELSE 'Wrong product type'
            END ProductType
FROM sourcetable t1
LEFT JOIN sourcetable t2 ON t1.CustomerAccount = t2.CustomerAccount
                        AND t1.Product != t2.Product    

该查询不使用 ProductID ，也不要检查其是否匹配 CustomerAccount 和/或 Product .这将是多余的工作而没有任何利润.

The query do NOT use ProductID and do NOT check that it matches CustomerAccount and/or Product. It will be excess work without any profit.

这篇关于在不使用GROUP BY的情况下标记重复的值MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！