求一个sql语句

一张audio表
  id,title,artist,album
  1 歌名1 歌手a 专辑A
  2 歌名2 歌手b 专辑B
  3 歌名3 歌手a 专辑C
  4 歌名4 歌手c 专辑D
  5 歌名5 歌手c 专辑E

需要查询每个artist有多少歌，有多少album。

  1 歌手a 2 2
  2 歌手b 1 1
  3 歌手c 2 1

=============================
这么一个sql怎么写？求教。

希望我表达清楚了。

--sql 2000
SQL code

create table audio(id int,title varchar(10),artist varchar(10),album varchar(10))
insert into audio values(1 ,'歌名1', '歌手a', '专辑A')
insert into audio values(2 ,'歌名2', '歌手b', '专辑B')
insert into audio values(3 ,'歌名3', '歌手a', '专辑C')
insert into audio values(4 ,'歌名4', '歌手c', '专辑D')
insert into audio values(5 ,'歌名5', '歌手c', '专辑E')
go


select artist , count(1) title , count(distinct album) album from audio group by artist
/*
artist     title       album       
---------- ----------- ----------- 
歌手a        2           2
歌手b        1           1
歌手c        2           2

（所影响的行数为 3 行）
*/


select (select count(1) from 
(
  select artist , count(1) title , count(distinct album) album from audio group by artist
) n where n.artist < m.artist) + 1 px, m.* from
(
  select artist , count(1) title , count(distinct album) album from audio group by artist
) m

/*
px          artist     title       album       
----------- ---------- ----------- ----------- 
1           歌手a        2           2
2           歌手b        1           1
3           歌手c        2           2

（所影响的行数为 3 行）
*/


drop table audio

--sql 2005
SQL code

create table audio(id int,title nvarchar(20),artist nvarchar(20),album nvarchar(20))
insert into audio values(1 ,N'歌名1', N'歌手a', N'专辑A')
insert into audio values(2 ,N'歌名2', N'歌手b', N'专辑B')
insert into audio values(3 ,N'歌名3', N'歌手a', N'专辑C')
insert into audio values(4 ,N'歌名4', N'歌手c', N'专辑D')
insert into audio values(5 ,N'歌名5', N'歌手c', N'专辑E')
go


select artist , count(1) title , count(distinct album) album from audio group by artist
/*
artist               title       album
-------------------- ----------- -----------
歌手a                  2           2
歌手b                  1           1
歌手c                  2           2

(3 行受影响)
*/


select row_number() over(order by title) px, m.* from
(
  select artist , count(1) title , count(distinct album) album from audio group by artist
) m

/*
px                   artist               title       album
-------------------- -------------------- ----------- -----------
1                    歌手b                  1           1
2                    歌手c                  2           2
3                    歌手a                  2           2

(3 行受影响)
*/


drop table audio

select id,artist,count(title) as 歌曲数,count(ablum）as 专辑数 from tb group by artist

select id,artist,count(title) as 歌曲数,count(ablum）as 专辑数 from tb group by artist

SQL code

if object_id('audio') is not null
   drop table audio
go
create table audio
(
 id int identity(1,1),
 title varchar(10),
 artist varchar(10),
 album varchar(10)
)
go
insert into audio(title,artist,album)
select '歌名1','歌手a','专辑A' union all
select '歌名2','歌手b','专辑B' union all
select '歌名3','歌手a','专辑C' union all
select '歌名4','歌手c','专辑D' union all
select '歌名5','歌手c','专辑E'
go
select row=row_number() over(order by getdate()),* from 
(
 select artist,歌名数=count(distinct title),专辑数=count(album) from audio group by artist
) t
go
/*
row                  artist     歌名数         专辑数
-------------------- ---------- ----------- -----------
1                    歌手a        2           2
2                    歌手b        1           1
3                    歌手c        2           2

(3 行受影响)
*/

学学

create table tt(
id int,
title char(10),
artist char(10),
ablum char(10)
)
insert tt
select 1,'歌名1','歌手a','专辑A'union all
select 2,'歌名2','歌手b','专辑B'union all
select 3,'歌名3','歌手a','专辑C'union all
select 4,'歌名4','歌手c','专辑D'union all
select 5,'歌名5','歌手c','专辑E'

select artist,COUNT(title) as 歌曲数,COUNT(ablum) as 专辑数 from tt group by artist