MySQL选择具有多个列条目的行

fdx2calv 于 5个月前发布在 Mysql

关注(0)|答案(2)|浏览(44)

我有一个非规范化的表，记录，列：
biz_address、customer_name、dept_ID、order_ID、order_amount。
biz_address和customer_name具有重复的列条目。
如何获取每个地址超过5个订单的地址的所有订单列表？
我想要的是这样的结果集...

+-----------------+-----------+------------+----------+-----------+
| biz_address | customer_name | dept_ID  | order_ID | order_amount |
+-----------------+-----------+------------+----------+-----------+
| 100   CLAY DR   | REAL      | 1     | 54    |     60 |
| 100   CLAY DR   | REAL      | 2     | 55    |      4 |
| 100   CLAY DR   | REAL      | 3     | 56    |      5 |
...
| 11    MOORE ST  | ABCO      | 10    | 67       |     7  |
| 11    MOORE ST  | ABCO      | 11    | 78       |     2  |
| 11    MOORE ST  | ABCO      | 12    | 79       |     4  |
| 11    MOORE ST  | ABCO      | 13    | 80       |     8  |

字符串
我知道怎么计数：

SELECT biz_address, customer_name, order_ID, COUNT(*) 
FROM records
GROUP BY customer_name
HAVING COUNT(*) > 5;

型
返回这个...

+----------------+------------+------------+----------+
| biz_address   | customer_name | order_ID   | COUNT(*) |
+--------+------------+---------------+----------+
| 100   CLAY DR    | REAL    | 54      |        6 |
| 11    MOORE ST   | ABC     | 67      |        8 |

型

mysql

来源：https://stackoverflow.com/questions/77526090/mysql-select-rows-with-multiple-column-entries

2条答案

按热度按时间

0vvn1miw1#

使用子查询联接原始表，该子查询获取具有指定重复数的所有地址。

WITH dups AS (
    SELECT biz_address
    FROM records
    GROUP BY biz_address
    HAVING COUNT(*) > 5
)
SELECT r.*
FROM records AS r
JOIN dups ON r.biz_address = dups.biz_address

字符串

赞(0）回复(0）举报 5个月前

vfh0ocws2#

这里有一个替代解决方案，使用COUNT()作为窗口函数，而不是聚合函数。

SELECT *
FROM (
  SELECT *, COUNT(*) OVER (PARTITION BY biz_address) AS count
  FROM records
) AS t
WHERE count >= 4;

字符串
窗口函数需要MySQL 8.0或更高版本。

赞(0）回复(0）举报 5个月前

我来回答

MySQL选择具有多个列条目的行

2条答案

相关问题

热门标签

最新问答