时间戳

首页 > 解决方案 > 按 LEFT(code, 4) 分组并选择其中一个描述为 1

sql - 按 LEFT(code, 4) 分组并选择其中一个描述为 1

问题描述

我有一个查询为我提供了这个当前输出：

数据

0001    Californian Whole Almonds (Own Label)       0.50
0001    Californian Whole Almonds                   1.00
0001    Californian Whole Almonds (Own Label)       1.00
0001    Californian Whole Almonds (Own Label)       3.00
0001    Californian Whole Almonds                   6.00
0001    Californian Whole Almonds (Own Label)       6.00
0001    Californian Whole Almonds                   10.00
0001    Californian Whole Almonds (Own Label)       10.00
0001    Californian Whole Almonds (HP)              1.92
0001    Californian Whole Almonds (HP - Own Label)  1.92
0001    Californian Whole Almonds (HP - Own Label)  1.50
0002    Flaked Almonds (Own Label)                  0.50
0002    Flaked Almonds                              1.00
0002    Flaked Almonds (Own Label)                  1.00
0002    Flaked Almonds (Own Label)                  3.00
0002    Flaked Almonds                              6.00

询问

SELECT LEFT(code, 4), description, w.WeightInKilograms AS WeightKG FROM Product p
JOIN TechnicalDatabase.dbo.Weights w on p.searchRef1 = w.Weight

问题

我怎样才能group一起LEFT(p.code, 4)，选择一个相同的描述，LEFT(p.code, 4)最后总结重量？

我试过的

电流输出

0001    Californian Whole Almonds                   17.00
0001    Californian Whole Almonds (HP - Own Label)  3.42
0001    Californian Whole Almonds (HP)              2.88
0001    Californian Whole Almonds (Own Label)       20.50

查询已尝试

SELECT LEFT(code, 4), description, SUM(w.WeightInKilograms) AS WeightKG FROM Product p
JOIN TechnicalDatabase.dbo.Weights w on p.searchRef1 = w.Weight GROUP BY LEFT(code, 4), description;

它应该是什么：

由此

0001    Californian Whole Almonds (Own Label)       0.50
0001    Californian Whole Almonds                   1.00
0001    Californian Whole Almonds (Own Label)       1.00
0001    Californian Whole Almonds (Own Label)       3.00
0001    Californian Whole Almonds                   6.00
0001    Californian Whole Almonds (Own Label)       6.00
0001    Californian Whole Almonds                   10.00
0001    Californian Whole Almonds (Own Label)       10.00
0001    Californian Whole Almonds (HP)              1.92
0001    Californian Whole Almonds (HP - Own Label)  1.92
0001    Californian Whole Almonds (HP - Own Label)  1.50
0002    Flaked Almonds (Own Label)                  0.50
0002    Flaked Almonds                              1.00
0002    Flaked Almonds (Own Label)                  1.00
0002    Flaked Almonds (Own Label)                  3.00
0002    Flaked Almonds                              6.00

对此

0001 Californian Whole Almonds (Own Label)  42.84
0002 Flaked Almonds (Own Label)             11.5

解释

我具体分组，LEFT(p.code, 4)我需要选择任何描述为 1 并显示权重的总和。只是不知道该怎么做。

标签： sqlsql-server

解决方案

选择与 LEFT(p.code, 4) 相同的描述之一，最后总结权重？

您想要其中一个描述，然后只需MAX/MIN在 Description 列上使用并将其从GROUP BY以下内容中删除：

SELECT LEFT(code, 4), 
       max(description) as description, 
       SUM(w.WeightInKilograms) AS WeightKG 
  FROM Product p
  JOIN TechnicalDatabase.dbo.Weights w on p.searchRef1 = w.Weight 
GROUP BY LEFT(code, 4);

推荐阅读