22,207
社区成员
发帖
与我相关
我的任务
分享
--建立测试环境
set nocount on
create table 主表(id varchar(20),名称 varchar(20),地址 varchar(20),编码 varchar(20))
insert into 主表 select '1','AAA','a','cc1'
insert into 主表 select '2','BBB','b','cc2'
insert into 主表 select '3','ccc','c','cc3'
insert into 主表 select '4','AAA','a','cc4'
create table 子表(Autoid varchar(20),id varchar(20),完成天数 varchar(20))
insert into 子表 select '1','1','5'
insert into 子表 select '2','1','7'
insert into 子表 select '3','2','12'
insert into 子表 select '4','2','15'
insert into 子表 select '5','2','14'
insert into 子表 select '6','3','2'
insert into 子表 select '7','3','4'
insert into 子表 select '8','3','9'
insert into 子表 select '9','3','8'
insert into 子表 select '10','4','10'
go
--测试
select 名称,地址,编码,count(*) ,
sum(case when 完成天数<=7 then 1 else 0 end ),
sum(case when 完成天数>7 and 完成天数<=14 then 1 else 0 end ),
sum(case when 完成天数>14 then 1 else 0 end )
from 主表 ,子表 where 主表.id=子表.id
group by 名称,地址,编码
--删除测试环境
drop table 子表
drop table 主表
set nocount off
declare @a table(id int,名称 varchar(10),地址 varchar(10),编码 varchar(10))
insert into @a select 1,'AAA','a','cc1'
insert into @a select 2,'BBB','b','cc2'
insert into @a select 3,'CCC','c','cc3'
insert into @a select 4,'AAA','a','cc4'
declare @b table(Autoid int,id int,完成天数 int)
insert into @b select 1,1,5
insert into @b select 2,1,7
insert into @b select 3,2,12
insert into @b select 4,2,15
insert into @b select 5,2,14
insert into @b select 6,3,2
insert into @b select 7,3,4
insert into @b select 8,3,9
insert into @b select 9,3,8
insert into @b select 10,4,10
select a.*,b.明细数,一周内完成,二周内完成,两周以上完成,c.同一名称 from @a a left join
(select id,count(1)明细数,
一周内完成=sum(case when 完成天数<=7 then 1 else 0 end),
二周内完成=sum(case when 完成天数>7 and 完成天数<=14 then 1 else 0 end),
两周以上完成=sum(case when 完成天数>14 then 1 else 0 end)
from @b group by id) b
on a.id=b.id
left join (select 名称,count(1)同一名称 from @a group by 名称)c
on a.名称=c.名称
create table 主表(id int, 名称 varchar(10), 地址 varchar(10) , 编码 varchar(10))
insert into 主表 values(1 , 'AAA' , 'a' , 'cc1')
insert into 主表 values(2 , 'BBB' , 'b' , 'cc2')
insert into 主表 values(3 , 'ccc' , 'c' , 'cc3')
insert into 主表 values(4 , 'AAA' , 'a' , 'cc4')
create table 子表(Autoid int,id int,完成天数 int)
insert into 子表 values(1 , 1 , 5 )
insert into 子表 values(2 , 1 , 7 )
insert into 子表 values(3 , 2 , 12 )
insert into 子表 values(4 , 2 , 15 )
insert into 子表 values(5 , 2 , 14 )
insert into 子表 values(6 , 3 , 2 )
insert into 子表 values(7 , 3 , 4 )
insert into 子表 values(8 , 3 , 9 )
insert into 子表 values(9 , 3 , 8 )
insert into 子表 values(10, 4 , 10 )
go
select a.名称,a.地址,a.编码,
子表明细数 = (select count(*) from 子表 where id = a.id),
一周内完成有多少项 = (select count(*) from 子表 where id = a.id and 完成天数 <= 7),
两周内完成有多少项 = (select count(*) from 子表 where id = a.id and 完成天数 <= 14),
两周以上完成的有多少项 = (select count(*) from 子表 where id = a.id and 完成天数 > 14),
两周以上完成的有多少项 = (select count(*) from 主表 where 名称 = a.名称)
from 主表 a
drop table 主表,子表
/*
名称 地址 编码 子表明细数 一周内完成有多少项 两周内完成有多少项 两周以上完成的有多少项 两周以上完成的有多少项
---------- ---------- ---------- ----------- ----------- ----------- ----------- -----------
AAA a cc1 2 2 2 0 2
BBB b cc2 3 0 2 1 1
ccc c cc3 4 2 4 0 1
AAA a cc4 1 0 1 0 2
(所影响的行数为 4 行)
*/
create view v_Test
as
select a.id,a.名称,a.地址,a.编码,b.Autoid,
b.完成天数 as 子表明细数,
c.[一周内完成有多少项],
c.[两周内完成的有多少项],
c.[两周以上完成的有多少项],
(select count(1) from 主表 where 名称=a.名称) as [主表内同一名称的多少项]
from 主表 a,子表 b,(
select id,
sum(case when 完成天数<=7 then 1 else 0 end) as [一周内完成有多少项],
sum(case when 完成天数>7 and 完成天数<=14 then 1 else 0 end) as [两周内完成的有多少项],
sum(case when 完成天数>14 then 1 else 0 end) as [两周以上完成的有多少项]
) as c
where a.id=b.id
and a.id=c.id
select m.名称,m.地址,m.编码,子表明细数=count(*)
,一周内完成有多少项=sum(case when 完成天数<=7 then 1 else 0 end)
,两周内完成的有多少项=sum(case when 完成天数<=14 then 1 else 0 end)
,两周以上完成的有多少项=sum(case when 完成天数>14 then 1 else 0 end)
,主表内同一名称的多少项=(select count(*) from maintb where 名称=m.名称)
from maintb m,subtb s
where m.id=s.id
group by m.名称,m.地址,m.编码
select m.名称,m.地址,m.编码,子表明细数=count(*)
,一周内完成有多少项=sum(case when 完成天数<=7 then 1 else 0 end)
,两周内完成的有多少项=sum(case when 完成天数<=14 then 1 else 0 end)
,两周以上完成的有多少项=sum(case when 完成天数>14 then 1 else 0 end)
,主表内同一名称的多少项=(select count(*) from maintb where 名称=m.名称)
from maintb m,subtb s
where m.id=s.id