--fk_temp 客服记录表 admin_id srcid
--Admin 客服表 adminid
--BASE_ZX_SRC来源类型表 srcidselect fk.src,fk.admin_id,fk.pname,adm.Name,src.Name
from fk_temp fk left join Admin adm on fk.admin_id=adm.Id
left join BASE_ZX_SRC src on fk.src=src.ID
--统计形式如下
-- 来源1数 来源2数
--客服1 5 6
--客服2 7 8
--Admin 客服表 adminid
--BASE_ZX_SRC来源类型表 srcidselect fk.src,fk.admin_id,fk.pname,adm.Name,src.Name
from fk_temp fk left join Admin adm on fk.admin_id=adm.Id
left join BASE_ZX_SRC src on fk.src=src.ID
--统计形式如下
-- 来源1数 来源2数
--客服1 5 6
--客服2 7 8
解决方案 »
- EXEC输出调用外部变量要怎么办呢?
- 一道关SQL查询select的问题,求解
- NULL类型转换!
- 工作交接,如何导出sqlserver2000库上所有建表语句存储过程存储函数触发器等等的创建sql?
- 新手,这个问题会不会很难呀。
- 发现一个奇怪的问题: 可以在查询分析器里面建立一个字段 numeric(38) 型的, 但是只可以用视图的方式在企业管理器里面建立一个 numeric
- 关于字符串的比较运算
- 请问一个简单的SQL查询语句的小问题(SQL Server 2000)
- 有朋友做过超市进出库管理的开发吗?有问题请教,请进来看看。(在线等待)
- 有谁知道我国的证券交易系统使用的什么数据库?
- 找不到列 "DBO" 或用户定义的函数或聚合 "DBO.F_CHECKDT",或者名称不明确。
- myeclipse远程连接sql server2008的问题
b.adminid,
sum(case when c.srcid=1 then 1 else 0 end) as 来源1数,
sum(case when c.srcid=2 then 1 else 0 end) as 来源2数
from
fk_temp left join Admin b
on
a.admin_id=b.adminid
left join
BASE_ZX_SRC c
on
a.srcid=c.srcid
group by
b.adminid
select fk.pname,
sum(case when src.Name='来源1' then 1 else 0 end),
sum(case when src.Name='来源2' then 1 else 0 end),
sum(case when src.Name='来源3' then 1 else 0 end)
from fk_temp fk left join Admin adm on fk.admin_id=adm.Id
left join BASE_ZX_SRC src on fk.src=src.ID
group by fk.pname
[ID] [int] IDENTITY(1,1) NOT NULL,
[I_datetime] [datetime] NULL,
[src] [nvarchar](50) NULL,
[type] [nvarchar](50) NULL,
[corName] [nvarchar](500) NULL,
[I_area] [nvarchar](50) NULL,
[linkman] [nvarchar](50) NULL,
[tel] [nvarchar](50) NULL,
[phone] [nvarchar](50) NOT NULL,
[fax] [nvarchar](50) NULL,
[mail] [nvarchar](50) NULL,
[corAddress] [nvarchar](500) NULL,
[notes] [nvarchar](500) NULL,
[pname] [nvarchar](50) NULL,
[ptype] [nvarchar](50) NULL,
[u_datetime] [datetime] NULL,
[Status] [int] NULL,
[nums] [int] NULL,
[admin_id] [int] NULL,
[productkind_Id] [int] NULL,
[Last_do] [nvarchar](50) NULL,
[OPP_id] [int] NULL,
CONSTRAINT [PK_fk_temp] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[Admin](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Passwd] [nchar](32) NULL,
[Department_id] [int] NULL,
[Role_Id] [int] NULL,
[Createdate] [datetime] NULL,
[Lastlogindate] [datetime] NULL,
[Lastloginip] [nvarchar](50) NULL,
[Status] [int] NULL,
[Admin_Bank_List] [nvarchar](100) NULL,
[Admin_Employee] [nvarchar](100) NULL,
[Is_Head] [int] NULL,
CONSTRAINT [PK_Admin] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[BASE_ZX_SRC](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Status] [int] NOT NULL,
CONSTRAINT [PK_BASE_ZX_SRC] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
select
b.Name,
sum(case when c.ID=1 then 1 else 0 end) as 来电咨询,
sum(case when c.id=2 then 1 else 0 end) as 在线咨询,
sum(case when c.id=3 then 1 else 0 end) as 商桥,
sum(case when c.id=4 then 1 else 0 end) as 后台咨询,
sum(case when c.id=5 then 1 else 0 end) as 邮件咨询,
sum(case when c.id=6 then 1 else 0 end) as 传真咨询,
sum(case when c.id=7 then 1 else 0 end) as 阿里巴巴
from
fk_temp a left join Admin b
on
a.admin_id=b.Id
left join
BASE_ZX_SRC c
on
a.src=c.ID
where a. I_datetime>'2012-10-08 '
group by
b.Name
非常感谢!
A020 5 6 0 1 0 0 0
elaine 1 0 0 0 0 0 1
sakura 8 0 0 0 0 0 1
sally 10 1 0 0 0 0 0
002 2 0 0 0 0 0 0汇总 26 7 0 1 0 0 2
怎么添加一个汇总
汇总,
sum(case when c.ID=1 then 1 else 0 end) as 来电咨询,
sum(case when c.id=2 then 1 else 0 end) as 在线咨询,
sum(case when c.id=3 then 1 else 0 end) as 商桥,
sum(case when c.id=4 then 1 else 0 end) as 后台咨询,
sum(case when c.id=5 then 1 else 0 end) as 邮件咨询,
sum(case when c.id=6 then 1 else 0 end) as 传真咨询,
sum(case when c.id=7 then 1 else 0 end) as 阿里巴巴
from
fk_temp a
left join
BASE_ZX_SRC c
on
a.src=c.ID
where a. I_datetime>'2012-10-08 '
select
isnull(b.Name,'合计'),
sum(case when c.ID=1 then 1 else 0 end) as 来电咨询,
sum(case when c.id=2 then 1 else 0 end) as 在线咨询,
sum(case when c.id=3 then 1 else 0 end) as 商桥,
sum(case when c.id=4 then 1 else 0 end) as 后台咨询,
sum(case when c.id=5 then 1 else 0 end) as 邮件咨询,
sum(case when c.id=6 then 1 else 0 end) as 传真咨询,
sum(case when c.id=7 then 1 else 0 end) as 阿里巴巴
from
fk_temp a left join Admin b
on
a.admin_id=b.Id
left join
BASE_ZX_SRC c
on
a.src=c.ID
where a. I_datetime>'2012-10-08 '
group by
b.Name
with rollup