select <br />
''
as
type, <br />
`
class
`.`name`
as
cname, <br />
`score`.`cid`, <br />
`score`.`uid`, <br />
`user`.`name`
as
uname,<br />
`score`<br />
from `score`<br />
right join `
class
` on `score`.`cid` = `
class
`.`cid`<br />
left join `user` on `score`.`uid` = `user`.`uid`<br />
<br />
union all(<br />
select <br />
'汇总'
, <br />
`c`.`cname`, <br />
`c`.`cid`, <br />
`c`.`uid`, <br />
count
(`cid`)
as
uname,<br />
sum(`score`)
as
score<br />
from (<br />
select <br />
''
as
type, <br />
`
class
`.`name`
as
cname, <br />
`score`.`cid`, <br />
`score`.`uid`, <br />
`user`.`name`
as
uname,<br />
`score`<br />
from `score`<br />
right join `
class
` on `score`.`cid` = `
class
`.`cid`<br />
left join `user` on `score`.`uid` = `user`.`uid`<br />
) c group by `cname`<br />
)<br />
<br />
order by `cname` asc, `type` asc;