mysql—如何在sql中对三个或更多表进行分组

gmxoilav 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(251)

我有第一个表叫做header

id | user_id | created_on
------------------------
 15 |  42     | 1 day ago
 16 |  43     | 1 day ago
 17 |  44     | 1 day ago
 18 |  45     | 2 day ago

第二个表名为line

line_id | ph_name | quantity
----------------------------
 15     | nokia   | 3
 16     | sumsung | 5
 17     | nokia   | 1

第三个表调用了user

id | name
--------------
 42 |  hi
 43 |  bye
 44 |  tata

那么如何将此关联和分组，根据用户数并考虑sql查询或django聚合orm中的今天日期，给出如下所示

{
 {
   "ph_name":"nokia",
   "num_of_user":"2", //hi and tata are the users
   "quantity":"4"
 },
 {
   "ph_name":"samsung",
   "num_of_user":"1", //bye is the only user
   "quantity":"5"
 }
}

sql mysql

来源：https://stackoverflow.com/questions/50851534/how-to-do-group-by-between-three-or-more-tables-in-sql

1条答案

按热度按时间

lb3vh1jj1#

试试这个：

select ph_name, count(distinct first_table.user_id), sum(quantity)
from second_table 
inner join (select * from first_table where created_on = '1 day ago') as first_table on second_table.line_id = first_table.id
inner join third_table on third_table.id = first_table.user_id
group by ph_name

注意，我不知道您的表是否在同一个数据库/模式中。您可能必须通过在表名前加前缀来指定数据库/架构 schema/database_name.table_name .

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

我来回答

mysql—如何在sql中对三个或更多表进行分组

1条答案

相关问题

热门标签

最新问答