sql面试题

Select  * from table where id in(select id from table group by id having count(*)>1)

多个字段：

Select * from table where id in (select id from table group by  id,name count(*) >1)

select * from t_user a where (a.username) in (
select  username from t_user Group by username,age,sex having COUNT(*)>1)

一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
select id,Count（*) from tb group by id having count(*) > 1

select * from (select count(ID) as count from table group by ID) T where  T.count > 1

SQLSERVER服务器中，给定表 table1 中有两个字段 ID、LastUpdateDate，ID表示更新的事务号， LastUpdateDate表示更新时的服务器时间，请使用一句SQL语句获得最后更新的事务号
答：Select ID FROM table1 Where LastUpdateDate = (Select MAX(LastUpdateDate) FROM table1) 

写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的。
答：解1: select top 10 * from A where id not in (select top 30 id from A)
解2: select top 10 * from A where id > (select max(id) from (select top 30 id from Aorder by id asc )as A)
select top 10 * from Import_Audit_Log where ImportID > 30(select max(ImportID) from (select top 30 ImportID from Import_Audit_Log  order by ImportID asc) as A)

实例：

select  top 2 * from  dbo.t_user where userId not in (select top 3 userId from t_user)

select top 2 * from dbo.t_user where userid  > (select max(userid) from (select top 3 userid from t_user order by userId asc) as a)