英文:
How can I force a SQL GROUP BY ? Or choose to prefer one of the value available?
问题
我有两个表：
表 1（T1）：
| ID | AMOUNT| NUMBER|
| -- | ----- | ----- |
| 1 | 100$ | 456 |
| 2 | 22$ | 789 |
表 2（T2）：
| NUMBER | VALUE（字符串）|
| ------ | -------------- |
| 456 | abc |
| 456 | def |
| 789 | ghi |
我的查询：
SELECT DISTINCT T1.ID, T1.AMOUNT, T2.VALUE
FROM T1 RIGHT JOIN T2 ON T1.NUMBER = T2.NUMBER
结果（我不想要的）：
| ID | AMOUNT| VALUE |
| -- | ----- | ----- |
| 1 | 100$ | abc |
| 1 | 100$ | def |
| 2 | 22$ | ghi |
如果我尝试按ID分组，会出现错误。
我尝试了不同的连接方法，右连接是结果最好的一个（结果行数比其他方法少）。
我尝试了一些其他方法，但都不起作用。
目标是只在我的结果中有一个不同的ID。可能是通过按字母表顺序选择两个值中的第一个，或者用其他我不知道的方法，但对我来说最重要的是每行只有一个ID/金额，并且无论VALUE是什么，因为在我后面的计算中这个值不那么重要，但我仍然需要它。
可能的期望结果之一：
| ID | AMOUNT| VALUE |
| -- | ----- | ----- |
| 1 | 100$ | abc |
| 2 | 22$ | ghi |
英文:
I have two tables :
Table 1 (T1) :
| ID | AMOUNT | NUMBER |
|---|---|---|
| 1 | 100$ | 456 |
| 2 | 22$ | 789 |
Table 2 (T2) :
| NUMBER | VALUE (string) |
|---|---|
| 456 | abc |
| 456 | def |
| 789 | ghi |
My Query :
SELECT DISTINCT T1.ID, T1.AMOUNT, T2.VALUE
FROM T1 RIGHT JOIN T2 ON T1.NUMBER = T2.NUMBER
Result (that I don't want) :
| ID | AMOUNT | VALUE |
|---|---|---|
| 1 | 100$ | abc |
| 1 | 100$ | def |
| 2 | 22$ | ghi |
If I try to GROUP BY ID, I will get an error.
I tried different JOIN methods and the RIGHT JOIN was the one with the best output (fewer lines in the result than the others).
I tried some other things that didn't worked out.
The goal would be to only have a distinct ID on my result.
It could be by choosing the first value of the two alphabetically or with an other method i dont know the possibilities but the most important thing to me is to have only one ID / AMOUNT per row and have whatever VALUE available because this value is less relevant in my later calculations but I still need it.
One of the possible wanted result :
| ID | AMOUNT | VALUE |
|---|---|---|
| 1 | 100$ | abc |
| 2 | 22$ | ghi |
答案1
得分: 1
你需要一个聚合函数来合并你按组进行分组的列，如果你不想要重复的结果：
SELECT T1.ID, SUM(T1.AMOUNT), MAX(T2.VALUE)
FROM T1
RIGHT JOIN T2 ON T1.NUMBER = T2.NUMBER
GROUP BY T1.ID
英文:
You will need an aggregate function to roll up the columns you are grouping by if you don't want duplicates:
SELECT T1.ID, SUM(T1.AMOUNT), MAX(T2.VALUE)
FROM T1
RIGHT JOIN T2 ON T1.NUMBER = T2.NUMBER
GROUP BY T1.ID
Note to mods: I am reposting my accepted answer since the original answer was removed in the ChatGPT witch hunt. In hindsight I can see how my choice of words ("Here's an example...") may have created an incorrect impression. Be assured that this answer was and is a product of my brain and not ChatGPT. If you disagree, let's talk about it instead of just deleting an accepted answer, which was confirmed to be a valid solution by the asker, no less.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论