SQL高手请进，效率问题，头都大了

[灰太狼是程序员]

现有一注册表和一礼物表
每个人有四个礼物 每个礼物有9个部分 这九个部分需要自己去收集，现在想得到收集完成一个礼物以上的人的注册信息
每一条注册信息对应礼物表36条记录，这36条记录分4种，带表的是4个礼物，
register结构 id username
gift 结构 id userid type giftname state

我的SQL是这样的，但是效率很慢，因为数据量大

select * from register r where (select count(*) from gift where userid=r.userid and type=1 and state='ok')=9
or (select count(*) from gift where userid=r.userid and type=2 and state='ok')=9
or (select count(*) from gift where userid=r.userid and type=3 and state='ok')=9
or (select count(*) from gift where userid=r.userid and type=4 and state='ok')=9

[randomfeel]

汗~~都不正确
那算了

[mbh0210]

select register.* from register  
   inner join (
   select userid from gift
   where state='ok' 
   group by userid , type  having count(type)=9)A on  A.userid = register.userid 

[jenny0810]

把帖子发到sql论坛吧 里面有很多人 应该能解决你的问题

[zsyok]

不明

[randomfeel]

select r.id,r.username,g.type from register r left join gift g on r.id=g.userid 
group by r.id,r.username,g.type having state='ok'

没测试

[randomfeel]

漏了，修改如下：

select r.id,r.username,g.type from register r left join gift g on r.id=g.userid 
group by r.id,r.username,g.type having [state]='ok' and count(*)=9

[灰太狼是程序员]

上面的不行，不知道还有没有其它办法

[randomfeel]

再修改了一下

CREATE TABLE register (id int,username varchar(20))
INSERT INTO register
SELECT '1','A' union all
SELECT '2','B' union all
SELECT '3','C' union all
SELECT '4','D' 

CREATE TABLE gift (id int,userid int,type int,giftname varchar(20),state varchar(10))
INSERT INTO gift
SELECT '1','1','1','gift##','ok' union all 
SELECT '2','1','1','gift##','ok' union all 
SELECT '3','1','1','gift##','ok' union all 
SELECT '4','1','1','gift##','ok' union all 
SELECT '5','1','1','gift##','ok' union all 
SELECT '6','1','1','gift##','ok' union all 
SELECT '7','1','1','gift##','ok' union all 
SELECT '8','1','1','gift##','ok' union all 
SELECT '9','1','1','gift##','ok' union all 
SELECT '10','2','2','gift##','ok' union all 
SELECT '11','2','3','gift##','ok' union all 
SELECT '12','3','4','gift##','ok'

select r.id,r.username,g.type,count(*) as '已收集礼物' from register r left join gift g on r.id=g.userid where g.state='ok'
group by r.id,r.username,g.type

drop table register
drop table gift

[还想懒够]

建索引

[wenblue7]

用存储过程
把sql语句中所有的*好都换成列名
尽量避免用子查询

[sunzsh]

楼主你好，我用子查询写了一个，不知道我理解您的意思对不对，也不知道效率怎么样，呵呵，反正你看下

SELECT * FROM [register] WHERE [userid] in
(
	SELECT [userid] FROM [gift] 
		WHERE [state] = 'ok' 
		GROUP BY [type], [userid] 
		HAVING COUNT(1) = 9
)

[randomfeel]

恩，count(*)可以改成count(1)

[conanhhy]

1. 建立索引
2. 试试下面得语句，我这边没试过
select * from register
join (select userid, count(userid) as giftcount from gift where state='ok') a on username = userid
where b.giftcount == 9

[conanhhy]

刚才那句SQL少了一些代码
select * from register
join (select userid, count(userid) as giftcount from gift where state='ok' group by userid) a on username = userid
where b.giftcount == 9

[灰太狼是程序员]

我先谢谢大家的支持，上面的都不正确

[灰太狼是程序员]

大家看一下我的SQL语句，我中间用了很多OR，所以效率很慢