数据量很大，求高效sql！在线等！！！！！！！！！！

[love_study]

有个数据表，数据量很大大概30w
我抽象说一下结构
表名t_talbe(usersid,orderid) 联合主键

数据
1,1
1,2
1,3
2,1
3,1
4,1
4,3
...

现在想查出 orderid=1 的usersid，但是usersid有不能和其他order有对应关系的，所有usersid

这个结果应该是 2，3

[you_tube]

left join

[love_study]

大哥，你给写全好吗？

[chuifengde]

orderid=1对应的usersid是1,2,3,4 其中1,2,3都与orderid有对应关系，为何结果是2,3?

[love_study]

后面的 1 和usersid 没有关系，我只是举例子

[昵称被占用了]

select usersid
from t_talbe a
where orderid=1
and not exists (
 select 1
 from t_talbe 
 where usersid = a.usersid
 and orderid<>1
 )

[昵称被占用了]

left join 写法

select a.usersid
from t_talbe a left join t_talbe b
on b.usersid = a.usersid and a.orderid=1 and b.orderid<>1
where a,orderid=1
and b.usersid is null

[love_study]

换个写法
数据：
usersid,orderid
1 A
1 B
1 C
2 A
3 A
4 A
4 C

要查出orderid =A 的，但是usersid 只能是单行的，所有数据 ，结果是 2，3
因为 1 的数据还有 B，C；4 的数据还有 C

[you_tube]

DECLARE @t_table  TABLE (userid INT,orderid int)
INSERT INTO @t_table 
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL 
SELECT 2,1 UNION ALL 
SELECT 3,1 UNION ALL 
SELECT 4,1 UNION ALL 
SELECT 4,3
--SELECT userid,orderid FROM @t_table 

select a.userid
from @t_table a left join @t_table b
on b.userid = a.userid and a.orderid=1 and b.orderid<>1
where a.orderid=1
and b.userid is null

userid
-----------
2
3

[claro]

[Quote=引用 2 楼 chuifengde 的回复:]
orderid=1对应的usersid是1,2,3,4 其中1,2,3都与orderid有对应关系，为何结果是2,3?
[/Quote]
帮顶
举例没看懂。

[xiaoxiangqing]

/*
如果orderid重复值很少,可以建orderid列的索引.如果重复值很大,没有必要建立索引
*/

if object_id('t_talbe')>0
drop table t_talbe
go
create table t_talbe(usersid int not null,orderid int not null,primary key(usersid,orderid))
go

insert t_talbe
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 3,1 union all
select 4,1 union all
select 4,3
go

select * from t_talbe
/*
usersid orderid
----------- -----------
1 1
1 2
1 3
2 1
3 1
4 1
4 3
*/

select usersid from t_talbe a where exists(select * from t_talbe where orderid=1 and a.usersid=usersid) group by usersid having count(*)=1
/*
usersid
2
3
*/

[htl258_Tony]

DECLARE @t  TABLE (userid INT,orderid varchar(10))
INSERT  @t 
SELECT 1,'A' UNION ALL
SELECT 1,'B' UNION ALL
SELECT 1,'C' UNION ALL 
SELECT 2,'A' UNION ALL 
SELECT 3,'A' UNION ALL 
SELECT 4,'A' UNION ALL 
SELECT 4,'C'
--SELECT userid,orderid FROM @t_table 

select * FROM @t a
  where orderid='a' and 
        userid = (select userid from @t where userid=a.userid group by userid having count(userid) = 1)
userid      orderid    
----------- ---------- 
2           A
3           A

（所影响的行数为 2 行）

[love_study]

太谢谢大家了！！

[y_dong119]

DECLARE @t_table TABLE (userid INT,orderid int)
INSERT INTO @t_table
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,3



;with cet123
as
(
select userid from @t_table group by userid
having(count(userid)=1)
)
select a.* from @t_table a, cet123 where a.userid=cet123.userid

[y_dong119]

DECLARE @t_table  TABLE (userid INT,orderid int)
INSERT INTO @t_table 
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL 
SELECT 2,1 UNION ALL 
SELECT 3,1 UNION ALL 
SELECT 4,1 UNION ALL 
SELECT 4,3



;with cet123 
as
(
select userid from @t_table group by userid 
having(count(userid)=1)
)
select a.* from @t_table a, cet123 where a.userid=cet123.userid



-----------------------------------------------------------------------------------

2	1
3	1