我可以将具有多个值的记录分组吗 [英] Can I group record with multiple value

问题描述

我正在尝试按照下面的语句按"OPC"的第一列对记录进行分组，但对我而言这没有用.

商品OPC代码1代码2值说明

1 8521 M1I1 8518 712 96892733235
2 8521 M1I1 8518 58 96899130633
3 8521 M1I1 8518 202 97022222222
1 8520 M2I1 8514 900 96897015984
1 8519 DIN1 9174 653 99990740
2 8519 DIN1 8629 120 736329982
1 8518 RIN1 9329 3600 9995957175

结果应为
项目OPC代码1代码2值Desc

1 8521 M1I1 8518 712 96892733235
1 8520 M2I1 8514 900 96897015984
1 8519 DIN1 9174 653 99990740
1 8518 RIN1 9329 3600 9995957175

我的选择声明

Hi,

I am trying to work on the statement below to group the record by the fist column which is "OPC" it didn''t work on my side.

Item OPC Code1 Code2 Value Desc

1 8521 M1I1 8518 712 96892733235
2 8521 M1I1 8518 58 96899130633
3 8521 M1I1 8518 202 97022222222
1 8520 M2I1 8514 900 96897015984
1 8519 DIN1 9174 653 99990740
2 8519 DIN1 8629 120 736329982
1 8518 RIN1 9329 3600 9995957175

Result should be
Item OPC Code1 Code2 Value Desc

1 8521 M1I1 8518 712 96892733235
1 8520 M2I1 8514 900 96897015984
1 8519 DIN1 9174 653 99990740
1 8518 RIN1 9329 3600 9995957175

My Select Statement

Select Item,OPC,Code1,Code2,Value,Desc
from #tmp2 c
where c.opc =  (select max(d.opc) from #tmp2  d
                       where c.opc = d.opc and c.items = d.items
                         and  c.code1 = d.code1 and c.code2 = d.code2
                         and c.value = d.value and c.desc = d.desc)

我的SQL不起作用.

预先感谢

My SQL doesn''t work.

Thank in advance

推荐答案

您可以使用group by 来 group 在多列上.

You can use group by to group on more than one column.

Select Item,OPC,Code1,Code2,Value,Desc
from #tmp2 cwhere c.opc =  (select max(d.opc) from #tmp2  d                       where c.opc = d.opc and c.items = d.items                         and  c.code1 = d.code1 and c.code2 = d.code2                         and c.value = d.value and c.desc = d.desc)Group by Item,OPC,Code1,Code2,Value,Desc

That is my solution I just forgot to include it.

同样的错误答案，我无法获得上面列出的分组.

The same wrong answer, I couldn''t get the grouping that I was listed above.

Select Item,OPC,Code1,Code2,Value,Desc
from #tmp2 c
where c.item =  (select max(d.item) from #tmp2  d                       where c.opc = d.opc)

有用.谢谢

新结果，用户希望获得最高的商品.

IT works. Thanks

New Result, The user wants to get the highest Items.

3 8521 M1I1 8518 202 97022222222
1 8520 M2I1 8514 900 96897015984
2 8519 DIN1 8629 120 736329982
1 8518 RIN1 9329 3600 9995957175

这篇关于我可以将具有多个值的记录分组吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！