select id=indentity(int,1,1),* into #t from yourtable
select * from #t a where not exists(select 1 from #t where id<a.id and 姓名=a.姓名)
select * from #t a where not exists(select 1 from #t where id<a.id and 姓名=a.姓名)
from # a
where not exists(select 1 from # where 姓名= a.姓名 and idrow > a.idrow)drop table #
select *
from # a
where not exists(select 1 from # where 姓名= a.姓名 and idrow > a.idrow) and 日期>'2006-1-1'
结果还是将所有行筛选了
请问怎么合并啊
(select 姓名 from tb group by 姓名 having(姓名)>1)
union
select * from tb where 姓名 in
(select 姓名 from tb group by 姓名 having(姓名)=1)
SQL> CREATE TABLE TEST(NAME VARCHAR2(20), COURSE VARCHAR2(20), COURSEDATE VARCHAR2(6), TEL VARCHAR2(12));Table created
SQL> INSERT INTO TEST VALUES('XIAOWANG', 'MATH', '20060228', '02150800193');1 row insertedSQL> INSERT INTO TEST VALUES('XIAOWANG', 'ENGLISH', '20060228', '02150800193');1 row insertedSQL> INSERT INTO TEST VALUES('XIAOZHANG', 'MATH', '20060227', '02150800192');1 row insertedSQL> SELECT * FROM TEST;NAME COURSE COURSEDATE TEL
-------------------- -------------------- ---------- ------------
XIAOWANG MATH 20060228 02150800193
XIAOWANG ENGLISH 20060228 02150800193
XIAOZHANG MATH 20060227 02150800192
生成sql语句:
SQL> SELECT a.* FROM test a WHERE a.course = (SELECT course FROM test WHERE NAME = a.NAME AND ROWNUM <= 1);NAME COURSE COURSEDATE TEL
-------------------- -------------------- ---------- ------------
XIAOWANG MATH 20060101 02150800193
XIAOZHANG MATH 20060227 02150800192
----------------
以上在oracle10g,pl/sql6.0上试验成功。
-------------------- -------------------- ---------- ------------
XIAOWANG MATH 20060101 02150800193
XIAOZHANG MATH 20060227 02150800192
----------------
以上在oracle10g,pl/sql6.0上试验成功。日期条件在(SELECT course FROM test WHERE NAME = a.NAME AND ROWNUM <= 1)中的where后面添加,在sqlserver2000中用top1代替"ROWNUM <= 1";
----------------------------------
因为我的数据库是SQL7.0,所以我把语句改了一下,但是提示错误信息:
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'top' 附近有语法错误。
改下是我以后的语句:
CREATE TABLE TEST
(
NAME VARCHAR(20),
COURSE VARCHAR(20),
COURSEDATE smalldatetime,
TEL VARCHAR(12)
)
goINSERT INTO TEST VALUES('XIAOWANG', 'MATH', '20060228', '02150800193')--1 row insertedINSERT INTO TEST VALUES('XIAOWANG', 'ENGLISH', '20060228', '02150800193')--1 row insertedINSERT INTO TEST VALUES('XIAOZHANG', 'MATH', '20060227', '02150800192')
goSELECT a.* FROM test a WHERE a.course = (SELECT course FROM test WHERE NAME = a.NAME AND top 1)
我是一步一步按你的做,怎么会有这样的错误呢!?
改为
SQL> SELECT a.* FROM test a WHERE a.course = (SELECT TOP 1 course FROM test WHERE NAME = a.NAME);
应该是这样,我这里没有SQLSERVER2000,你可以查一下TOP的用法