SQL查詢,關於DISTINCT

比如我要對 Name 字段做查詢,並且要求不能有重復值
則SQL查詢則可以寫成:

"SELECT DISTINCT Name FORM Table"
測試無問題

但如果查詢字段多於一個,則DISTINCT無效了,如下所示

"SELECT DISTINCT Name,id FORM Table"
測試結果是返回所有的值

有沒有辦法可以查詢多個字段,並且按照某個字段做DISTINCT??

---------------------------------------------------------------

distinct好象就是对你选出的所有字段做删除重复的,没办法选多个只对一个字段起作用
---------------------------------------------------------------

二种方法取表中一个字段不重复的记录
select * from [menu]
where [Name] in(select [Name] from [menu] group by [Name] having count([Name])=1)

select * from [menu] x where [Name]=
(selecr top 1 [Name] from [menu] where [Name]=x.[Name])
---------------------------------------------------------------

DISTINCT 是对记录而言,即一条记录完全系统,当然包括所有的字段相同。
---------------------------------------------------------------

SELECT * FROM Table WHERE Name in (SELECT DISTINCT Name FORM Table)

Published At
Categories with Web编程
Tagged with
comments powered by Disqus