有表
table1(col1 char(20),col2 int)
原始数据如下:
col1 col2
a 1
a 3
a 13
b 2
c 123
c 562
..
希望对于col1每一个值,只保留col2中最小的那一行,即最终得到如下:
col1 col2
a 1
b 2
c 123
-=====================
SQL2000中,我使用如下查询:
SELECT * FROM TABLE1 AS A
WHERE EXISTS
(
SELECT * FROM TABLE1 AS B
WHERE A.COL1 = B.COL1 AND
A.COL2 > B.COL2
)
可以看到返回的结果集就是我想删除的,但是把它改为
DELETE FROM TABLE1 ...(以后同上)
就出现了语法错误,恳请路过朋友代为解答
告之正确写法,谢谢~~~
table1(col1 char(20),col2 int)
原始数据如下:
col1 col2
a 1
a 3
a 13
b 2
c 123
c 562
..
希望对于col1每一个值,只保留col2中最小的那一行,即最终得到如下:
col1 col2
a 1
b 2
c 123
-=====================
SQL2000中,我使用如下查询:
SELECT * FROM TABLE1 AS A
WHERE EXISTS
(
SELECT * FROM TABLE1 AS B
WHERE A.COL1 = B.COL1 AND
A.COL2 > B.COL2
)
可以看到返回的结果集就是我想删除的,但是把它改为
DELETE FROM TABLE1 ...(以后同上)
就出现了语法错误,恳请路过朋友代为解答
告之正确写法,谢谢~~~
解决方案 »
- 语句查询
- |zyciis| 我要写一个函数,找出最大的上级,但语法有错,大家帮改一下
- 请问WINCE里的SQLSERVER是不是和WINDOWXP里装的SQLSERVER一样的用法???
- 急求一sql语句
- 跟着前辈大牛们学习了这么久,CSDN的处女贴,来个99乘法表,像大牛们学习!
- 请大家帮忙写一段算法,实现合并报表的功能。
- 帮忙写一个简单的SQL语法。
- 请帮忙看一下,链接服务器错误(错误7399:OLE DB 提供程序'Microsoft.Jet.OLEDB.4.0'报错)。
- 怎么判断 当点击button1时执行xxxx
- 一条t-SQL语句实现如下要求,求指教
- 取表自身一部分做关联该怎么写?
- 怎么执行外部文件中存储的批处理语句?
where not exists(select 1 from table1 where col1=a.col1 and col2<a.col2)
把not exists
改为
exists
delete a
from table1 as a
where not exists(select 1 from table1 where col1=a.col1 and col2<a.col2)
delete a
from table1 as a
where exists(select 1 from table1 where col1=a.col1 and col2<a.col2)--去掉not
col1 char(1),
col2 int
)insert into table1
select 'a',2 union all
select 'a',3 union all
select 'b',21 union all
select 'b',13 union all
select 'c',145select col1,min(col2) as col2 from table1 group by col1drop table table1
from table1 a
Where col2 in (Select Min(col2) From table1 Where col1 = a.col1)
delete (SELECT * FROM TABLE1 AS A
WHERE EXISTS
(
SELECT * FROM TABLE1 AS B
WHERE A.COL1 = B.COL1 AND
A.COL2 > B.COL2
)) a