【急，谢谢了】分类统计问题，希望大家帮忙

[抬头望远，低头沉思]

小弟SQL不精，遇到一个分类统计问题，希望大家帮忙，比较急，分不是问题，谢谢，解决马上结账，

A表结构：活动主表，包括本次活动ID（主键）和计划参加活动的人数
MID Persons
----------------------
1 3
2 1
3 2
55 55

B表结构：活动子表，包括具体参加人员的信息
包括：子表ID ,活动的ID（对应主表主键），参加人员ID，以及该人员参加活动的状态：State为int型（1：按时到，2：未来参加，3：迟到早退）
ID MID EmpID State
----------------------------------
1 1 201 1
2 3 222 3
3 2 180 1
4 55 199 2
5 1 199 2
6 1 222 2
7 3 199 1
..........

我想统计本次活动的人员参加情况,如下图
MID Persons 按时到人数 未来参加人数 迟到人数
-----------------------------------------------------------------
1 3 1 2 0
2 1 1 0 0
3 2 1 0 1
55 1 0 1 0


我是下面这个思路，但是只能统计按时到人数，若是在加上另外两种状态的统计，where语句中就矛盾了
SELECT A.MID, A.Persons, COUNT(B.MID) AS STrainJoin
FROM B INNER JOIN A ON
A.MID = B.MID
WHERE (B.State = 1)
GROUP BY B.TrainID,A.TrainID,A.TrainPersons

不知道大家有什么思路

[SQL77]

又来,SUM(CASE WHEN.....

[sgtzzc]

if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[MID] int,[EmpID] int,[State] int)
insert [B]
select 1,1,201,1 union all
select 2,3,222,3 union all
select 3,2,180,1 union all
select 4,55,199,2 union all
select 5,1,199,2 union all
select 6,1,222,2 union all
select 7,3,199,1

select 
  MID,
  count(1) as Persons,
  sum(case when State=1 then 1 else 0 end) as 按时到人数,
  sum(case when State=2 then 1 else 0 end) as 未来参加人数,
  sum(case when State=3 then 1 else 0 end) as  迟到人数
from
  B
group by
  MID


--测试结果:
/*
MID         Persons     按时到人数       未来参加人数      迟到人数
----------- ----------- ----------- ----------- -----------
1           3           1           2           0
2           1           1           0           0
3           2           1           0           1
55          1           0           1           0

(4 行受影响)

*/

[liangCK]

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-10-12 15:32:37
-------------------------------------
 
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (MID int,Persons int)
INSERT INTO @tb1
SELECT 1,3 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,2 UNION ALL
SELECT 55,55
 
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (ID int,MID int,EmpID int,State int)
INSERT INTO @tb2
SELECT 1,1,201,1 UNION ALL
SELECT 2,3,222,3 UNION ALL
SELECT 3,2,180,1 UNION ALL
SELECT 4,55,199,2 UNION ALL
SELECT 5,1,199,2 UNION ALL
SELECT 6,1,222,2 UNION ALL
SELECT 7,3,199,1

--SQL查询如下:

SELECT A.*,ISNULL(B.按时到人数,0) as 按时到人数,
    ISNULL(B.未来参加人数,0) as 未来参加人数,
    ISNULL(B.迟到早退,0) AS 迟到早退
FROM @tb1 AS A
    LEFT JOIN (SELECT MID,SUM(CASE WHEN State=1 THEN 1 ELSE 0 END) AS 按时到人数,
                 SUM(CASE WHEN State =2 THEN 1 ELSE 0 END) AS 未来参加人数,
                 SUM(CASE WHEN State=3 THEN 1 ELSE 0 END) AS 迟到早退
               FROM @tb2 GROUP BY MID) AS B
ON A.MID = B.MID

/*
MID         Persons     按时到人数       未来参加人数      迟到早退
----------- ----------- ----------- ----------- -----------
1           3           1           2           0
2           1           1           0           0
3           2           1           0           1
55          55          0           1           0

(4 行受影响)


*/

[水族杰纶]

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-12 15:30:24 
declare @A table([MID] int,[Persons] int)
Insert @A
select 1,3 union all
select 2,1 union all
select 3,2 union all
select 55,55
declare @B table([ID] int,[MID] int,[EmpID] int,[State] int)
Insert @B
select 1,1,201,1 union all
select 2,3,222,3 union all
select 3,2,180,1 union all
select 4,55,199,2 union all
select 5,1,199,2 union all
select 6,1,222,2 union all
select 7,3,199,1
select a.mid,
       sum(case  when State = 1 then 1 else 0 end) as N'按時到',
       sum(case  when State = 2 then 1 else 0 end) as N'未来参加',
       sum(case  when State = 3 then 1 else 0 end) as N'迟到早退'
from @A a,@B b where a.mid=b.mid group by a.mid
/*
(7 個資料列受到影響)
mid         按時到         未来参加        迟到早退
----------- ----------- ----------- -----------
1           1           2           0
2           1           0           0
3           1           0           1
55          0           1           0

(4 個資料列受到影響)
*/

[liangCK]

LZ是老用户了..应该知道开一个帖就可以了.

[navy887]

试试

select  mid,count(*) as Persons,
  sum(case when State=1 then 1 else 0 end) as 按时到人数,  sum(case when State=2 then 1 else 0 end) as 未来参加人数,  sum(case when State=3 then 1 else 0 end) as  迟到人数
from  B
group by mid

[gsk09]

SELECT 
	 A.MID
	,count(*) Persons
	,sum(case  when State = 1 then 1 else 0 end) as [按時到]
	,sum(case  when State = 2 then 1 else 0 end) as [未来参加]
	,sum(case  when State = 3 then 1 else 0 end) as [迟到早退]
FROM B INNER JOIN A ON 
      A.MID = B.MID 
GROUP BY B.TrainID,A.TrainID,A.TrainPersons 

[gsk09]

大家真快

[抬头望远，低头沉思]

呵呵，谢谢大家，我先试试，只要可以马上结账

[华夏小卒]

if object_id('ta') is not null drop table ta
go
create table ta(MID int,Persons int)
insert into ta select
1,  3 union all select
2,  1 union all select
3,  2 union all select
55,  55 
if object_id('tb') is not null drop table tb
go
create table tb(id int identity,MID int,EmpID int,State int)
insert into tb select
1, 201 ,1 union all select
3, 222 ,3 union all select
2, 180 ,1 union all select
55, 199 ,2 union all select
1, 199 ,2 union all select
1, 222 ,2 union all select
3, 199 ,1 

select a.mid,persons=count(*),
按时到人数=sum(case when State=1 then 1 else 0 end),
未来参加人数=sum(case when State=2 then 1 else 0 end),
迟到人数=sum(case when State=3 then 1 else 0 end)
from ta a
left join tb b on a.mid=b.mid
group by a.mid 



mid         persons     按时到人数       未来参加人数      迟到人数
----------- ----------- ----------- ----------- -----------
1           3           1           2           0
2           1           1           0           0
3           2           1           0           1
55          1           0           1           0

(4 行受影响)

[--小F--]

Create Table A(MID int,Persons int)
go

insert into A values(1,3)
insert into A values(2,1)
insert into A values(3,2)
insert into A values(55,55)


Create Table B(ID int,MID int, EmpID int, State int)
go

insert into B values(1, 1, 201, 1 )
insert into B values(2, 3, 222, 3)
insert into B values(3, 2, 180, 1 )
insert into B values(4, 55, 199, 2)
insert into B values(5, 1, 199, 2 )
insert into B values(6, 1, 222, 2 )
insert into B values(7, 3, 199, 1 )


select
     A.MID
    ,count(1) as Persons
    ,sum(case  when State = 1 then 1 else 0 end) as [按時到人数]
    ,sum(case  when State = 2 then 1 else 0 end) as [未来参加人数]
    ,sum(case  when State = 3 then 1 else 0 end) as [迟到早退人数]
from 
   B  
join A on 
  A.MID = B.MID 
group by 
   A.MID
drop table a
drop table b
/*MID         Persons     按時到人数       未来参加人数      迟到早退人数
----------- ----------- ----------- ----------- -----------
1           3           1           2           0
2           1           1           0           0
3           2           1           0           1
55          1           0           1           0

(4 行受影响)

*/