SQL Server 查询 LEFT JOIN、SUM 和 GROUP BY，我被难住了！

2022-01-09 00:00:00 join sum group-by sql-server

我正在用我的大脑来对抗这个

I am beating my brain against this one

我有 3 个 SQL Server 2005 表

I have 3 SQL Server 2005 tables

用户奖励:

id, awardamount, userid, dateawarded, awardtypeid

用户:

id, firstname, lastname

奖励类型: 

id, title

所以如果 awards 表有行

1, 300.00, 3, 01-01-2011, 1
2, 125.00, 3, 01-05-2011, 1
3,  50.00, 2, 01-05-2011, 2

用户表行

1, john, smith
2, mark, smith
3, bob, smith

奖励类型

1, cash
2, prize

我希望输出看起来与此类似

and I want the output to look similar to this

bob smith, 425.00, cash
mark smith, 50, prize

等等等等

一个用户可以有多个奖励，结果需要显示唯一用户，但有总奖励金额.此外，还需要 2 个连接，一个用于获取用户表中的用户名/姓和奖励类型标题.

A user can have multiple awards, the results need to display unique users, but with there total award amount. in addition there needs to be 2 joins, one, to grab the users first name/last that's in a user table and the award type title.

所以我的查询看起来像这样(我知道它不起作用)

So my query is looking like this (i know it doesn't work)

SELECT id, userid, awardtypeid, SUM(awardamount) 
FROM awards a
LEFT JOIN userinfo ui ON ui.userid = a,userid
LEFT JOIN awardtypes ON awardtypesid = a.awardtypeid
GROUP BY userid

这可能吗?

推荐答案

你可能想要

SELECT userid, 
       awardtypeid, 
       SUM(awardamount) 
FROM   awards a 
       LEFT JOIN userinfo ui 
         ON ui.userid = a.userid 
       LEFT JOIN awardtypes 
         ON awardtypesid = a.awardtypeid 
GROUP  BY userid, 
          awardtypeid

或

SELECT userid, 
       SUM(awardamount) 
FROM   awards a 
       LEFT JOIN userinfo ui 
         ON ui.userid = a.userid 
       LEFT JOIN awardtypes 
         ON awardtypesid = a.awardtypeid 
GROUP  BY userid

这会删除 id 列(可能不是您想要分组的内容)

This drops the id Column (probably not what you want to group on)

在第一种情况下，我在 select 中包含了 Awardtypeid，但这意味着您还必须将其添加到 group by.

In the first case I included the awardtypeid in the select but this means you must also add that to the group by.

相关文章