有一个SQL统计的问题

[caiyi000]

比如我有表TB1
id1 name1
1 银行系统
2 职员系统
3 工资系统


TB2

id2 name2
1 软件
2 硬件
3 其他

TB3

id3 title id1 id2
1 test 1 1
2 test1 1 2
3 test2 2 1
4 test3 1 1


最后我想统计这样的结果

银行系统 软件 2次 硬件1次 其他0次
职员系统 软件 0次 硬件1次 其他0次
工资系统 软件 0次 硬件0次 其他0次

SQL语句怎么实现

[道素]

;with TB1(id1,name1) AS (
  SELECT  1,N'银行系统' union all
SELECT  2,N'职员系统' union all
SELECT  3,N'工资系统'
),TB2(id2,name2) AS (
  SELECT  1,N'软件' union all
  SELECT  2,N'硬件' union all
  SELECT  3,N'其他'
),TB3(id3,title,id1,id2) AS (
  SELECT  1,'test',1,1 UNION ALL
  SELECT  2,'test1',1,2 UNION ALL
  SELECT  3,'test2',2,1 UNION ALL
  SELECT  4,'test3',1,1 
  
)
SELECT * FROM (
SELECT tb3.id3,tb2.name2,tb1.name1 FROM tb1 LEFT JOIN  tb3 ON tb1.id1=tb3.id1 LEFT JOIN tb2 ON tb2.id2=tb3.id2 
) t PIVOT(COUNT(id3) for name2 IN ([软件],[硬件],[其他])) u

/*
name1	软件	硬件	其他
工资系统	0	0	0
职员系统	1	0	0
银行系统	2	1	0
*/

[唐诗三百首]

create table TB1(id1 int, name1 varchar(10))

insert into TB1
 select 1, '银行系统' union all
 select 2, '职员系统' union all
 select 3, '工资系统'

create table TB2(id2 int, name2 varchar(10))

insert into TB2
 select 1, '软件' union all
 select 2, '硬件' union all
 select 3, '其他'

create table TB3(id3 int,title varchar(10), id1 int, id2 int)

insert into TB3
 select 1, 'test', 1, 1 union all
 select 2, 'test1', 1, 2 union all
 select 3, 'test2', 2, 1 union all
 select 4, 'test3', 1, 1


with t as(
select c.id1, c.name1,c.name2+rtrim(isnull(d.qty,0))+'次' 'name2'
 from (select a.id1, a.name1, b.name2
           from TB1 a
           cross join TB2 b) c
 left join (select b.name1,c.name2,count(1) 'qty'
                from TB3 a
                inner join TB1 b on a.id1=b.id1
                inner join TB2 c on a.id2=c.id2 
                group by b.name1,c.name2) d on c.name1=d.name1 and c.name2=d.name2
)
select e.name1,
           stuff((select ' '+f.name2
                    from t f 
                    where f.name1=e.name1
                    for xml path('')),1,1,'') 'name2'
  from t e
  group by e.id1, e.name1
  order by e.id1

/*
name1      name2
---------- ---------------------------------------------------------------
银行系统       软件2次 硬件1次 其他0次
职员系统       软件1次 硬件0次 其他0次
工资系统       软件0次 硬件0次 其他0次

(3 row(s) affected)
*/

[道素]

如果非要你那种输出内容，稍微处理下就行，但感觉没太大必要：

SELECT *,N'软件'+convert(nvarchar,软件)+N'次',N'硬件'+convert(nvarchar,硬件)+N'次',N'其他'+convert(nvarchar,其他)+N'次' FROM (
SELECT tb3.id3,tb2.name2,tb1.name1 FROM tb1 LEFT JOIN  tb3 ON tb1.id1=tb3.id1 LEFT JOIN tb2 ON tb2.id2=tb3.id2 
) t PIVOT(COUNT(id3) for name2 IN ([软件],[硬件],[其他])) u
/*
name1	软件	硬件	其他	(No column name)	(No column name)	(No column name)
工资系统	0	0	0	软件0次	硬件0次	其他0次
职员系统	1	0	0	软件1次	硬件0次	其他0次
银行系统	2	1	0	软件2次	硬件1次	其他0次
*/