主页 / 问题 / 1609968
Accepted
user698894
Asked:2025-04-06 18:46:29 +0000 UTC2025-04-06 18:46:29 +0000 UTC

分组和计数

  • 772

有一张表 TableName：

id   a        b       brand
1   001      zyb      apl
2   002      yan      apl
3   002      pit      ggl
4   101      zyb      ggl
5   003      zyb      ggl
6   102      yan      apl
7   002      pit      msf

结果：

 a        b       brand   c
001      zyb      apl     2
002      yan      apl     2
002      pit      ggl     2
003      zyb      ggl     2
002      pit      msf     1 

SELECT
    a,
    b,
    brand,
    COUNT(brand) AS c
FROM
    TableName
WHERE
    a REGEXP '^00'
ORDER BY
    c ASC

我收到此错误：

这与 sql_mode=only_full_group_by 不兼容

sql

2 个回答

  1. SELECT seller, location, brand, COUNT(brand) as c 
FROM TablName 
WHERE seller REGEXP '^00' 
GROUP BY seller, location, brand 
ORDER BY c ASC;

    我补充道GROUP BY seller, location, brand。这样就可以根据指定的字段对数据进行聚合，满足模式的要求ONLY_FULL_GROUP_BY.COUNT(brand)。它现在可以COUNT(brand)正常工作，因为所有其他字段SELECT现在都在GROUP BY。

    查询现在应该可以正常运行。

    • 2
  2. Best Answer

    对于支持窗口函数的 DBMS，使用

    WITH TableName (id, a, b, brand) AS (
  SELECT 1,   '001',      'zyb',      'apl' UNION ALL
  SELECT 2,   '002',      'yan',      'apl' UNION ALL
  SELECT 3,   '002',      'pit',      'ggl' UNION ALL
  SELECT 4,   '101',      'zyb',      'ggl' UNION ALL
  SELECT 5,   '003',      'zyb',      'ggl' UNION ALL
  SELECT 6,   '102',      'yan',      'apl' UNION ALL
  SELECT 7,   '002',      'pit',      'msf'
  )
SELECT
    a,
    b,
    brand,
    COUNT(brand) OVER (PARTITION BY brand) AS c
FROM
    TableName
WHERE
    a REGEXP '^00'
ORDER BY
    c ASC
    一个 b 品牌 c
    002 坑 无国界医生组织 1
    001 齐布 杀伤人员地雷 2
    002 严 杀伤人员地雷 2
    002 坑 gggl 2
    003 齐布 gggl 2

    小提琴

    对于 MySQL 5.5 版本，您可以使用

    SELECT TableName.a, TableName.b, brand, counts.c
FROM TableName
NATURAL JOIN (
  SELECT brand, COUNT(brand) AS c
  FROM TableName
  WHERE a REGEXP '^00'
  GROUP BY brand
  ) AS counts
WHERE a REGEXP '^00';

    或者

    SELECT a, b, brand, 
  ( SELECT COUNT(brand)
    FROM TableName t2
    WHERE a REGEXP '^00'
      AND brand = t1.brand
    ) AS c
FROM TableName t1
WHERE a REGEXP '^00';

    小提琴

    • 0

相关问题