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

4zcjmb1e  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(263)

我正在努力正确地查询看似简单的内容,但我能想到的唯一解决方案是无法保存为视图,因为它包含一个变量。我要做的是只选择每个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
yzxexxkh

yzxexxkh1#

似乎“第一”排在一组 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

相关问题