SQL Server Query LEFT JOIN, SUM and GROUP BY and I

问题:

I am beating my brain against this one

I have 3 SQL Server 2005 tables

userawards:

id, awardamount, userid, dateawarded, awardtypeid 

user:

id, firstname, lastname

awardtypes:

id, title

So if the awards table had the rows

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

user table rows

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

award types

1, cash
2, prize

and I want the output to look similar to this

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

etc etc.

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

Is this even possible?

回答1:

You probably want

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 

or

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

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

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

回答2:

You can do it but the way you do it now is that it will show the number of awardamount, or bactually, the sum of awardamount - but per id, user, awardtypeid combination. You need to grab the user and sum of awards by itself, then join up with awardtype id - just be aware that the sum of award amounts is repeated for every awardtypeid

SELECT 
     ??.id ,
     a.userid , 
     a.awardtypeid , 
     ab.awardamount ,
     <whateverelse>
FROM 
     (select userid, SUM(awardamount) as awardamount FROM awards GROUP BY userid) AS ab
INNER JOIN awards AS a on ab.userid = a.userid
LEFT JOIN userinfo AS ui  
  ON ui.userid = a.userid
LEFT JOIN awardtypes AS at 
  ON awardtypesid = a.awardtypeid 

By adding up the sum of awardamount per user before you join it in, you should be able to get what you want, without any grouping.

回答3:

SELECT
  ui.FirstName
 ,ui.LastName
 ,at.Title AS Award
 ,SUM(a.AwardAmount) AS AwardAmount
FROM Awards a
  INNER JOIN UserInfo ui ON ui.UserId = a.UserId
  INNER JOIN AwardTypes at ON at.AwardTypeId = a.AwardTypeId
GROUP BY ui.FirstName, ui.LastName, at.Title
ORDER BY ui.LastName, ui.FirstName

回答4:

If I understand this correctly, there should be no more than one row per user and you need a total award amount per user. On other side you want to know what kind of awards every user had. You can show comma delimited list of awards for every user:

select 
    usr.FirstName,
    usr.LastName, 
    awd.AwardAmount,
    AwardTypes = (   
        select Title + ','
        from UserAwards uaw
            join AwardTypes awt on
                uaw.AwardTypeId = awt.Id
        where uaw.UserId = usr.id
        for xml path(''))
from [User] usr
    join (
        select UserId, sum(AwardAmount) AwardAmount
        from UserAwards uaw
        group by UserId
    ) awd on
        awd.UserId = usr.Id