如何获取多列中每个不同值的计数并在单独的列中获取结果? [英] How to get the count of each distinct value in Multiple columns and get the result in separate columns?

问题描述

我需要查询下表才能得到表下方给出的结果.

I need the following table to be queried to get the result given below the table.

表格:

----------------------------------
| Name  |  Age   |  slot         |
|-------|--------|---------------|
|A      |20      | 1             |
|B      |30      | 2             |  
|C      |30      | 1             |                
|D      |20      | 1             |                
|E      |40      | 2             |                
|F      |40      | 3             |
|G      |50      | 3             |
----------------------------------

结果:

-------------------------------------------
|Age   |Age_Count     |Slot    |Slot_Count|
-------------------------------------------
|20    | 2            |1       |3         |
-------------------------------------------
|30    | 2            |2       |2         |
-------------------------------------------
|40    | 2            |3       |2         |
-------------------------------------------
|50    | 1            |
-----------------------

在搜索 stackoverflow 时，我发现 针对单列的这个问题 问题，并且有 [针对多列的链接] (获取多个"列中每个不同值的计数) 问题.第二个链接中的答案(对于多个 coulmn 的不同计数)显示在一个列下，我的要求是我想与那里发布的答案大不相同.

While searching stackoverflow i found this question for single column question and there is [this link for multiple columns] (get the count of each distinct value in "Multiple" columns) question. The answers from the second link (for the multiple coulmn's distinct count) is displayed under a single column and my requirement is i guess quite different from the answers posted there.

提前致谢

推荐答案

你的要求有点奇怪.你确定要那个吗?

Your request is kind of odd. Are you sure you want that?

如果是这样，这可能会有所帮助:

If so, this may help:

SET @x:=0,@y:=0,@m:=0,@n:=0;
SELECT 
    DISTINCT age,age_count, slot,slot_count 
FROM (
    SELECT 
        age, age_count, slot, slot_count
    FROM (
        SELECT 
            @x:=@x + 1 AS aid, age, COUNT(*) age_count
        FROM
            slots
        GROUP BY age
    ) a
    LEFT JOIN (
        SELECT 
            @y:=@y + 1 AS sid, slot, COUNT(*) slot_count
        FROM
           slots
        GROUP BY slot
    ) s ON a.aid = s.sid

    UNION

    SELECT 
        age, age_count, slot, slot_count
    FROM (
        SELECT 
            @m:=@m + 1 AS aid, slot, COUNT(*) slot_count
        FROM
           slots
        GROUP BY slot
    ) a 
    LEFT JOIN (
        SELECT 
            @n:=@n + 1 AS sid, age, COUNT(*) age_count
        FROM
            slots
        GROUP BY age
    ) s ON a.aid = s.sid
) a

如果你确定你有更多的独特年龄而不是独特的插槽，或者相反，你可以摆脱混乱的联盟.

If you know for sure that you have more unique ages than unique slots , or opposite, you can get ride of messy union.

这篇关于如何获取多列中每个不同值的计数并在单独的列中获取结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！