+-----------------+--------+-------+
| Date(yyyy-mm-dd)| name | price |
+-----------------+--------+-------+
| 2021-03-01 | APPLE | 15 |
| 2021-03-01 | Orange | 10 |
| 2021-03-01 | Banana | 5 |
| 2021-03-02 | Apple | 16 |
| 2021-03-02 | Orange | 9 |
| 2021-03-02 | Banana | 4 |
| 2021-03-03 | Apple | 17 |
| 2021-03-03 | Orange | 11 |
| 2021-03-03 | Banana | 5 |
+-----------------+--------+-------+
**Fruit Value**
我想知道那些每天都涨价的水果的名字。所以产量应该是“苹果”，因为苹果的价格在3月1日是15，3月2日是16，3月3日是17...............................................请任何人给我建议。
如果您有所有日期的所有水果的价格，那么您可以使用group_concat():
SELECT name
FROM tablename
GROUP BY name
HAVING COUNT(*) = COUNT(DISTINCT price) -- all prices are different
AND GROUP_CONCAT(price ORDER BY Date) = GROUP_CONCAT(price ORDER BY price)
-- prices ordered by date are in the same order as prices ordered ascending by price
请参阅演示。
lag()是这里要使用的窗口函数。
内部查询检测每组中当前行和前一行之间的价格变化是否为增加。它只是设置了一个标志:1表示增加，0表示不增加。
外部查询只是将标志求和，并将求和值与每个name组中元素的总数进行比较。
SELECT name
FROM (
SELECT
*,
IF(
price > LAG(price, 1, 0) OVER (
PARTITION BY name
ORDER BY date
),
1,
0
) AS flag
FROM a
) r
GROUP BY name
HAVING SUM(flag) = COUNT(*)
dB<>小提琴
