多表联合查询count的问题

[fengjing888]

我想求按t_users表中的不同user_id 查出t_activity表中不同的act_type的count(act_type),act_type 为 t_activitytype中top 5 的type_id,

t_users.user_id=t_opporunity.opporunity_create,
t_opporunity.opporunity_id=t_activity.opporuniyt_id,
t_activity.act_type=t_activityType.type_id

得到的结果是这样:
用户名 type_name1 type_name2 type_name3 type_name4
张三 2 5 0 9
李四 0 4 3 5


表结构如下:
CREATE TABLE [dbo].[t_Opporunity] (
[Opporunity_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Opporunity_CustID] [int] NULL ,
[Opporunity_Content] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[Opporunity_Create] [int] NULL ,
[Opporunity_DelFlag] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[t_Users] (
[User_ID] [int] IDENTITY (1, 1) NOT NULL ,
[User_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[User_CompanyID] [int] NULL ,
[User_Code] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[User_PassWord] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[User_Sex] [bit] NULL ,
[User_DelFlag] [int] NULL ,
[User_Position] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[t_ActivityType] (
[Type_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Type_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[t_Activity] (
[Act_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Opporunity_ID] [int] NULL ,
[Act_Date] [datetime] NULL ,
[Act_Type] [int] NULL ,
[Act_Phase] [int] NULL ,
[Act_IntendingDate] [datetime] NULL ,
[Act_IntendingMoney] [money] NULL ,
[Act_NextDate] [datetime] NULL ,
[Act_NextType] [int] NULL ,
[Act_Bewrite] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[Act_NextBewrite] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[Act_SumUp] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
[Act_DelFlag] [int] NULL ,
[SuccessRate] [float] NULL
) ON [PRIMARY]
GO


请大家帮忙,谢谢!!

[fengjing888]

怎么没人帮忙啊?!郁闷!!

[中国风]

CREATE TABLE [dbo].[t_Opporunity] (
[Opporunity_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Opporunity_CustID] [int] NULL ,
[Opporunity_Content] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[Opporunity_Create] [int] NULL ,
[Opporunity_DelFlag] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[t_Users] (
[User_ID] [int] IDENTITY (1, 1) NOT NULL ,
[User_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[User_CompanyID] [int] NULL ,
[User_Code] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[User_PassWord] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[User_Sex] [bit] NULL ,
[User_DelFlag] [int] NULL ,
[User_Position] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[t_ActivityType] (
[Type_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Type_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[t_Activity] (
[Act_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Opporunity_ID] [int] NULL ,
[Act_Date] [datetime] NULL ,
[Act_Type] [int] NULL ,
[Act_Phase] [int] NULL ,
[Act_IntendingDate] [datetime] NULL ,
[Act_IntendingMoney] [money] NULL ,
[Act_NextDate] [datetime] NULL ,
[Act_NextType] [int] NULL ,
[Act_Bewrite] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[Act_NextBewrite] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[Act_SumUp] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
[Act_DelFlag] [int] NULL ,
[SuccessRate] [float] NULL
) ON [PRIMARY]

insert into t_activitytype (type_name) values('电话')
insert into t_activitytype (type_name) values('短信')
insert into t_activitytype (type_name) values('邮件')
insert into t_activitytype (type_name) values('传真')

insert into t_opporunity values(4,'AAA',2,0)
insert into t_opporunity values(4,'BBB',1,0)
insert into t_opporunity values(4,'CCC',1,0)
insert into t_opporunity values(4,'DDD',2,0)

insert into t_activity values (1,2007-02-30,2,0,2007-03-30,0,2007-04-30,1,'AAA','','',0,10.1)
insert into t_activity values (2,2007-02-30,1,0,2007-03-30,0,2007-04-30,1,'BBB','','',0,10.1)
insert into t_activity values (3,2007-02-30,1,0,2007-03-30,0,2007-04-30,1,'AAA','','',0,10.1)
insert into t_activity values (4,2007-02-30,4,0,2007-03-30,0,2007-04-30,1,'AAA','','',0,10.1)


insert into t_users values('张三',1,'000001',0,1,0,1)
insert into t_users values('李四',1,'000001',0,1,0,1)


declare @sql varchar(4000)
set @sql=''
select @sql=@sql+',[type_name'+rtrim(Type_ID)+']=sum(case t_activitytype.Type_ID when '+rtrim(Type_ID)
+' then 1 else 0 end)'
from t_activitytype group by Type_ID
--print @sql
exec ('select t_users.[User_Name]'+@sql+'
from t_users join t_opporunity on t_users.[user_id]=t_opporunity.opporunity_create
join t_activity on t_opporunity.[Opporunity_ID]=t_activity.[Opporunity_ID]
join t_activityType on t_activity.act_type=t_activityType.type_id
group by t_users.[User_Name]')



User_Name type_name1 type_name2 type_name3 type_name4
-------------------------------------------------- ----------- ----------- ----------- -----------
李四 0 1 0 1
张三 2 0 0 0

(2 行受影响)

[fengjing888]

谢谢!

[leo_lesley]

没有数据啊~~ 贴点数据出来啊！

[fengjing888]

数据如下:

insert into t_activitytype (type_name) values('电话')
insert into t_activitytype (type_name) values('短信')
insert into t_activitytype (type_name) values('邮件')
insert into t_activitytype (type_name) values('传真')

insert into t_opporunity values(4,'AAA',2,0)
insert into t_opporunity values(4,'BBB',1,0)
insert into t_opporunity values(4,'CCC',1,0)
insert into t_opporunity values(4,'DDD',2,0)

insert into t_activity values (1,2007-02-30,2,0,2007-03-30,0,2007-04-30,1,'AAA','','',0,10.1)
insert into t_activity values (2,2007-02-30,1,0,2007-03-30,0,2007-04-30,1,'BBB','','',0,10.1)
insert into t_activity values (3,2007-02-30,1,0,2007-03-30,0,2007-04-30,1,'AAA','','',0,10.1)
insert into t_activity values (4,2007-02-30,4,0,2007-03-30,0,2007-04-30,1,'AAA','','',0,10.1)


insert into t_users values('张三',1,'000001',0,1,0,1)
insert into t_users values('李四',1,'000001',0,1,0,1)