交叉连接查询corss.txt

动态sql语句基本语法 1 :普通SQL语句可以用Exec执行 eg: Select * from tableName Exec( select * from tableName )

declare @t table(id int,name varchar(20),bh int)
insert into @t
select 1,'aa',10
union all select 2,'bb',11


select name=a.name,bh=b.bh,sl=isnull(c.sl,0)from 
(select name from @t group by name) a
cross join 
(select bh from @t group by bh) b
left join 
(select name,bh,sl=count(*) from @t group by name,bh) c 
on a.name=c.name and b.bh=c.bh