英文:
SQL: Select distinct while ignoring only one column
问题
我有这样的一个表格。输入表格
name age amount xyz
dip 3 12 22a
dip 3 12 23a
oli 4 34 23b
mou 5 56 23b
mou 5 56 23a
maa 7 68 24c
我想要查找不同的行，但在查找不同行时要忽略“xyz”列。
输出应该如下所示。输出表格
name age amount xyz
dip 3 12 22a
oli 4 34 23b
mou 5 56 23b
maa 7 68 24c
SELECT DISTINCT * EXCEPT (xyz)
FROM table
但它没有起作用。
英文:
I have a table like this. input table
name age amount xyz
dip 3 12 22a
dip 3 12 23a
oli 4 34 23b
mou 5 56 23b
mou 5 56 23a
maa 7 68 24c
I want to find distinct rows but want to ignore the column 'xyz' while finding distinct.
The output should look like this. output table
name age amount xyz
dip 3 12 22a
oli 4 34 23b
mou 5 56 23b
maa 7 68 24c
SELECT
DISTINCT * EXCEPT (xyz)
FROM table
but it didn't work.
答案1
得分: 1
翻译好的部分如下：
few more options
select any_value(t).*
from your_table t
group by t.name, t.age, t.amount
with output
[![enter image description here][1]][1]
if you need MIN or MAX - you can use below
select any_value(t having min xyz).*
from your_table t
group by t.name, t.age, t.amount
or
select * from your_table
qualify xyz = min(xyz) over(partition by name, age, amount)
with output
[![enter image description here][2]][2]
希望这个翻译对您有所帮助。如果您有任何其他问题，请随时提问。
英文:
few more options
select any_value(t).*
from your_table t
group by t.name, t.age, t.amount
with output
if you need MIN or MAX - you can use below
select any_value(t having min xyz).*
from your_table t
group by t.name, t.age, t.amount
or
select * from your_table
qualify xyz = min(xyz) over(partition by name, age, amount)
with output
答案2
得分: 0
你需要使用 GROUP BY，请尝试以下代码，看看是否有效：
SELECT name, age, amount, MIN(xyz) AS xyz
FROM table
GROUP BY name, age, amount
英文:
You need to use group by, try the below and see if it works:
SELECT name, age, amount, MIN(xyz) AS xyz
FROM table
GROUP BY name, age, amount
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论