蚂蚁的:去除重复值
如果有ID字段,就是具有唯一性的字段delect table where id not in ( select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。2,如果是判断所有字段也可以这样
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa3,没有ID的情况select identity(int,1,1) as id,* into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col2,col3...)
delect table
inset into table(...)
select ..... from #temp
col1+','+col2+','...col5 联合主键
select * from table where col1+','+col2+','...col5 in ( select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。2,
select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)
如果有ID字段,就是具有唯一性的字段delect table where id not in ( select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。2,如果是判断所有字段也可以这样
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa3,没有ID的情况select identity(int,1,1) as id,* into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col2,col3...)
delect table
inset into table(...)
select ..... from #temp
col1+','+col2+','...col5 联合主键
select * from table where col1+','+col2+','...col5 in ( select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。2,
select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)
解决方案 »
- sql语句
- MSSQLSERVER服务器突然无法启动服务了,如何解决。错误信息为:
- sql server 2008 安装不了,“活动模板库ATL出错"
- 在SQL Server2000企业管理器中删除记录出错?请高手指点!
- 请教一个SQL语句的写法
- 要求下一条记录满足制定条件的记录
- 数据备份问题
- (急)请教高手,sqlserver2000 备份的数据怎么回复到sqlserver7中,恢复时报错 难道不可以吗?
- 怎样在 在 SQL Server 2000 中的视图 创建索引?
- Win2003+SqlServer+SqlSp4不能访问数据库
- 高手求助,vb连SQL6.0,为什么不能访问日文表名,该如何连接
- SQL TROUBLE
join (select pbclassid,min(productid) as productid from product group by pbclassid) b
on a.productid = b.productid
order by ptime desc
order by ptime desc
请问大侠该怎么做
select top 15 * from product a where productid=(select min(productid) from product where pbclassid=a.pbclassid)
order by ptime desc
或:
Select top 15 a.* from product a
join (select pbclassid,min(productid) as productid from product group by pbclassid) b
on a.productid = b.productid
order by ptime desc
pbclassid productid ptime
----------- ----------- ------------------------------------------------------
234 409 2003-10-08 08:55:41.983
16 327 2003-10-08 08:45:41.327
222 72 2003-10-07 14:51:36.013
239 549 2003-10-07 12:23:07.153
239 551 2003-10-07 12:21:47.670
239 550 2003-10-07 12:17:53.687
206 93 2003-10-07 11:27:45.217
222 548 2003-10-07 10:52:09.687
222 246 2003-10-07 10:45:40.640
221 547 2003-10-07 10:41:26.500
224 546 2003-10-07 10:36:16.090
222 545 2003-10-07 10:33:19.903
222 544 2003-10-07 10:30:25.123
236 543 2003-10-07 09:51:37.280
28 542 2003-10-06 22:53:57.170(所影响的行数为 15 行)
pbclassid还是重复的呀
where o.pbclassid=s.pbclassid o.productid=s.productid
order by ptime desc
select top 15 pbclassid,productid,ptime from product o,(select min(pbclassid) pbclassid,productid from product group by productid) s
where o.pbclassid=s.pbclassid o.productid=s.productid
order by ptime desc
select top 15 o.pbclassid,o.productid,o.ptime from product o,(select min(pbclassid) pbclassid,productid from product group by productid) s
where o.pbclassid=s.pbclassid o.productid=s.productid
order by ptime desc
不好意思!刚刚看到
你的方法为什么今天的数据看不到呀
只能插昨天的吗?
select top 15 pbclassid,productid,ptime from product tem where productid=(select max(productid) from product where pbclassid=tem.pbclassid) order by ptime desc
提示什么?