Try:
Select A.Publisher, aa.ID as StyleID,IsNull(count(a.TID),0) as totalcount
from (Select top 4 (select sum(1) from sysobjects where name<= a.name) as id from sysobjects a) aa
left join MyTable A on aa.id = a.StyleID
group by A.Publisher,A.StyleID
order by A.Publisher,A.StyleID
Select A.Publisher, aa.ID as StyleID,IsNull(count(a.TID),0) as totalcount
from (Select top 4 (select sum(1) from sysobjects where name<= a.name) as id from sysobjects a) aa
left join MyTable A on aa.id = a.StyleID
group by A.Publisher,A.StyleID
order by A.Publisher,A.StyleID
Select A.Publisher, aa.ID as StyleID,IsNull(count(a.TID),0) as totalcount
from (Select top 4 (select sum(1) from sysobjects where name<= a.name) as id from sysobjects a) aa
left join MyTable A on aa.id = a.StyleID
group by A.Publisher,aa.ID
order by A.Publisher,aa.ID
From (select 1 StyleID union select 2 union select 3 union 4) b left join MyTable A
b.styleid=a.styleid
group by A.Publisher,b.StyleID
order by A.Publisher,A.StyleID
From (select * from (select 1 StyleID union select 2 union select 3 union select 4) aa,(select distinct Publisher from MYTable) bb) b left join MyTable A
on b.styleid=a.styleid and b.Publisher=a.Publisher
group by b.Publisher,b.StyleID
order by b.Publisher,b.StyleID
顺便问一下我的这个查询语句有无优化的可能,有没有更好的办法?
select AB.Publisher, AB.StyleID,count(TID) as totalcount
From (select a.publisher,b.styleid
from (select distinct publisher
from MyTable) as A,
(select 1 StyleID union select 2 union select 3 union select 4) as B) as AB
left join MyTable AA
on AB.publisher=AA.publisher
and AB.styleID=AA.StyleID
group by AB.Publisher,AB.StyleID
order by AB.Publisher,AB.StyleID该语句实现的功能:统计各个出版者发布的各种类型书籍的数目。
select AB.Publisher, AB.StyleID,count(TID) as totalcount
From (select a.publisher,b.styleid
from 编码表 as A,
(select 1 StyleID union all select 2 union all select 3 union all select 4) as B) as AB
left join MyTable AA
on AB.publisher=AA.publisher
and AB.styleID=AA.StyleID
group by AB.Publisher,AB.StyleID
order by AB.Publisher,AB.StyleID