选择列值在多个列上分组时多次出现的行

rta7y2nd 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(298)

我想分成两列， ClientID, TypeID ，并选择其中的所有行 ClientID 值在表中有多个引用。所以不只是这些值的列表，而是所有的行。
例如，这是我的内部子查询结果：

╔══════════╦════════╦══════════╗
║ ClientID ║ TypeID ║ Count(*) ║
╠══════════╬════════╬══════════╣
║ "0"      ║ "1"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "455"    ║ "1"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "455"    ║ "2"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "455"    ║ "8"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40036"  ║ "8"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40070"  ║ "7"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40070"  ║ "8"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40082"  ║ "2"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40082"  ║ "12"   ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40085"  ║ "1"    ║ "1"      ║
╚══════════╩════════╩══════════╝

我想要这个输出：

╔══════════╦════════╦══════════╗
║ ClientID ║ TypeID ║ Count(*) ║
╠══════════╬════════╬══════════╣
║ "455"    ║ "1"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "455"    ║ "2"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "455"    ║ "8"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40070"  ║ "7"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40070"  ║ "8"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40082"  ║ "2"    ║ "1"      ║
╠══════════╬════════╬══════════╣
║ "40082"  ║ "12"   ║ "1"      ║
╚══════════╩════════╩══════════╝

正如您所看到的一样，只有clientid值不出现多次的行消失了。
这是我的mysql查询，但是这自然会给我一个空结果，因为所有的行都是唯一的，所以 Count(*) 列永远不会超过1，这与按单个字段而不是两个字段分组的情况不同。

SELECT *, COUNT(*) AS C
FROM (
    SELECT ClientID, TypeID
    FROM table
    GROUP BY ClientID, TypeID
) AS R
GROUP BY ClientID, TypeID
HAVING COUNT(C) > 1
ORDER BY ClientID, TypeID

我应该如何计算两个列都被聚合的组，所以 Count(*) 字段将实际保存正确的发生次数？

sql mysql group-by select having-clause

来源：https://stackoverflow.com/questions/62220538/select-rows-where-column-value-appears-multiple-times-while-grouped-on-multiple

2条答案

按热度按时间

6uxekuva1#

你可以用 exists 以及 distinct :

select distinct clientID, typeID
from mytable t
where exists (
    select 1 from mytable t1 where t1.clientID = t.clientID and t1.typeID <> t.typeID
)

或者如果您也需要计数，请使用聚合而不是 distinct :

select clientID, typeID, count(*) cnt
from mytable t
where exists (
    select 1 from mytable t1 where t1.clientID = t.clientID and t1.typeID <> t.typeID
)
group by clientID, typeID

对于性能，请考虑 (clientID, typeID) .

赞(0）回复(0）举报 2021-07-26

ax6ht2ek2#

select t.*,
(select count(*)from table i where i.ClientID=t.ClientID and i.TypeID=t.TypeID) as Count 
from table t where clientID in (
select ClientID from table
GROUP BY ClientID
HAVING COUNT(ClientID) > 1)

赞(0）回复(0）举报 2021-07-26

我来回答

选择列值在多个列上分组时多次出现的行

2条答案

相关问题

热门标签

最新问答