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)
请详细说明select * from ttt where (serverid,userid) in (select serverid,userid from ttt group by serverid,userid having count(*)=1);
我也来猜一下 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) )
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)
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)
create table DBTest ( id int identity(1,1) primary key, name varchar(25) , serverid int , userid int ) goinsert 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
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
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>
or SELECT * FROM dbtest a WHERE NOT EXISTS(SELECT 1 FROM dbtest WHERE a.serverid=serverid AND a.userid=userid AND a.id>id)
from tb A
where not exits (select 1 from tb B where A.serverid=B.serverid and A.userid=B.userid)
[征集]分组取最大N条记录方法征集,及散分....
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)
试试这样~
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式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)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
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)
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)
)
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)
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)
create table DBTest
(
id int identity(1,1) primary key,
name varchar(25) ,
serverid int ,
userid int
)
goinsert 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
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
-> 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>
SELECT * FROM dbtest a WHERE NOT EXISTS(SELECT 1 FROM dbtest WHERE a.serverid=serverid
AND a.userid=userid AND a.id>id)