我想求按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