22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE proc Pr_100
@lendDate1 varchar(4),
@lendDate2 varchar(4)
AS
DECLARE @SQL VARCHAR(5000)
SET @SQL='SELECT UPPER(left(call_no,1)) as KIND'
SELECT @SQL= @SQL+ ',sum(CASE WHEN DOC_TYPE_CODE = '''+DOC_TYPE_CODE+''' THEN 1 else 0 END) ['+DOC_TYPE_CODE+'],['+
DOC_TYPE_CODE+'总数]=count(distinct case when DOC_TYPE_CODE='''+DOC_TYPE_CODE+''' then call_no end)'
FROM (SELECT DISTINCT DOC_TYPE_CODE FROM Item ) as A ORDER BY DOC_TYPE_CODE
SET @SQL=@SQL+' FROM (SELECT * from item where IN_YEAR BETWEEN '''+@lendDate1+''' AND '''+@lendDate2+''' ) X
GROUP BY UPPER(left(call_no,1))
ORDER BY UPPER(left(call_no,1)) '
EXEC (@SQL)
Pr_100 '2009','2009'
KIND 01 01总数 02 02总数
---- ----------- ----------- ----------- -----------
A 10 4 4 3
B 3 2 2 2
C 1 1 1 1
(3 行受影响)
Pr_100 '2008','2009'
KIND 01 01总数 02 02总数
---- ----------- ----------- ----------- -----------
A 16 4 8 3
B 5 2 4 2
C 2 1 2 1
(3 行受影响)
if object_id('item')is not null
drop table item
if object_id('pr_100') is not null
drop procedure pr_100
create table ITEM(DOC_TYPE_CODE varchar(2),CALL_NO varchar(10),IN_YEAR int)
insert into ITEM select '01','A00001',2008
insert into ITEM select '01','A00001',2008
insert into ITEM select '01','A00001',2009
insert into ITEM select '01','A00001',2009
insert into ITEM select '01','A00001',2009
insert into ITEM select '01','A00002',2008
insert into ITEM select '01','A00002',2008
insert into ITEM select '01','A00002',2008
insert into ITEM select '01','A00002',2009
insert into ITEM select '01','A00002',2009
insert into ITEM select '01','A00002',2009
insert into ITEM select '01','A00002',2009
insert into ITEM select '01','A00003',2008
insert into ITEM select '01','A00003',2009
insert into ITEM select '01','A00003',2009
insert into ITEM select '01','A00004',2009
insert into ITEM select '01','B00001',2008
insert into ITEM select '01','B00001',2008
insert into ITEM select '01','B00001',2009
insert into ITEM select '01','B00001',2009
insert into ITEM select '01','B00002',2007
insert into ITEM select '01','B00002',2009
insert into ITEM select '01','C00005',2007
insert into ITEM select '01','C00005',2008
insert into ITEM select '01','C00005',2009
insert into ITEM select '02','A00001',2008
insert into ITEM select '02','A00001',2009
insert into ITEM select '02','A00001',2009
insert into ITEM select '02','A00002',2008
insert into ITEM select '02','A00002',2008
insert into ITEM select '02','A00002',2009
insert into ITEM select '02','A00003',2008
insert into ITEM select '02','A00003',2009
insert into ITEM select '02','B00001',2008
insert into ITEM select '02','B00001',2008
insert into ITEM select '02','B00001',2009
insert into ITEM select '02','B00002',2009
insert into ITEM select '02','C00005',2008
insert into ITEM select '02','C00005',2009
go
create proc pr_100
@lendDate1 varchar(4),
@lendDate2 varchar(4)
as
set nocount on
select * from (select DOC_TYPE_CODE,LEFT(CALL_NO,1) call_no,in_year from ITEM where IN_YEAR between @lendDate1 and @lendDate2) a pivot (count(in_year) for DOC_TYPE_CODE in ([01],[02])) b
set nocount off
go
exec pr_100 '2009','2009'
exec pr_100 '2008','2009'
1 行受影响)
(1 行受影响)
call_no 01 02
------- ----------- -----------
A 10 4
B 3 2
C 1 1
call_no 01 02
------- ----------- -----------
A 16 8
B 5 4
C 2 2
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(DOC_TYPE_CODE)+'=sum(case when DOC_TYPE_CODE='''+DOC_TYPE_CODE+''' then 1 else 0 end),['+
DOC_TYPE_CODE+'总数]=count(distinct case when DOC_TYPE_CODE='''+DOC_TYPE_CODE+''' then 1 else 0 end)'
from item group by DOC_TYPE_CODE
exec('select CALL_NO=left(CALL_NO,1) '+@s+' from item group by left(CALL_NO,1)')
/*
CALL_NO 01 01总数 02 02总数
------- ----------- ----------- ----------- -----------
A 16 2 8 2
B 6 2 4 2
C 3 2 2 2
*/
--自己加条件
select kind=left(call_no,1),[01]=sum(case when DOC_TYPE_CODE ='01' then 1 else 0 end),
[02]=sum(case when DOC_TYPE_CODE ='02' then 1 else 0 end),
[01种数]= count(distinct case when DOC_TYPE_CODE ='01' then call_no end) ,
[02种数]= count(distinct case when DOC_TYPE_CODE ='02' then call_no end)
from (select * from item where IN_YEAR='2009')K
group by left(call_no,1)
kind 01 02 01种数 02种数
A 10 4 4 3
B 3 2 2 2
C 1 1 1 1