sql server—交叉引用同一表的sql查询

p5cysglq  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(429)

下面有一个查询,我使用左外连接交叉引用同一个表。基本上我想交叉引用最小\u大小\u代码字段来反映最小\u实际\u大小和最大\u大小\u代码字段来反映最大\u实际\u大小。然而,我没有得到正确的答案,我正在寻找。
下面是我基于sql查询的答案

理想答案如下

下面是我的代码

select A.week,A.style,A.Color, A.location, A.Actual_size, A.size_code, A.Min_size_code, A.Max_size_code,
min(B.Actual_size) over(partition by A.week,A.style,A.Color,A.location) Min_Actual_Size,
max(B.Actual_size) over(partition by A.week,A.style,A.Color,A.location) Max_Actual_Size

from
(
select week, style, Color, location, Actual_size, size_code,
min(size_code)over(partition by week,style,Color,location) min_size_code,
max(size_code)over(partition by week,style,Color,location) max_size_code

from TestSizeTable where Style = 'AB123' and color = 'WY4567'
and week = '202002'  and location in ( '111')
) A
Left outer Join 
TestSizeTable B
on ( A.Style = B.Style and A.Color = B.Color and A.Week = B.Week
and A.Location = B.Location)
where (B.size_code = A.min_size_code or B.size_code = A.max_size_code)

任何帮助都太好了!

uujelgoq

uujelgoq1#

我认为问题在于列实际大小的数据类型。我怀疑是 varcahr 在这种情况下,14小于2。
你的代码在这里运行良好。
请检查更新的解决方案以获取 varchar 列(实际大小)。

vom3gejh

vom3gejh2#

这可能不是理想的方式,但我得到了一个答案,从下面修改代码。

Select A.week,A.style,A.Color, A.location, A.Actual_size, A.size_code, A.Min_size_code, A.Max_size_code,
Min_Actual_Size,Max_Actual_Size
from
(
select A.week,A.style,A.Color, A.location, A.Actual_size, A.size_code, A.Min_size_code, A.Max_size_code,
max(B.Actual_size) over(partition by A.week,A.style,A.Color,A.location) Max_Actual_Size,
(
select A.week,A.style,A.Color, A.location, A.Actual_size, A.size_code, A.Min_size_code, A.Max_size_code,
min(B.Actual_size) over(partition by A.week,A.style,A.Color,A.location) Min_Actual_Size,
from
(
select week, style, Color, location, Actual_size, size_code,
min(size_code)over(partition by week,style,Color,location) min_size_code,
max(size_code)over(partition by week,style,Color,location) max_size_code
from TestSizeTable where Style = 'AB123' and color = 'WY4567'
and week = '202002'  and location in ( '111')
) A
Left outer Join 
TestSizeTable B
on ( A.Style = B.Style and A.Color = B.Color and A.Week = B.Week
and A.Location = B.Location)
where B.size_code = A.min_size_code
) A
Left outer Join 
TestSizeTable B
on ( A.Style = B.Style and A.Color = B.Color and A.Week = B.Week
and A.Location = B.Location)
where B.size_code = A.max_size_code
) A

相关问题