高手请指点 多表联合分组查询
我想通过三个表联事查询并按姓名进行分组
这是下面的代码
select C1.UserName as 姓名,C.ID as 计算机名,R.fee as 总费用
from Record as R
inner join Card as C1 on(C1.ID=R.Cardid)
inner join Computer as C on(C.ID=R.ComputerID)
WHERE datepart(week,R.ENDTIME)=DATEPART(WEEK,getdate())
group by C1.UserName
错误提示是：
服务器: 消息 8120，级别 16，状态 1，行 1
列 'C.ID ' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
服务器: 消息 8120，级别 16，状态 1，行 1
列 'R.Fee ' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
[解决办法]
select
C1.UserName as 姓名,
C.ID as 计算机名,
SUM(R.fee) as 总费用
from
Record as R
inner join
Card as C1
on
C1.ID=R.Cardid
inner join
Computer as C
on
C.ID=R.ComputerID
WHERE
datepart(week,R.ENDTIME)=DATEPART(WEEK,getdate())
group by
C1.UserName,C.ID
[解决办法]
或者你這麼寫吧
select C1.UserName as 姓名,Min(C.ID) as 计算机名,SUM(R.fee) as 总费用
from Record as R
inner join Card as C1 on(C1.ID=R.Cardid)
inner join Computer as C on(C.ID=R.ComputerID)
WHERE datepart(week,R.ENDTIME)=DATEPART(WEEK,getdate())
group by C1.UserName
