我正在努力正确地查询看似简单的内容,但我能想到的唯一解决方案是无法保存为视图,因为它包含一个变量。我要做的是只选择每个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
1条答案
按热度按时间yzxexxkh1#
似乎“第一”排在一组
idcustomer
以及StartDate
定义为具有最低total
价值观。我们可以确定最低的
total
派生表中指定组的值。我们会的JOIN
使用最小的总值将结果集返回主表,idcustomer
以及StartDate
. 这将为我们提供一个完整的行,与一个组中的最低总值相对应。db小提琴演示
查询
结果
现在,您可以使用这个查询创建视图(没有用户定义的变量)。