有没有可能在mysql中获得一个组的子计数

ee7vknir 于 2021-06-25 发布在 Mysql

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

不确定这在mysql中是否可行。
我有一个查询，它生成下面的结果，试图找出如何获得每个州的小计和总计。

SELECT COUNT(tr.teamMemberID) AS 'Team Count',  
       tr.teamUserID,  
       tu.schoolState,  
       tu.schoolCity AS 'TeamTitle'  
 FROM Tournament AS tr JOIN TeamUser AS tu ON tu.id = tr.teamUserID  
WHERE tr.seasonID > 62  
GROUP BY tr.teamUserID  
ORDER BY tu.schoolState, tu.schoolCity

查询结果

+------------+-------------+----------------------------------------+  
 | Team Count | State       |               TeamTitle                |  
 +------------+-------------+----------------------------------------+  
 |          5 | CA          | Calvary Chapel Christian School Downey |  
 |          9 | CA          | Calvary Chapel High School             |  
 |          3 | CA          | Delta Charter High School              |  
 |          8 | CA          | Immanuel High School                   |  
 |          4 | CO          | Caliche High School                    |  
 |         10 | CO          | Del Norte High School                  |  
 |         14 | CO          | Faith Christian High School            |  
 |          8 | IL          | Altamont High School                   |  
 |         30 | IL          | Brimfield High School                  |  
 +------------+-------------+----------------------------------------+

所以我要找的结果在每个州之后都会有一个小计。
谢谢

mysql

来源：https://stackoverflow.com/questions/50276280/is-it-possible-to-get-a-sub-count-on-a-group-in-mysql

2条答案

按热度按时间

o8x7eapl1#

尝试使用with rollup获取小计和总计。您可能需要更改分组方式中列的顺序。这就决定了到目前为止的顺序。
看看这个：按修改器分组

SELECT COUNT(tr.teamMemberID) AS 'Team Count',  
       tr.teamUserID,  
       tu.schoolState,  
       tu.schoolCity AS 'TeamTitle'  
 FROM Tournament AS tr JOIN TeamUser AS tu ON tu.id = tr.teamUserID  
WHERE tr.seasonID > 62  
GROUP BY tr.teamUserID, tu.schoolState, tu.schoolCity WITH ROLLUP

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

vtwuwzda2#

要进行状态计数，可以使用 UNION :

(SELECT COUNT(tr.teamMemberID) AS 'Team Count',  
       tu.schoolState,  
       tu.schoolCity AS 'TeamTitle'  
  FROM Tournament AS tr JOIN TeamUser AS tu ON tu.id = tr.teamUserID  
 WHERE tr.seasonID > 62  
 GROUP BY tr.teamUserID  
 ORDER BY tu.schoolState, tu.schoolCity)
UNION
(SELECT COUNT(tu.schoolState), 
        tu.schoolState, 
        null 
  FROM Tournament tr join TeamUser tu ON tu.id=tr.teamUserID
 WHERE tr.seasonID > 62  
 GROUP BY tu.schoolState) 
ORDER BY schoolState, TeamTitle desc;

最后一个“order by”将状态汇总放在单个计数之后，但在下一个状态之前。注意，我没有在查询中包含“teamuserid”，因为它没有出现在示例结果中。

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

我来回答

有没有可能在mysql中获得一个组的子计数

2条答案

相关问题

热门标签

最新问答