access去除重复记录,在线等,谢谢了

[cg20]

表products
表xl
现在我想读取xl里面的10条记录,但是xl_id要等于products表里的p_xlid,并且p_xlid只取products里面的以p_browsecount DESC排序的前10条
我用的这个取出来有重复记录
SELECT TOP 10 xl_info.xl_id,xl_info.xl_name FROM products,xl_info WHERE (xl_info.xl_id=products.p_xlid) ORDER BY products.p_browsecount DESC

[liudingguoo87]

SELECT distinct TOP 10 xl_info.xl_id,xl_info.xl_name FROM products,xl_info WHERE (xl_info.xl_id=products.p_xlid) ORDER BY products.p_browsecount DESC

[wiki14]

select * from 表名 where name in (select name from 表名 having count(name)>1)

[hanzhaoever]

加关键字 distinct

[cg20]

异常详细信息: System.Data.OleDb.OleDbException: ORDER BY 子句与 (products.p_browsecount) DISTINCT 冲突。

[月之点点]

用内连接啊！！！用内连接就没有重复的了

select top 10 b.xl_id,b.xl_name 
FROM products as a inner JOIN xl_info as b 
ON b.xl_id=a.p_xlid) ORDER BY a.p_browsecount DESC

-.-!

[月之点点]

select top 10 b.xl_id,b.xl_name 
FROM products as a inner JOIN xl_info as b 
ON b.xl_id=a.p_xlid ORDER BY a.p_browsecount DESC

-.-!这个。。

[wiki14]

select TOP 10 xl_info.xl_id,xl_info.xl_name FROM xl_info
where xl_info.xl_id in (select  products.p_xlid  from  products  group  by  products.p_xlid  having  count(products.p_xlid) > 1) 

用group by having

[cg20]

wiki14

SELECT TOP 10 xl_id,xl_name FROM xl_info WHERE (xl_id in (SELECT TOP 10 p_xlid FROM products ORDER BY p_browsecount DESC)) ORDER BY xl_taxis DESC
读出来只有9条记录

[chenguang79]

SELECT xl_info.xl_id,xl_info.xl_name FROM xl_info WHERE xl_id in (
SELECT distinct TOP 10 products.p_xlid FROM products,xl_info WHERE (xl_info.xl_id=products.p_xlid) ORDER BY products.p_browsecount DESC
)

试一下

[cg20]

[Quote=引用 7 楼 wiki14 的回复:]
SQL codeselectTOP10 xl_info.xl_id,xl_info.xl_nameFROM xl_infowhere xl_info.xl_idin (select products.p_xlidfrom productsgroupby products.p_xlidhavingcount(products.p_xlid)>1)

用group  by  having
[/Quote]

还要将结果按照xl_taxis DESC显示，最后显示还是11条 ORDER BY xl_taxis DESC

[aellonxie]

[Quote=引用 2 楼 wiki14 的回复:]
select  *  from  表名  where  name  in  (select  name  from  表名  having  count(name)>1)

[/Quote]

先取不重复的记录条数，然后select * from （不重复的记录ID） where ID in （不重复的记录ID）

[智者知已应修善业]

distinct

[cg20]

distinct
ORDER BY 子句与 (products.p_browsecount) DISTINCT 冲突