问题描述
创建proc select_report5
@y int，@ z nvarchar(12)，@ x nvarchar(50)
如
从报告5中选择[进口年]，[进口月]，[发件人城市]，[收货人城市](选择计数([启动时间])
其中(([Acctual Time] =''d + 1'')和([Acctual Time]！=''d + 2'')和([Acctual Time]！=''d + 3'')和( [准确时间]！=''d + 4'')和([准确时间]！=''d + 5'')和([准确时间]！=''d + 6'')和([准确时间]！=''> d + 6''))和([发件人城市] = [接收者城市])和([[进口年份] = @ y)和([[进口月份] = @ z)和([发件人城市] = @ x))作为[d + 1]，(从report5
选择计数([启动时间])其中(([Acctual Time] =''d + 2'')和([Acctual Time]！=''d + 1'')和([Acctual Time]！=''d + 3'')和( [准确时间]！=''d + 4'')和([准确时间]！=''d + 5'')和([准确时间]！=''d + 6'')和([准确时间]！=''> d + 6''))和([发件人城市] = [接收者城市])和([[进口年份] = @ y)和([[进口月份] = @ z)和([发件人城市] = @ x))作为[d + 2]，(从report5
中选择count([Acctual Time])其中(([Acctual Time] =''d + 3'')和([Acctual Time]！=''d + 1'')和([Acctual Time]！=''d + 2'')和( [准确时间]！=''d + 4'')和([准确时间]！=''d + 5'')和([准确时间]！=''d + 6'')和([准确时间]！=''> d + 6''))和([发件人城市] = [接收者城市])和([[进口年份] = @ y)和([[进口月份] = @ z)和([发件人城市] = @ x))作为[d + 3]，(从report5
中选择count([Acctual Time])其中(([Acctual Time] =''d + 4'')和([Acctual Time]！=''d + 1'')和([Acctual Time]！=''d + 2'')和( [精确时间]！=''d + 3'')和([精确时间]！=''d + 5'')和([精确时间]！=''d + 6'')和([精确时间]！=''> d + 6''))和([发件人城市] = [接收者城市])和([[进口年份] = @ y)和([[进口月份] = @ z)和([发件人城市] = @ x))作为[d + 4]，(从report5
选择计数([启动时间])其中(([Acctual Time] =''d + 5'')和([Acctual Time]！=''d + 1'')和([Acctual Time]！=''d + 2'')和( [精确时间]！=''d + 3'')和([精确时间]！=''d + 4'')和([精确时间]！=''d + 6'')和([精确时间]！=''> d + 6''))和([发件人城市] = [接收者城市])和([[进口年份] = @ y)和([[进口月份] = @ z)和([发件人城市] = @ x))作为[d + 5]，(从report5
选择计数([启动时间])其中(([Acctual Time] =''d + 6'')和([Acctual Time]！=''d + 1'')和([Acctual Time]！=''d + 2'')和( [加速时间]！=''d + 3'')和([加速时间]！=''d + 4'')和([加速时间]！=''d + 5'')和([加速时间]！=''> d + 6''))和([发件人城市] = [接收者城市])和([[进口年份] = @ y)和([[进口月份] = @ z)和([发件人城市] = @ x))作为[d + 6]，(从report5
选择计数([启动时间])其中(([Acctual Time] =''> d + 6'')和([[Acctual Time]！=''d + 1'')和([Acctual Time]！=''d + 2'')和([Acctual Time]！=''d + 3'')和([Acctual Time]！=''d + 4'')和([Acctual Time]！=''d + 5'')和( [启动时间]！=''d + 6''))和([发件人城市] = [接收者城市])和([[进口年份] = @ y)和([[进口月份] = @ z)和([发件人城市] = @ x))作为[> d + 6]
来自report5
其中[发件人城市] = @ x和[导入年份] = @ y和[导入月份] = @ z
[发件人城市]，[收货人城市]，[进口年]，[进口月]分组
=========================================
此显示报告代码，显示一天[d + 1]之后，两天[d + 2]之后，...，六天以上[> d + 6]
后的字母数但此代码无法分类我的数据的问题取决于接收方城市
注意:当我选择发件人所在的城市和到达信件的月份时，我必须在同一报告中找到所有从该发件人所在的城市接收信件的城市，以及信件的数量
创建proc select_report5
@y int，@ z nvarchar(12)，@ x nvarchar(50)，@ u nvarchar(50)默认为null
如
选择[进口年]，[进口月]，[发件人城市]，[收货人城市]，
sum(case'Acctual Time'当'd + 1'时，然后1 else 0 end)为[d + 1]，
sum(case'Acctual Time'当'd + 2'则1 else 0 end)as [d + 2]，
sum(case'Acctual Time'当'd + 3'然后1 else 0 end)as [d + 3]，
sum(case'Acctual Time'当'd + 4'时，则1 else 0 end)为[d + 4]，
sum(case'Acctual Time'当'd + 5'时，则1 else 0 end)为[d + 5]，
sum(case'Acctual Time'当'd + 6'然后1 else 0 end)as [d + 6]，
sum(情况为启动时间"，当``> d + 6''则1个其他0结束)为[&d; d + 6]
来自report5
其中[发件人城市] = @ x
和[Receiver City] = isull(@u，[Receiver City])
和[导入年份] = @ y
和[导入月份] = @ z
[发件人城市]分组，
[接收方城市]，
[进口年份]，
[导入月份]
create proc select_report5
@y int,@z nvarchar(12),@x nvarchar(50)
as
select [Import year],[import month],[Sender City],[Receiver City],(select count([Acctual Time]) from report5
where (([Acctual Time]=''d+1'') and ([Acctual Time]!=''d+2'')and ([Acctual Time]!=''d+3'')and ([Acctual Time]!=''d+4'')and ([Acctual Time]!=''d+5'')and ([Acctual Time]!=''d+6'')and ([Acctual Time]!=''>d+6'')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+1],(select count([Acctual Time])from report5
where (([Acctual Time]=''d+2'') and ([Acctual Time]!=''d+1'')and ([Acctual Time]!=''d+3'')and ([Acctual Time]!=''d+4'')and ([Acctual Time]!=''d+5'')and ([Acctual Time]!=''d+6'')and ([Acctual Time]!=''>d+6'')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+2],(select count([Acctual Time])from report5
where (([Acctual Time]=''d+3'') and ([Acctual Time]!=''d+1'')and ([Acctual Time]!=''d+2'')and ([Acctual Time]!=''d+4'')and ([Acctual Time]!=''d+5'')and ([Acctual Time]!=''d+6'')and ([Acctual Time]!=''>d+6'')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+3],(select count([Acctual Time])from report5
where (([Acctual Time]=''d+4'') and ([Acctual Time]!=''d+1'')and ([Acctual Time]!=''d+2'')and ([Acctual Time]!=''d+3'')and ([Acctual Time]!=''d+5'')and ([Acctual Time]!=''d+6'')and ([Acctual Time]!=''>d+6'')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+4],(select count([Acctual Time])from report5
where (([Acctual Time]=''d+5'') and ([Acctual Time]!=''d+1'')and ([Acctual Time]!=''d+2'')and ([Acctual Time]!=''d+3'')and ([Acctual Time]!=''d+4'')and ([Acctual Time]!=''d+6'')and ([Acctual Time]!=''>d+6'')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+5],(select count([Acctual Time])from report5
where (([Acctual Time]=''d+6'') and ([Acctual Time]!=''d+1'')and ([Acctual Time]!=''d+2'')and ([Acctual Time]!=''d+3'')and ([Acctual Time]!=''d+4'')and ([Acctual Time]!=''d+5'')and ([Acctual Time]!=''>d+6'')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+6],(select count([Acctual Time])from report5
where (([Acctual Time]=''>d+6'') and ([Acctual Time]!=''d+1'')and ([Acctual Time]!=''d+2'')and ([Acctual Time]!=''d+3'')and ([Acctual Time]!=''d+4'')and ([Acctual Time]!=''d+5'')and ([Acctual Time]!=''d+6'')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [>d+6]
from report5
where [Sender City]=@x and [Import year]=@y and [Import month]=@z
group by [Sender City],[Receiver City],[Import year],[import month]
============================================
this code for show report which show the number of leetters after one day [d+1] ,after two day [d+2],...,after more than six day [>d+6]
but the problem this code can''t clssification my data depend on the receiver city
note : when i choose the sender city and the month of arriving letters i must find all city which recive letters from this sender city in the same report also the number of letters
这篇关于我想在子查询中使用分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！