sql 读取全部记录，重复记录只读取其中一条

sql 读取全部记录，重复记录只读取其中一条

环境:SQL Server

如下：

表是：article,有以下字段：

id   year  month username  title
1    2006   8     admin     xxx
2    2006   8     admin     yyy
3    2006   8     admin     zzz
4    2006   9     admin     ppp

重复的month数据只调一条

SQL 语句
select id,title from article where id in(SELECT max(id) FROM article GROUP BY month) 

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

select * from article a where not exists(select 1 from article b where a.id<b.id and a.[year]=b.[year] and a.[month]=b.[month])
或
select * from article a where 1>(select count(1) from article b where a.id<b.id and a.[year]=b.[year] and a.[month]=b.[month])
或
select a.* from article a inner join(select min(id) mi from article group by rtrim([year])+'-'+rtrim([month])) b on a.id=b.mi
或
select * from article a where id in(select min(id) from article b where a.[year]=b.[year] and a.[month]=b.[month])

 

 

统计数据：sql="select count(*) as id from table"，调用：<%=rs("id")%>
精确查询：sql="select * from table where auther="&str&"order by id desc"
模糊查询：sql="select * from table where title like %"&str&"%order by id desc"
随机查询：Randomize
rid=int(rnd*20+1)
Set test_ti=Conn.Execute("SELECT * from table where id="&rid)
从表中取出第N条到第M条记录:SELECT TOP m-n+1 * FROM table WHERE (id NOT IN　(SELECT TOP n-1 id FROM table))
不过用in的效率不好，不如用这个：select * from tableName where id > (select top 1 id from tablename order by id asc)
随机取出n条记录：Sql server:select top n * from 表 order by newid()
Access:SELECT top n * FROM 表 ORDER BY Rnd(id)
mysql:Select * From 表 Order By rand() Limit n
这些是很多初学者不大掌握的查询语句，简单的就不列了。如果在使用中遇到难题，欢迎留言。