sql面试的
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count
(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count
(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId
)>1)
3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having
count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having
count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having
count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
相关文档：
有执行sql条件语句where id in(@参数)的时候，如果处理不当，就会出现问题：
如下面这个存储过程：
alter proc Web_gettwtwgoldgameserverGoldSell
@ID int
as
declare @twgoldsellID nvarchar(1000)
select @twgoldsellID=twgoldsellID from twgoldgameserver where ID=@ID
set @twgoldsellID=replace(@twgoldsell ......
1. 程序如下：
string str = "Create Database " + "DBname";
string con = "Data Source=10.0.0.249\\sql2005;Initial Catalog=master;Persist Security Info=True;User ID=sa;Password=sa";
&n ......
Java数据类型
Hibernate数据类型
标准SQL数据类型
(PS:对于不同的DB可能有所差异)
byte、java.lang.Byte
byte
TINYINT
short、java.lang.Short
short
SMALLINT
int、java.lang.Integer
integer
INGEGER
long、java.lang.Long
long
BIGINT
float、java.lang.Float
float
FLOAT
double、java.lang.Doubl ......
对于有主键自动增长数据库表，在进行分页设置时，我认为还比较不错的是：
SELECT TOP 页大小 *
from TestTable
WHERE (ID >
(SELECT MAX(id)
from (SELECT TOP 页大小*页数 id
......
加[ ]用于定界列名或表名等信息，以区别一些特殊的不合命名规则的字串，或是与系统保留字有冲突。如"user"是SQL Server保留的关键字，所以用"select * from user"查询会提示语法错误。加上中括号，以声明其不是保留字。 "select * from [user]" 如果表名不是关键字，如Company，则用"select * from Company"是没问题的，不� ......
