从groupbycase语句中获取外键项的计数

nhhxz33t 于 2021-06-20 发布在 Mysql

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

尝试学习mysql连接，并提供一个基本的示例。我想弄清楚如何连接两个表，并按年龄组对销售额进行分组。

People table:
id            age
1             11
2             23
3             17
4             14
5             24
6             19
7             21

Other table:
id          
1           
2           
3            
4            
5           

Middle table
id          o_id         p_id
1           3            1
2           1            5
3           5            3
4           4            7
5           2            6
6           5            2
7           3            4
8           1            7
9           3            5
10          2            3

age     Total Other for each age group
10-20         5
20-30         5

sql mysql foreign-keys

来源：https://stackoverflow.com/questions/51644943/get-count-of-foreign-key-items-from-a-group-by-case-statement

2条答案

按热度按时间

tzxcd3kk1#

SELECT case
        when Customer.age between 11 and 20 then '11-20'
        when Customer.age between 21 and 30 then '21-30'
        end as age,
         count(*)
 FROM Sale, Customer
 WHERE Sale.c_id = Customer.id
 GROUP BY case
          when Customer.age between 11 and 20 then '11-20'
          when Customer.age between 21 and 30 then '21-30'
          end
;

以上工作。

赞(0）回复(0）举报 2021-06-20

kokeuurv2#

select count(*),
    case
        when age between 11 and 20 then '11-20'
        when age between 21 and 30 then '21-30'
    end as aggage
from Customer inner join Sale
    on Customer.id = Sale.c_id
group by aggage

你应该可以加入sale表来计算销售额。它在这里工作。

赞(0）回复(0）举报 2021-06-20

我来回答

从groupbycase语句中获取外键项的计数

2条答案

相关问题

热门标签

最新问答