SQL在一行中选择第一个值，然后将该值插入另一列

otasi 发表于 Dev

父亲

我对sql不太好，所以不确定是否可行。甚至在Excel中？我试图选择第一个值，并忽略Product_ID中的重复项，然后将该行的第一个值添加到“标题”列。

另请注意，我的产品清单超过25,000多种产品。

因此，请采取以下措施：

+---------------+------------+-------+-------------+-------+
| Product_Count | Product_ID | Title | _Color_Name | _Size |
+---------------+------------+-------+-------------+-------+
| 2             | 14589      |       | Black       | 00    |
| 3             | 14589      |       | Black       | 0     |
| 4             | 14589      |       | Black       | 2     |
| 5             | 14589      |       | Black       | 4     |
| 6             | 14589      |       | Black       | 6     |
| 11            | 14589      |       | Dark Coral  | 00    |
| 12            | 14589      |       | Dark Coral  | 0     |
| 13            | 14589      |       | Dark Coral  | 2     |
| 14            | 14589      |       | Dark Coral  | 4     |
| 15            | 14589      |       | Dark Coral  | 6     |
| 129           | 15027      |       | Aqua        | 00    |
| 130           | 15027      |       | Aqua        | 0     |
| 131           | 15027      |       | Aqua        | 2     |
| 132           | 15027      |       | Aqua        | 4     |
| 133           | 15027      |       | Aqua        | 6     |
| 138           | 15027      |       | Black       | 00    |
| 139           | 15027      |       | Black       | 0     |
| 140           | 15027      |       | Black       | 2     |
| 141           | 15027      |       | Black       | 4     |
| 142           | 15027      |       | Black       | 6     |
+---------------+------------+-------+-------------+-------+

并将其转换为：

+---------------+------------+-------+-------------+-------+
| Product_Count | Product_ID | Title | _Color_Name | _Size |
+---------------+------------+-------+-------------+-------+
| 2             | 14589      | 14589 | Black       | 00    |
| 3             | 14589      |       | Black       | 0     |
| 4             | 14589      |       | Black       | 2     |
| 5             | 14589      |       | Black       | 4     |
| 6             | 14589      |       | Black       | 6     |
| 11            | 14589      |       | Dark Coral  | 00    |
| 12            | 14589      |       | Dark Coral  | 0     |
| 13            | 14589      |       | Dark Coral  | 2     |
| 14            | 14589      |       | Dark Coral  | 4     |
| 15            | 14589      |       | Dark Coral  | 6     |
| 129           | 15027      | 15027 | Aqua        | 00    |
| 130           | 15027      |       | Aqua        | 0     |
| 131           | 15027      |       | Aqua        | 2     |
| 132           | 15027      |       | Aqua        | 4     |
| 133           | 15027      |       | Aqua        | 6     |
| 138           | 15027      |       | Black       | 00    |
| 139           | 15027      |       | Black       | 0     |
| 140           | 15027      |       | Black       | 2     |
| 141           | 15027      |       | Black       | 4     |
| 142           | 15027      |       | Black       | 6     |
+---------------+------------+-------+-------------+-------+

斯图尔特

您可以使用PARTITION来窗口化ProductIds，然后使用以下命令标识每个分区的第一行ROW_NUMBER()：

SELECT 
  ProductID, 
  Product_Count, 
  CASE WHEN rn = 1 THEN ProductID else null END AS Title, 
  Color_Name, 
  Size
FROM
(
    SELECT ProductID, Product_Count, Color_Name, Size,
    ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Product_Count) AS rn
    FROM product_stock
) AS X;

SqlFiddle在这里

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。