我想在子查询中使用分组依据 [英] i want to use group by in sub query
问题描述
创建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]
后的字母数
但此代码无法分类我的数据的问题取决于接收方城市
注意:当我选择发件人所在的城市和到达信件的月份时，我必须在同一报告中找到所有从该发件人所在的城市接收信件的城市，以及信件的数量
<我认为类似的方法会起作用，您不需要传递最后一个值，否则您也可以使Sender where子句与Receiver相同，并且如果您只想按Receiver进行过滤，则可以发送null.另外，由于您可能不需要所有这些嵌套的选择并且正在计数，因此我修剪了您的代码.
创建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
[发件人城市]分组，
[接收方城市]，
[进口年份]，
[导入月份]
非常感谢它的运行正常
没有最后一个变量
:rose:
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
I think something like this would work, you don''t need to pass the last value otherwise you could also make the Sender where clause same as the Receiver and send null if you only want to filter by the Receiver. Plus I trimmed your code since you probably don''t need all those nested selects and are counting.
create proc select_report5
@y int,@z nvarchar(12),@x nvarchar(50), @u nvarchar(50) default null
as
select [Import year],[import month],[Sender City],[Receiver City],
sum( case [Acctual Time] when ''d+1'' then 1 else 0 end ) as [d+1],
sum( case [Acctual Time] when ''d+2'' then 1 else 0 end ) as [d+2],
sum( case [Acctual Time] when ''d+3'' then 1 else 0 end ) as [d+3],
sum( case [Acctual Time] when ''d+4'' then 1 else 0 end ) as [d+4],
sum( case [Acctual Time] when ''d+5'' then 1 else 0 end ) as [d+5],
sum( case [Acctual Time] when ''d+6'' then 1 else 0 end ) as [d+6],
sum( case [Acctual Time] when ''>d+6'' then 1 else 0 end ) as [>d+6]
from report5
where [Sender City]=@x
and [Receiver City] = isnull(@u, [Receiver City])
and [Import year]=@y
and [Import month]=@z
group by [Sender City],
[Receiver City],
[Import year],
[import month]
thanks alot it''s run ok
without the last variable
:rose:
这篇关于我想在子查询中使用分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
