多表联合查询count的问题
我想求按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
请大家帮忙,谢谢!!
[解决办法]
没有数据啊~~ 贴点数据出来啊！
[解决办法]
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 行受影响)