时间戳

首页 > 解决方案 > MYSQL 查询，只给出可以保存为视图的每个组的第一次出现

mysql - MYSQL 查询，只给出可以保存为视图的每个组的第一次出现

问题描述

我正在努力正确查询看似简单的内容，但我能想出的唯一解决方案无法保存为视图，因为它包含一个变量。我要做的是只选择每个 StartDate/idcustomerL 组合的第一次出现。当我尝试使用 Group by StartDate, idcustomerL 它没有给我第一个结果（我尝试以不同的方式订购它，也没有运气）。
下面是我正在使用的表格。

StartDate = 每个客户的每个 StartDateC 和 StartDateR
总计 = StartDateC + StartDateR 与 StartDate 的天差（我这样做是为了创建确定要使用的 StartDate 所需的订单）

桌子

+------------+------------+------------+------------+-------+
| StartDate  | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20         | 2018-11-06 | 2018-10-01 | 36    |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20         | 2018-11-06 | 2018-01-01 | 309   |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20         | 2018-10-01 | 2018-10-01 | 0     |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20         | 2018-08-30 | 2018-10-01 | 32    |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20         | 2018-10-01 | 2018-01-01 | 273   |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20         | 2018-08-30 | 2018-01-01 | 241   |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62         | 2018-10-01 | 2018-10-01 | 0     |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62         | 2018-08-30 | 2018-10-01 | 32    |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62         | 2018-10-01 | 2018-01-01 | 273   |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62         | 2018-08-30 | 2018-01-01 | 241   |
+------------+------------+------------+------------+-------+

这是我需要得到的结果，我需要能够将查询保存为视图。

+------------+------------+------------+------------+-------+
| StartDate  | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20         | 2018-11-06 | 2018-10-01 | 36    |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20         | 2018-10-01 | 2018-10-01 | 0     |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20         | 2018-08-30 | 2018-01-01 | 241   |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62         | 2018-10-01 | 2018-10-01 | 0     |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62         | 2018-08-30 | 2018-01-01 | 241   |
+------------+------------+------------+------------+-------+

我可以通过以下方式获得我正在寻找的结果，但由于@count，我无法将其保存为视图，因此我需要找到另一个解决方案。

Select (@count := @count + 1) AS rowNumber, StartDate, idcustomerL, StartDateC, StartDateR, Test as totals 
from test 
CROSS JOIN (SELECT @count := 0) AS dummy 
Group by StartDate,idcustomerL order by idcustomerL, StartDate desc, totals

标签： mysql

解决方案

似乎一组idcustomer和中的“第一”行StartDate被定义为具有最低total值的行。

我们可以确定派生表total中指定组的最小值。然后，我们将使用最低总值将此结果集返回到主表，并且。这将为我们提供针对组中最低总值的完整行。JOINidcustomerStartDate

DB Fiddle 上的演示

询问

SELECT
  t.*
FROM 
  test AS t
JOIN 
(
  SELECT 
    StartDate, 
    idcustomer, 
    MIN(total) AS lowest_total 
  FROM test 
  GROUP BY 
    StartDate, 
    idcustomer
) AS dt 
  ON dt.StartDate = t.StartDate AND 
     dt.idcustomer = t.idcustomer AND 
     dt.lowest_total = t.total;

结果

| StartDate  | idcustomer | StartDateC | StartDateR | total |
| ---------- | ---------- | ---------- | ---------- | ----- |
| 2018-11-06 | 20         | 2018-11-06 | 2018-10-01 | 36    |
| 2018-10-01 | 20         | 2018-10-01 | 2018-10-01 | 0     |
| 2018-08-30 | 20         | 2018-08-30 | 2018-01-01 | 241   |
| 2018-10-01 | 62         | 2018-10-01 | 2018-10-01 | 0     |
| 2018-08-30 | 62         | 2018-08-30 | 2018-01-01 | 241   |

现在，您可以使用此查询创建视图（没有用户定义的变量）。

CREATE VIEW test_view AS 
SELECT
  t.*
FROM 
  test AS t
JOIN 
(
  SELECT 
    StartDate, 
    idcustomer, 
    MIN(total) AS lowest_total 
  FROM test 
  GROUP BY 
    StartDate, 
    idcustomer
) AS dt 
  ON dt.StartDate = t.StartDate AND 
     dt.idcustomer = t.idcustomer AND 
     dt.lowest_total = t.total

推荐阅读