我有此查询根据某些条件选择一些字段:
SELECT [Qty].[plant_no]
,[Qty].[ord_no]
,[Qty].[form_no]
,[Qty].[form_var]
,[Qty].[form_part]
,[Qty].[res_no]
,isnull([RFORMPARTS].[cost_center],'_') as [RFORMPARTS_cost_center]
,[BARTICLES].[wght_net] AS [Unit_kg]
,CASE
WHEN [Qty].[qty_unit] IN ('KG','KG-C') THEN [Qty].[fb_qty]
WHEN [Qty].[qty_unit] = 'NR' THEN [Qty].[fb_qty]*[BARTICLES].[wght_net]
END AS [Qty_KG]
FROM [dbo].[E00_PRD_003] AS [Qty]
LEFT JOIN [dbo].[RFORMPARTS] as [RFORMPARTS] ON [RFORMPARTS].[form_no] = [Qty].[form_no] AND [RFORMPARTS].[form_var] = [Qty].[form_var] and [RFORMPARTS].[form_part] = [qty].form_part
LEFT JOIN [dbo].[BARTICLES] AS [BARTICLES] ON [BARTICLES].[art_no] = [Qty].[form_no]
WHERE CONVERT(VARCHAR(10),[Qty].[date_start],112) >= 20180101 AND [Qty].[in_out] = '02' AND LEFT([Qty].[res_no],2) <> 'SL'
我想获得每个[Qty]。[ord_no]和[Qty]。[form_no]的最大Qty.form_part,同时保留所有选定字段。有可能和一群人一起做吗?如果是,我该怎么办?如果没有,还有其他方法吗?
如果我了解您的要求,一种选择是创建一个子查询,该子查询返回max
每个ord_no
&form_no
对的,您将使用Group By
它,但仅在子查询中,然后max
form_part
在外部查询结果中使用子查询的值,如果您只需要具有每个ord_no
&form_no
对最大值的行,则可以INNER JOIN
向子查询的条件添加一个条件,并添加form_part
值的联接,以便它也联接在最大值上,这将过滤掉所有多余的条件具有相同ord_no
&form_no
组合且小于最大值的行form_part
:
SELECT [Qty].[plant_no]
,[Qty].[ord_no]
,[Qty].[form_no]
,[Qty].[form_var]
--,[Qty].[form_part] --Replaced with the subquery max
,[maxVals].[form_part] --Max values returned by subquery
,[Qty].[res_no]
,isnull([RFORMPARTS].[cost_center],'_') as [RFORMPARTS_cost_center]
,[BARTICLES].[wght_net] AS [Unit_kg]
,CASE
WHEN [Qty].[qty_unit] IN ('KG','KG-C') THEN [Qty].[fb_qty]
WHEN [Qty].[qty_unit] = 'NR' THEN [Qty].[fb_qty]*[BARTICLES].[wght_net]
END AS [Qty_KG]
FROM [dbo].[E00_PRD_003] AS [Qty]
INNER JOIN --INNER JOIN the max values on the Qty table
(
SELECT [ord_no], [form_no], MAX([form_part]) AS [form_part]
FROM [dbo].[E00_PRD_003]
GROUP BY [ord_no], [form_no]
) AS maxVals
ON [maxVals].[ord_no] = [Qty].[ord_no] AND [maxVals].[form_no] = [Qty].[form_no]
AND [maxVals].[form_part] = [Qty].[form_part]
LEFT JOIN [dbo].[RFORMPARTS] as [RFORMPARTS] ON [RFORMPARTS].[form_no] = [Qty].[form_no] AND [RFORMPARTS].[form_var] = [Qty].[form_var] and [RFORMPARTS].[form_part] = [qty].form_part
LEFT JOIN [dbo].[BARTICLES] AS [BARTICLES] ON [BARTICLES].[art_no] = [Qty].[form_no]
WHERE CONVERT(VARCHAR(10),[Qty].[date_start],112) >= 20180101 AND [Qty].[in_out] = '02' AND LEFT([Qty].[res_no],2) <> 'SL'
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句