sql脚本按所需帮助分组

h9a6wy2h 于 2022-10-22 发布在 Mysql

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

我有一个sql脚本，需要进一步调整以生成一个更简单的报告，如下所示。我正在寻找按用户名分组的结果。
用户名、周数和获胜次数

select 
  p.userID,
  s.gameID,
  p.pickID,
  u.userName,
  s.weekNum,
  s.homeID, 
  s.homeScore, 
  s.visitorID, 
  s.visitorScore
from nflp_picks p 
inner join nflp_users u
  on p.userID = u.userID 
inner join nflp_schedule s 
  on p.gameID = s.gameID 
where s.weekNum = 6 
  and u.userName <> 'admin' 
order by p.userID, s.gameTimeEastern, s.gameID;

小时

mysql

来源：https://stackoverflow.com/questions/74145826/sql-script-group-by-help-needed

1条答案

按热度按时间

bq3bfh9z1#

将当前查询设为派生表，然后执行以下操作：

SELECT userName, 
        weeknum,
        SUM(CASE WHEN pickID=homeID 
            AND homeScore > visitorScore THEN 1
            WHEN pickID=visitorID 
            AND visitorScore > homeScore THEN 1
        ELSE 0 END) AS "# of wins"
  FROM
(SELECT 
  p.userID,
  s.gameID,
  p.pickID,
  u.userName,
  s.weekNum,
  s.homeID, 
  s.homeScore, 
  s.visitorID, 
  s.visitorScore
FROM nflp_picks p 
INNER JOIN nflp_users u
  ON p.userID = u.userID 
INNER JOIN nflp_schedule s 
  ON p.gameID = s.gameID 
WHERE s.weekNum = 6
  AND u.userName <> 'admin' 
ORDER BY p.userID, s.gameTimeEastern, s.gameID) a
  GROUP BY userName, weeknum;

SUM()对CASE表达式检查pickID值是否与获胜球队相同。
Here's a trimmed down fiddle example升

赞(0）回复(0）举报 2022-10-22

我来回答

sql脚本按所需帮助分组

1条答案

相关问题

热门标签

最新问答