mysql 组连接

发布于 2024-10-29 22:52:17 字数 356 浏览 2 评论 0原文

我怎样才能分组连接？这是我的查询

select t.date, group_concat(count(*)) from table1 t
group by t.date

，但它返回错误“无效使用组函数”

如果我使用这样的查询

select t.date, count(*) from table1 t
group by t.date

，那么它会返回以下输出，但我想 group_concat 这个输出

2011-01-01  100
2011-01-02  97
2011-01-03  105

原文

how can I group concat? here is my query

select t.date, group_concat(count(*)) from table1 t
group by t.date

but it returns error "Invalid use of group function"

If I use query like this

select t.date, count(*) from table1 t
group by t.date

then it returns following output but I want to group_concat this output

2011-01-01  100
2011-01-02  97
2011-01-03  105

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

醉南桥 2024-11-05 22:52:17

SELECT GROUP_CONCAT(`cnt` ORDER BY `date`) 
FROM (
    SELECT t.`date`, COUNT(*) AS `cnt`
    FROM `table1` t
    GROUP BY t.`date`
) d

SELECT GROUP_CONCAT(`cnt` ORDER BY `date`) 
FROM (
    SELECT t.`date`, COUNT(*) AS `cnt`
    FROM `table1` t
    GROUP BY t.`date`
) d

回复收藏 0 原文

聆听风音 2024-11-05 22:52:17

您想计算按日期分组的日期行数吗？

我使用此报表来计算特定月份中每个日期的发票项目数。

SELECT date(i.Date), count(*) cnt
FROM invoice i
WHERE MONTH(i.Date) = 3
GROUP BY date(i.Date)

这会将所有相同的日期分组。这是你的意思吗？

我将 GROUP_CONCAT 用于返回多行的子查询。

* 编辑 *
OUPS，看到我的建议与您已经尝试过的相同。那我不明白你在找什么。您能举一个期望结果的例子吗？

Do you want to count the number of date rows group by date?

I use this statement to count number of invoice items per date in a specific month.

SELECT date(i.Date), count(*) cnt
FROM invoice i
WHERE MONTH(i.Date) = 3
GROUP BY date(i.Date)

This will group all dates that are the same. Is this what you meant?

I use GROUP_CONCAT for subqueries returning more than one row.

* EDIT *
OUPS, saw that my suggestion was the same as your already tried. Then I don't understand what you are looking for. Can you please show an example of desired result?

回复收藏 0 原文

情独悲 2024-11-05 22:52:17

您想要这样的东西：

SELECT GROUP_CONCAT(n) groupN
FROM (SELECT COUNT(*) n
      FROM table1
      GROUP BY date) tmp

但这不是 RDBMS 的角色！修饰是你的应用程序语言（PHP、Python、Ruby，等等）的作用，你的查询应该只选择你的数据，期间。因此，GROUP_CONCAT 不是这种情况下的解决方案。

You want something like this :

SELECT GROUP_CONCAT(n) groupN
FROM (SELECT COUNT(*) n
      FROM table1
      GROUP BY date) tmp

But that's not the role of the RDBMS! Cosmetic is the role of your application language (PHP, Python, Ruby, whatever), your query should only select your data, period. Therefore, GROUP_CONCAT is not the solution in this case.

回复收藏 0 原文

~没有更多了~