记录重复问题，急急急

[DiscussQuestions]

有一张表 Table1，字段 id name serverid userid 四个字段
如何查询出serverid、userid不同时重复的记录(也就是说 serverid和userid相当于一个联合主键)
急急急，在线等待

[claro]

试试1F的。

[ACMAIN_CHM]

select * from Table1 group by serverid,userid

[DiscussQuestions]

[Quote=引用 4 楼 acmain_chm 的回复:]
SQL code
select * from Table1 group by serverid,userid
[/Quote]

这个怎么可以，呵呵

[DiscussQuestions]

[Quote=引用 1 楼 rucypli 的回复:]
select *
from tb A
where not exits (select 1 from tb B where A.serverid=B.serverid and A.userid=B.userid)
[/Quote]

你的这个没有测试吧，不行

[feixianxxx]

select *
from tb A
where not exits (select 1 from tb B where A.serverid=B.serverid and A.userid=B.userid and a.id<>id)

试试这样~

[DiscussQuestions]

[Quote=引用 7 楼 feixianxxx 的回复:]
SQL code
select *
from tb A
where not exits (select 1 from tb B where A.serverid=B.serverid and A.userid=B.userid and a.id<>id)

试试这样~
[/Quote]

这样一样的不行，你写个小测试就知道了

[feixianxxx]

[Quote=引用 8 楼 discussquestions 的回复:]
引用 7 楼 feixianxxx 的回复:
SQL code
select *
from tb A
where not exits (select 1 from tb B where A.serverid=B.serverid and A.userid=B.userid and a.id<>id)

试试这样~


这样一样的不行，你写个小测试就知道了
[/Quote]
可否给点测试的数据 ~说明你要的结果。
不太清楚你要什么~

[feixianxxx]

[Quote=引用 8 楼 discussquestions 的回复:]
引用 7 楼 feixianxxx 的回复:
SQL code
select *
from tb A
where not exits (select 1 from tb B where A.serverid=B.serverid and A.userid=B.userid and a.id<>id)

试试这样~


这样一样的不行，你写个小测试就知道了
[/Quote]
可否给点测试的数据 ~说明你要的结果。
不太清楚你要什么~

[ACMAIN_CHM]

(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html

1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样，（并给以简单的算法描述）
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)

这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

[wwwwb]

1、贴记录及要求结果出来看看；
2、
try:
select * from tt a left join tt b
on (a.serverid=b.serverid and a.userid<>userid)
or
(a.serverid<>b.serverid and a.userid=userid)

[loveflea]

请详细说明

select * from ttt where (serverid,userid) in (select serverid,userid from ttt group by serverid,userid having count(*)=1);

[guguda2008]

我也来猜一下

SELECT * FROM TABLE1 T1 WHERE EXISTS(
SELECT 1 FROM TABLE1 T2 WHERE T2.ID=T1.ID AND T2.NAME=T1.NAME 
AND (T2.SERVERID<>T1.SERVERID OR T2.USERID<>T1.USERID)
)

[zmjsg]

select * from csdn_test2 where id not in(SELECT a.id
FROM `csdn_test2` as a left join csdn_test2 as b on(a.serverid=b.serverid and a.userid=b.userid) where a.id<>b.id)

[zmjsg]

select * from Table1 where id not in(
SELECT a.id FROM `Table1` as a left join Table1 as b 
on(a.serverid=b.serverid and a.userid=b.userid) where a.id<>b.id)

[DiscussQuestions]

[Quote=引用 12 楼 wwwwb 的回复:]
1、贴记录及要求结果出来看看；
2、
try:
select * from tt a left join tt b
on (a.serverid=b.serverid and a.userid<>userid)
or
(a.serverid<>b.serverid and a.userid=userid)
[/Quote]

create table DBTest
(
	id int identity(1,1) primary key,
	name varchar(25) ,
	serverid int ,
	userid int
)
go

insert into DBTest values('A',10,100)
insert into DBTest values('B',10,110)
insert into DBTest values('C',11,100)
insert into DBTest values('D',12,102)
insert into DBTest values('E',12,102)
insert into DBTest values('F',10,110)
insert into DBTest values('G',13,130)
insert into DBTest values('H',10,130)
go

最后结果
----------------------
id     name   serverid  userid
1	A	10	100
2	B	10	110
3	C	11	100
4	D	12	102
5	G	13	130
6	H	10	130

[WWWWA]

SELECT a.* FROM dbtest a
INNER JOIN (
SELECT a.serverid,a.userid,MIN(id) AS mi FROM dbtest a GROUP BY a.serverid,a.userid) b
ON b.mi=a.id ORDER BY a.NAME,a.serverid,a.userid

[ACMAIN_CHM]

mysql> select *
    -> from DBTest
    -> group by name,serverid,userid;
+----+------+----------+--------+
| id | name | serverid | userid |
+----+------+----------+--------+
|  1 | A    |       10 |    100 |
|  2 | B    |       10 |    110 |
|  3 | C    |       11 |    100 |
|  4 | D    |       12 |    102 |
|  5 | E    |       12 |    102 |
|  6 | F    |       10 |    110 |
|  7 | G    |       13 |    130 |
|  8 | H    |       10 |    130 |
+----+------+----------+--------+
8 rows in set (0.09 sec)

mysql> select *
    -> from DBTest
    -> group by serverid,userid;
+----+------+----------+--------+
| id | name | serverid | userid |
+----+------+----------+--------+
|  1 | A    |       10 |    100 |
|  2 | B    |       10 |    110 |
|  8 | H    |       10 |    130 |
|  3 | C    |       11 |    100 |
|  4 | D    |       12 |    102 |
|  7 | G    |       13 |    130 |
+----+------+----------+--------+
6 rows in set (0.00 sec)

mysql>

[WWWWA]

or
SELECT * FROM dbtest a WHERE NOT EXISTS(SELECT 1 FROM dbtest WHERE a.serverid=serverid
AND a.userid=userid AND a.id>id

)

[mysqldbd]

[Quote=引用 18 楼 wwwwa 的回复:]
SELECT a.* FROM dbtest a
INNER JOIN (
SELECT a.serverid,a.userid,MIN(id) AS mi FROM dbtest a GROUP BY a.serverid,a.userid) b
ON b.mi=a.id ORDER BY a.NAME,a.serverid,a.userid
[/Quote]

支持，说的很好！

[iihero_]

4楼的方法比较标准可行。