mysql的sql查询写法问题

mysql sql 写法

怎么使结果集增加一列，是sum（countName）的值

参考下述代码即可

select number,countName, id,sum(a.countName) as cn from (
SELECT
number,
count( NAME ) AS countName,sum( id ) as id
FROM
test
WHERE
adress ='1
GROUP BY number
) a

SELECT number, 
       count(NAME) AS countName, 
       sum(id) as id, 
       sum(count(NAME)) as sumCountName
FROM test
WHERE adress = '1'
GROUP BY number

该回答引用ChatGPT
请参考下面的方法

SELECT number, 
       count(NAME) AS countName, 
       sum(id) as id, 
       sum(countName) as sumCountName
FROM test
WHERE adress = '1'
GROUP BY number

sql查出来会是多条数据，但是那个sum只会是一个字段值

建议查询结果后，再包一层就可以了

1、如果是MySQL5.7，可以使用with rollup来统计，不过这个是增加一个统计行，而不是列，硬要增加一列，需要用到8.0新增的窗口函数或@变量

SELECT COALESCE( number, '合计' ) , count( name ) AS countName, sum( id ) AS id
  FROM test
 WHERE adress = '1'
 GROUP BY number
  WITH ROLLUP;

题主快来看一下

with a as (select number,count(name) as countname,sum(id) as id from test where adress='1' group by number),
b as (select sum(countname) sumcountname from a)
select * from a,b;

不知道你这个问题是否已经解决, 如果还没有解决的话:

• 建议你看下这篇博客👉 ：Mysql 输入SQL后无响应解决办法

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^