部门月份业绩表
CREATE TABLE [dbo].[bmy](
[mon] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[dep] [varchar](18) COLLATE Chinese_PRC_CI_AS NULL,
[yj] [numeric](18, 0) NULL,
[id] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
部门表
CREATE TABLE [dbo].[depart](
[dep] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[dname] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
数据统计:
--统计每个部门的每个月份业绩
select depart.dname dep,sum(case when bmy.mon ='一月份' then bmy.yj else null end) as 一月份,
sum(case when bmy.mon ='二月份' then bmy.yj else null end) as 二月份,
sum(case when bmy.mon ='三月份' then bmy.yj else null end) as 三月份
from bmy
left join depart on bmy.dep = depart.dep
group by depart.dname
order by depart.dname
--统计在bmy中,存在多个月份记录的月份,和次数
select mon ,count(*) num from bmy group by mon having count(*) > 1
select * from( select mon ,count(*) as num from bmy group by mon )t where t.num > 1