关于 distinct的使用问题(100分)

Y

youhe

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-31

• #1

SELECT distinct name
FROM height_success20
WHERE subject_id = 64 AND range BETWEEN 1 AND 8
ORDER BY level_int DESC

运行出现：ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

但我又需要level_int按降序排序，请问各大虾，如何解此问题？？？

 

H

honestman

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-31

• #2

他不是已经给了答案了吗？？？ORDER BY items must appear in the select list
把level_int加到Select中。

 

Y

youhe

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-31

• #3

我知道呀！可是结果不是我要的……
例：数据库记录:
name level_int
aa 9
aa 10
bb 7
bb 9
cc 8
cc 8
……

我要的记录是
name level_int
aa 10
bb 9
cc 8

但是按照你的给的答案就得到:
aa 9
aa 10
bb 7
bb 9
cc 8

也就是说distinct对相同记录（name,level_int相同）有效，而对其他就无效了.

 

O

onedot

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-31

• #4

我感觉如果你是想要上面的结果，SQL该这么写吧
SELECT name,my_level=max(level_int)
FROM height_success20
WHERE subject_id = 64 AND range BETWEEN 1 AND 8
group by name
ORDER BY my_level DESC

 

J

jqw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-31

• #5

agree with onedot

 

Y

youhe

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-31

• #6

多人接受答案了。