当前位置：文江博客话题详情

MySql，如何在正或负的选择查询中进行分组？

发布于 2024-10-14 02:22:39 字数 45 浏览 5 评论 0原文

我想按所有消极的价值观和所有积极的价值观进行分组，有什么想法如何做到这一点？

原文

分享到QQ

分享到微博

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

发布评论

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

菊凝晚露 2024-10-21 02:22:39

GROUP BY SIGN(field) 应该可以。

回复收藏 0 原文

段念尘 2024-10-21 02:22:39

SELECT SUM(CASE WHEN SomeColumn < 0 THEN 1 ELSE 0 END) AS negative_values,
       SUM(CASE WHEN SomeColumn >=0 THEN 1 ELSE 0 END) AS non_negative_values
    FROM YourTable

SELECT SUM(CASE WHEN SomeColumn < 0 THEN 1 ELSE 0 END) AS negative_values,
       SUM(CASE WHEN SomeColumn >=0 THEN 1 ELSE 0 END) AS non_negative_values
    FROM YourTable

回复收藏 0 原文

对不⑦ 2024-10-21 02:22:39

Stefan 的解决方案看起来是迄今为止最好的答案。如果您希望 0 与正数分组，您可以使用

GROUP BY `field` >= 0

Stefan's solution looks like the best answer so far. If you would prefer for 0 to be grouped in with positive numbers, you can use

GROUP BY `field` >= 0

回复收藏 0 原文

友欢 2024-10-21 02:22:39

尝试这样的事情：

select count(*), IF(foo >= 0, "pos", "neg") as sign  from test group by sign;

其中 foo 是具有正值或负值的列

编辑：如果您希望将零值与正值和负值区别对待，Stefan 的解决方案会更优雅、更好。

Try something like this:

select count(*), IF(foo >= 0, "pos", "neg") as sign  from test group by sign;

where foo is the column with the positive or negative values

EDIT: Stefan's solution is more elegant and better if you want zero values treated differently from positive and negative ones.

回复收藏 0 原文

不知在何时 2024-10-21 02:22:39

我知道这个问题很久以前就被问过，但它可能会对将来的某人有所帮助。
注意：下面是在 SQL Server 中测试的，我相信同样的逻辑也适用于 MySql。

    declare @temp table(
    id int,
    amount decimal(18,2),
    rate decimal(18,2),
    isPositiveAmount bit,
    isPositiveRate bit
)

insert into @temp values(1, 100.1, 12.3, null, null)
insert into @temp values(1, -1, -1, null, null)
insert into @temp values(2, 100.1, 12.32, null, null)

update @temp set isPositiveAmount = iif(amount >= 0, 1, 0), isPositiveRate = iif(rate >= 0, 1, 0)

select id, sum(amount), avg(rate), isPositiveAmount, isPositiveRate from @temp
group by id, isPositiveAmount, isPositiveRate

I know the question was asked long ago, but it may help someone in the future.
NOTE: Below was tested in SQL server, I believe the same logic will apply in MySql.

    declare @temp table(
    id int,
    amount decimal(18,2),
    rate decimal(18,2),
    isPositiveAmount bit,
    isPositiveRate bit
)

insert into @temp values(1, 100.1, 12.3, null, null)
insert into @temp values(1, -1, -1, null, null)
insert into @temp values(2, 100.1, 12.32, null, null)

update @temp set isPositiveAmount = iif(amount >= 0, 1, 0), isPositiveRate = iif(rate >= 0, 1, 0)

select id, sum(amount), avg(rate), isPositiveAmount, isPositiveRate from @temp
group by id, isPositiveAmount, isPositiveRate

回复收藏 0 原文

~没有更多了~