英文:
SELECT CASE expression, COUNT(*) aggregate to include 0 when no rows match a case
问题
以下是您要翻译的内容：
我想要一个包含所有类别及其计数的结果集，即使一个或多个计数为0。
表格：`Numbers`
| id | number |
| ------ | ------ |
| 0 | 2 |
| 1 | -1 |
| 2 | 1 |
我尝试过的SQL查询如下：
SELECT sign,
COUNT(*) sign_count
FROM (SELECT CASE WHEN number < 0 THEN 'negative'
WHEN number > 0 THEN 'positive'
ELSE 'neither'
END AS sign
FROM Numbers) n
GROUP BY sign
我得到的结果如下：
| sign | sign_count |
|---|---|
| negative | 1 |
| positive | 2 |
我想要的结果如下：
```sql
| sign | sign_count |
| -------- | ---------- |
| negative | 1 |
| neither | 0 |
| positive | 2 |
我理解在Numbers表中没有任何值对应于neither，但是如何按照空类别分组呢？我还尝试了在id列上与Numbers进行自连接，以及创建了一个包含所有三个标志的单独的Signs表来执行外连接，但都没有得到我想要的结果。
表格：Signs
| sign |
|---|
| negative |
| neither |
| positive |
包括左连接的查询如下：
SELECT s.sign,
COUNT(*) sign_count
FROM (SELECT CASE WHEN number < 0 THEN 'negative'
WHEN number > 0 THEN 'positive'
ELSE 'neither'
END AS sign
FROM Numbers) n
LEFT JOIN Signs s ON n.sign = s.sign
GROUP BY s.sign
上述查询的结果如下：
| sign | sign_count |
|---|---|
| negative | 1 |
| positive | 2 |
如果在查询中将`LEFT`替换为`RIGHT`，结果如下：
```sql
| sign | sign_count |
| -------- | ---------- |
| negative | 1 |
| neither | 1 |
| positive | 2 |
这是错误的。
<details>
<summary>英文:</summary>
I want a result set that includes all categories and their counts, even if one or more counts are 0.
Table: `Numbers`
| id | number |
| ------ | ------ |
| 0 | 2 |
| 1 | -1 |
| 2 | 1 |
What I've tried:
SELECT sign,
COUNT(*) sign_count
FROM (SELECT CASE WHEN number < 0 THEN 'negative'
WHEN number > 0 THEN 'positive'
ELSE 'neither'
END AS sign
FROM Numbers) n
GROUP BY sign
What I get:
| sign | sign_count |
| -------- | ---------- |
| negative | 1 |
| positive | 2 |
What I want:
| sign | sign_count |
| -------- | ---------- |
| negative | 1 |
| neither | 0 |
| positive | 2 |
I understand that none of the values in `Numbers` corresponds to `neither`, but then how do I group by the null category? I have also searched around and tried self joins with `Numbers` on the id column, as well as created a separate `Signs` table consisting of all three signs to perform outer joins on, but neither gives what I'm looking for.
@ahmed
Table: `Signs`
| sign |
| -------- |
| negative |
| neither |
| positive |
Query including left join:
SELECT s.sign,
COUNT(*) sign_count
FROM (SELECT CASE WHEN number < 0 THEN 'negative'
WHEN number > 0 THEN 'positive'
ELSE 'neither'
END AS sign
FROM Numbers) n
LEFT JOIN Signs s ON n.sign = s.sign
GROUP BY s.sign
Result of above:
| sign | sign_count |
| -------- | ---------- |
| negative | 1 |
| positive | 2 |
If replace `LEFT` with `RIGHT` in the query, result:
| sign | sign_count |
| -------- | ---------- |
| negative | 1 |
| neither | 1 |
| positive | 2 |
which is wrong.
</details>
# 答案1
**得分**: 2
以下是翻译好的内容：
另一种执行此操作的方法是通过以下步骤：
- 用您的符号字符串值与您的表进行交叉连接
- 根据所需的符号有条件地计数您的值
```sql
SELECT sign,
COUNT(CASE WHEN sign = 'positive' AND number > 0 THEN 1
WHEN sign = 'negative' AND number < 0 THEN 1
WHEN sign = 'neither' AND number = 0 THEN 1 END) AS sign_count
FROM (VALUES('positive'),('negative'),('neither')) AS signs(sign)
CROSS JOIN Numbers
GROUP BY sign
输出：
| sign | sign_count |
|---|---|
| positive | 2 |
| negative | 1 |
| neither | 0 |
查看演示 这里。
英文:
Yet another way of doing this is by
- cross-joining your sign string values with your table
- counting conditionally your values according to the required sign
SELECT sign,
COUNT(CASE WHEN sign = 'positive' AND number > 0 THEN 1
WHEN sign = 'negative' AND number < 0 THEN 1
WHEN sign = 'neither' AND number = 0 THEN 1 END) AS sign_count
FROM (VALUES('positive'),('negative'),('neither')) AS signs(sign)
CROSS JOIN Numbers
GROUP BY sign
Output:
| sign | sign_count |
|---|---|
| positive | 2 |
| negative | 1 |
| neither | 0 |
Check the demo here.
答案2
得分: 1
以下是翻译好的部分：
这可能是一种选择：
- 为每个条件制作一个查询，并使用“UNION ALL”关键字将这些查询联合起来，以将查询结果混合在单个结果中。
查询：
SELECT 'negative' sign, COUNT(1) sign_count
FROM Numbers
WHERE number < 0
UNION ALL
SELECT 'neither' sign, COUNT(1) sign_count
FROM Numbers
WHERE number = 0
UNION ALL
SELECT 'positive' sign, COUNT(1) sign_count
FROM Numbers
WHERE number > 0;
结果：
| sign | sign_count |
|---|---|
| negative | 1 |
| neither | 0 |
| positive | 2 |
在 sqlfiddle 上查看其运行情况。
英文:
This might be an option:
- Make a query for each condition and union the queries using the "UNION ALL" keywork for mix the results of the queries in a single result.
Query:
SELECT 'negative' sign, COUNT(1) sign_count
FROM Numbers
WHERE number < 0
UNION ALL
SELECT 'neither' sign, COUNT(1) sign_count
FROM Numbers
WHERE number = 0
UNION ALL
SELECT 'positive' sign, COUNT(1) sign_count
FROM Numbers
WHERE number > 0;
Results:
| sign | sign_count |
|---|---|
| negative | 1 |
| neither | 0 |
| positive | 2 |
See it working on sqlfiddle.
答案3
得分: 1
选择 signs.sign,
SUM(CASE WHEN Numbers.number IS NULL THEN 0 ELSE 1 END)
FROM (SELECT 'positive' AS [sign]
UNION ALL
SELECT 'negative'
UNION ALL
SELECT 'neither') AS signs
LEFT OUTER JOIN Numbers
ON (signs.sign = CASE WHEN number < 0 THEN 'negative'
WHEN number > 0 THEN 'positive'
ELSE 'neither' END)
GROUP BY signs.sign
英文:
SELECT signs.sign,
SUM(CASE WHEN Numbers.number IS NULL THEN 0 ELSE 1 END)
FROM (SELECT 'positive' AS [sign]
UNION ALL
SELECT 'negative'
UNION ALL
SELECT 'neither') AS signs
LEFT OUTER JOIN Numbers
ON (signs.sign = CASE WHEN number < 0 THEN 'negative'
WHEN number > 0 THEN 'positive'
ELSE 'neither' END)
GROUP BY signs.sign
Since you want all the signs values, you need to have a table with all of them in it. You can do a table valued subquery like the above or an actual or temporary table. But the idea is that if the value doesn't exist in your data, you are not going to get it in your output. Since the neither value is not in your data, you need to create a table that has it. Then we join to your actual table based on the sign of the number and get a count.
To explain the sum vs count thing, since this is outer join you'll get rows with Numbers.number equal to NULL for the neither case. So if you count them you get 1 instead of the desired 0. So I changed it to give each row a value of 0 or 1 and take the sum. So for the rows with NULL number I give them that value of 0 so they aren't counted and for the others a value of 1 so they are. If you had actual NULL values in your numbers table, you'd have to do the null check on a field that always has data and no nulls.
Edit: changed from count approach to sum to account for missing rows in join
答案4
得分: 1
你可以首先编写条件（使用 cte），然后根据这些条件分组。
with _listcon as (
select 'negative' as sign, CAST(0x8000000000000000 AS bigint) as startNumber, -1 as EndNumber union
select 'positive' as sign, 1 as startNumber, CAST(0x7FFFFFFFFFFFFFFF AS bigint) as EndNumber union
select 'neither' as sign, 0 as startNumber, 0 as EndNumber
)
select sign, ISNULL(COUNT(a.number), 0) as sign_count
from Numbers a
right join _listcon b on a.number between startNumber and EndNumber
GROUP BY sign
英文:
You can write conditions first(with cte) and then group based on those conditions
with _listcon as (
select 'negative' as sign,CAST(0x8000000000000000 AS bigint) /*min bigint*/ startNumber,-1 EndNumber union
select 'positive' as sign,1 startNumber, CAST(0x7FFFFFFFFFFFFFFF AS bigint) /*max bigint*/ EndNumber union
select 'neither' as sign, 0 startNumber,0 EndNumber
)
select sign, ISNULL( COUNT(a.number),0) sign_count
from Numbers a
right join _listcon b on a.number between startNumber and EndNumber
GROUP BY sign
答案5
得分: 1
这是使用 CASE 表达式和 UNPIVOT 的一种方法：
select count(case when number < 0 then 1 end) as 'negative',
count(case when number > 0 then 1 end) as 'positive',
count(case when number = 0 then 1 end) as 'neither'
from Numbers;
将返回：
negative positive neither
1 2 0
然后我们使用以下查询来进行 unpivot 操作：
with cte as (
select count(case when number < 0 then 1 end) as 'negative',
count(case when number > 0 then 1 end) as 'positive',
count(case when number = 0 then 1 end) as 'neither'
from Numbers
)
select sign, sign_count
from cte
unpivot
(
sign_count
for sign in (negative, positive, neither)
) u;
结果为：
sign sign_count
negative 1
positive 2
neither 0
英文:
This is a way to do it using CASE expression and UNPIVOT :
select count(case when number < 0 then 1 end) as 'negative',
count(case when number > 0 then 1 end) as 'positive',
count(case when number = 0 then 1 end) as 'neither'
from Numbers;
Will return :
negative positive neither
1 2 0
Then we unpivot it using this query :
with cte as (
select count(case when number < 0 then 1 end) as 'negative',
count(case when number > 0 then 1 end) as 'positive',
count(case when number = 0 then 1 end) as 'neither'
from Numbers
)
select sign, sign_count
from cte
unpivot
(
sign_count
for sign in (negative, positive, neither)
) u;
Result :
sign sign_count
negative 1
positive 2
neither 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论