mysql查询，只给出每个组的第一个匹配项，可以保存为一个视图

我正在努力正确地查询看似简单的内容，但我能想到的唯一解决方案是无法保存为视图，因为它包含一个变量。我要做的是只选择每个startdate/idcustomerl组合的第一个匹配项。当我尝试使用groupbystartdate时，idcustomerl并没有给出第一个结果（我尝试了不同的排序方式，但也没有成功）。
下面是我的工作表。
startdate=每个客户的每个startdate和startdate
total=startdatec+startdater与startdate的日差（我这样做是为了创建确定使用哪个startdate所需的订单）
table

+------------+------------+------------+------------+-------+
| 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

似乎“第一”排在一组 idcustomer 以及 StartDate 定义为具有最低 total 价值观。
我们可以确定最低的 total 派生表中指定组的值。我们会的 JOIN 使用最小的总值将结果集返回主表， idcustomer 以及 StartDate . 这将为我们提供一个完整的行，与一个组中的最低总值相对应。
db小提琴演示
查询

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

mysql查询，只给出每个组的第一个匹配项，可以保存为一个视图

1条答案

相关问题

热门标签

最新问答