sql查询统计问题

[SeaHome1981]

sql2005
表A
numbm date mark
2 2013 5
2 2013 7
2 2013 5
3 2013 5
3 2013 7
3 2013 5

表B
numbm bmmc
2 人事部门
3 后勤部门

如何实现查询条件为date＝2013 and mark in（5，7）的以下查询结果

人事部 后勤部门
count（numbm） count（numbm）

就是查询符合查询条件的行数合，统计用

[SeaHome1981]

感谢楼上二位，效果实现了，但是这个要求是我自己提的不够准备，准备另开帖子请教！

[發糞塗牆貮號]

如果部门很少 写死得了 否则JOIN后 然后行专列吧

[SeaHome1981]

忘了说了，查询结果里的部门也是动态的，根据查询条件来确定

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-16 10:35:20
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([numbm] int,[date] int,[mark] int)
insert [A]
select 2,2013,5 union all
select 2,2013,7 union all
select 2,2013,5 union all
select 3,2013,5 union all
select 3,2013,7 union all
select 3,2013,5 union all
select 3,2013,6
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([numbm] int,[bmmc] varchar(8))
insert [B]
select 2,'人事部门' union all
select 3,'后勤部门'
--------------开始查询--------------------------
IF OBJECT_ID('tempdb..#t','u')IS NOT NULL 
DROP TABLE #t

select bmmc,COUNT(CASE WHEN bmmc=bmmc THEN 1 ELSE NULL END )[countNumb] INTO #t
from [A] LEFT JOIN [b] ON a.[numbm]=b.[numbm]
where [date]=2013 and mark IN (5,7)
GROUP BY bmmc


declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(bmmc)+'=max(case when [bmmc]='+quotename(bmmc,'''')+' then [countNumb] else 0 end)'
from #t group by bmmc
SET @s=SUBSTRING(@s,2,LEN(@s))
exec('select '+@s+' from #t  ')
----------------结果----------------------------
/* 
后勤部门        人事部门
----------- -----------
3           3

*/

[LongRui888]

试试这个：

if object_id('[A]') is not null drop table [A]
go 
create table [A]([numbm] int,[date] int,[mark] int)
insert [A]
select 2,2013,5 union all
select 2,2013,7 union all
select 2,2013,5 union all
select 3,2013,5 union all
select 3,2013,7 union all
select 3,2013,5 union all
select 3,2013,6


if object_id('[B]') is not null drop table [B]
go 
create table [B]([numbm] int,[bmmc] varchar(8))
insert [B]
select 2,'人事部门' union all
select 3,'后勤部门'
go


declare @sql varchar(8000)

set @sql = ''

select @sql = @sql + ',count(case when bmmc='''+b.bmmc+''' then 1 else null end) as ['+
                     b.bmmc+']'
from A
inner join B
        on a.numbm = b.numbm
group by b.bmmc


select @sql = 'select '+ stuff(@sql,1,1,'') + 
              ' from A
				inner join B
						on a.numbm = b.numbm' 

exec(@sql)
/*
后勤部门	人事部门
4	3
*/

[LongRui888]

加上了过滤条件：

if object_id('[A]') is not null drop table [A]
go 
create table [A]([numbm] int,[date] int,[mark] int)
insert [A]
select 2,2013,5 union all
select 2,2013,7 union all
select 2,2013,5 union all
select 3,2013,5 union all
select 3,2013,7 union all
select 3,2013,5 union all
select 3,2013,6


if object_id('[B]') is not null drop table [B]
go 
create table [B]([numbm] int,[bmmc] varchar(8))
insert [B]
select 2,'人事部门' union all
select 3,'后勤部门'
go


declare @sql varchar(8000)

set @sql = ''

select @sql = @sql + ',count(case when bmmc='''+b.bmmc+''' then 1 else null end) as ['+
                     b.bmmc+']'
from A
inner join B
        on a.numbm = b.numbm
group by b.bmmc


select @sql = 'select '+ stuff(@sql,1,1,'') + 
              ' from A
				inner join B
						on a.numbm = b.numbm
				where a.date=2013 and a.mark in (5,7)' 

exec(@sql)
/*
后勤部门	人事部门
3	3
*/