表名 tab
id regtime username
104001 2009-01-13 10:43:00 ymf021
104002 2009-01-13 10:43:00 ymf021
104003 2009-01-13 10:43:00 yddd
104004 2009-01-13 10:43:00 dfdf1
104005 2009-01-13 10:43:00 ymf021
104006 2009-01-13 10:43:00 ymf021
104007 2009-01-13 10:43:00 yddd1
104008 2009-01-13 10:43:00 y111
104009 2009-01-13 10:43:00 ymf021
104010 2009-01-13 10:43:00 ymdfd21
104011 2009-01-13 10:43:00 ymf021
104012 2009-01-13 10:43:00 1333
104013 2009-01-13 10:43:00 ymf021
104014 2009-01-13 10:43:00 1333
104015 2009-01-13 10:43:00 ymf021
这是表的结构 我想要的结果是 查询username列重复出现2次的则只取其中的2条记录,不重复的则全取
我要的结果是 凡是重复出现2次以上的记录 只取其中的2条,否则全取 结果就是 104001 2009-01-13 10:43:00 ymf021
104002 2009-01-13 10:43:00 ymf021
104003 2009-01-13 10:43:00 yddd
104004 2009-01-13 10:43:00 dfdf1
104007 2009-01-13 10:43:00 yddd1
104008 2009-01-13 10:43:00 y111
104010 2009-01-13 10:43:00 ymdfd21
104012 2009-01-13 10:43:00 1333
104014 2009-01-13 10:43:00 1333
这样的语句怎么写 高手帮下忙
id regtime username
104001 2009-01-13 10:43:00 ymf021
104002 2009-01-13 10:43:00 ymf021
104003 2009-01-13 10:43:00 yddd
104004 2009-01-13 10:43:00 dfdf1
104005 2009-01-13 10:43:00 ymf021
104006 2009-01-13 10:43:00 ymf021
104007 2009-01-13 10:43:00 yddd1
104008 2009-01-13 10:43:00 y111
104009 2009-01-13 10:43:00 ymf021
104010 2009-01-13 10:43:00 ymdfd21
104011 2009-01-13 10:43:00 ymf021
104012 2009-01-13 10:43:00 1333
104013 2009-01-13 10:43:00 ymf021
104014 2009-01-13 10:43:00 1333
104015 2009-01-13 10:43:00 ymf021
这是表的结构 我想要的结果是 查询username列重复出现2次的则只取其中的2条记录,不重复的则全取
我要的结果是 凡是重复出现2次以上的记录 只取其中的2条,否则全取 结果就是 104001 2009-01-13 10:43:00 ymf021
104002 2009-01-13 10:43:00 ymf021
104003 2009-01-13 10:43:00 yddd
104004 2009-01-13 10:43:00 dfdf1
104007 2009-01-13 10:43:00 yddd1
104008 2009-01-13 10:43:00 y111
104010 2009-01-13 10:43:00 ymdfd21
104012 2009-01-13 10:43:00 1333
104014 2009-01-13 10:43:00 1333
这样的语句怎么写 高手帮下忙
解决方案 »
- sql2000:通过用备份文件恢复到新服务器后,为什么数据存储(更新)还在原服务器上?
- 速求这两个表如何合并
- 求教,关于锁的问题!
- sql server2005 建立oracle服务器连接时 没有 oracle接口,求大神指导
- 为什么我建的表示只读的呢?
- SQL安装时出现的问题
- 一个急手的问题!!!
- 怎样将EXCEL数据表,用存储过程或本地包导入到SQL SERVER 数据库中?
- 如何建立DTS包,将一个数据库里面的数据倒入另一个?
- 请介绍SQL anywhere7.0,6.0的详细资料(主要是触发器和存储过程 )
- 关于判断的问题
- a.consult_physician *= c.emp_sn and 中的 *= 是什么意思
from tb A
where checknum(*) in (select top 2 check(*) from tb where usermname = A.username )
在 '*' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 3
在关键字 'check' 附近有语法错误。
DECLARE @tab TABLE
(
id INT ,
regtime DATETIME,
usermname VARCHAR(10)
)
INSERT INTO @tab
SELECT 104001, '2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104002, '2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104003, ' 2009-01-13 10:43:00','yddd' UNION ALL
SELECT 104004, ' 2009-01-13 10:43:00','dfdf1' UNION ALL
SELECT 104005, ' 2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104006, ' 2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104007, ' 2009-01-13 10:43:00','yddd1' UNION ALL
SELECT 104008, ' 2009-01-13 10:43:00',' y111' UNION ALL
SELECT 104009, ' 2009-01-13 10:43:00',' ymf021' UNION ALL
SELECT 104010, ' 2009-01-13 10:43:00',' ymdfd21' UNION ALL
SELECT 104011, ' 2009-01-13 10:43:00',' ymf021' UNION ALL
SELECT 104012, ' 2009-01-13 10:43:00','1333' UNION ALL
SELECT 104013, ' 2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104014, ' 2009-01-13 10:43:00','1333' UNION ALL
SELECT 104015, ' 2009-01-13 10:43:00','ymf021'
SELECT * FROM @tab
select * from @tab as aa
where id in (select top 2 id from @tab where usermname=aa.usermname) id regtime usermname
----------- ----------------------- ----------
104001 2009-01-13 10:43:00.000 ymf021
104002 2009-01-13 10:43:00.000 ymf021
104003 2009-01-13 10:43:00.000 yddd
104004 2009-01-13 10:43:00.000 dfdf1
104005 2009-01-13 10:43:00.000 ymf021
104006 2009-01-13 10:43:00.000 ymf021
104007 2009-01-13 10:43:00.000 yddd1
104008 2009-01-13 10:43:00.000 y111
104009 2009-01-13 10:43:00.000 ymf021
104010 2009-01-13 10:43:00.000 ymdfd21
104011 2009-01-13 10:43:00.000 ymf021
104012 2009-01-13 10:43:00.000 1333
104013 2009-01-13 10:43:00.000 ymf021
104014 2009-01-13 10:43:00.000 1333
104015 2009-01-13 10:43:00.000 ymf021(15 行受影响)id regtime usermname
----------- ----------------------- ----------
104001 2009-01-13 10:43:00.000 ymf021
104002 2009-01-13 10:43:00.000 ymf021
104003 2009-01-13 10:43:00.000 yddd
104004 2009-01-13 10:43:00.000 dfdf1
104007 2009-01-13 10:43:00.000 yddd1
104008 2009-01-13 10:43:00.000 y111
104009 2009-01-13 10:43:00.000 ymf021
104010 2009-01-13 10:43:00.000 ymdfd21
104011 2009-01-13 10:43:00.000 ymf021
104012 2009-01-13 10:43:00.000 1333
104014 2009-01-13 10:43:00.000 1333(11 行受影响)
(
id INT ,
regtime DATETIME,
usermname VARCHAR(10)
)
INSERT INTO @tab
SELECT 104001, '2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104002, '2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104003, ' 2009-01-13 10:43:00','yddd' UNION ALL
SELECT 104004, ' 2009-01-13 10:43:00','dfdf1' UNION ALL
SELECT 104005, ' 2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104006, ' 2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104007, ' 2009-01-13 10:43:00','yddd1' UNION ALL
SELECT 104008, ' 2009-01-13 10:43:00',' y111' UNION ALL
SELECT 104009, ' 2009-01-13 10:43:00',' ymf021' UNION ALL
SELECT 104010, ' 2009-01-13 10:43:00',' ymdfd21' UNION ALL
SELECT 104011, ' 2009-01-13 10:43:00',' ymf021' UNION ALL
SELECT 104012, ' 2009-01-13 10:43:00','1333' UNION ALL
SELECT 104013, ' 2009-01-13 10:43:00','ymf021' UNION ALL
SELECT 104014, ' 2009-01-13 10:43:00','1333' UNION ALL
SELECT 104015, ' 2009-01-13 10:43:00','ymf021' select * from @tab as aa
where id in (select top 2 id from @tab where usermname=aa.usermname)
ORDER BY [usermname]id regtime usermname
----------- ----------------------- ----------
104008 2009-01-13 10:43:00.000 y111
104010 2009-01-13 10:43:00.000 ymdfd21
104011 2009-01-13 10:43:00.000 ymf021
104009 2009-01-13 10:43:00.000 ymf021
104012 2009-01-13 10:43:00.000 1333
104014 2009-01-13 10:43:00.000 1333
104004 2009-01-13 10:43:00.000 dfdf1
104003 2009-01-13 10:43:00.000 yddd
104007 2009-01-13 10:43:00.000 yddd1
104001 2009-01-13 10:43:00.000 ymf021
104002 2009-01-13 10:43:00.000 ymf021(11 行受影响)