MySQL-如何使用Group By获取值

Question

+-----------------+--------+-------+
| 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...............................................请任何人给我建议。

Answer 1

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<>小提琴

Answer 2

如果您有所有日期的所有水果的价格，那么您可以使用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

请参阅演示。