现在有产品表 product
pid '产品id
cid '公司id
name '产品名现在要求是这样的,求出,符合条件 name like '%aaa%' 的记录,但每个公司只能出一条我现在是这样实现的,先求出每个公司符合条件的最大的记录,组成一个集合,作为子查询,然后再求出详细信息如下select * from product
where pid in
(
select max(pid) as maxid
from product
where name like '%aaa%'
group by comid
limit 0,20
)
问题来了,我现在用的是mysql 4.1
他死活不让我在子查询中,用limit 0,20
如果把limit 0,20 放在外面的话,效率又极低谁能帮帮我,谢谢
pid '产品id
cid '公司id
name '产品名现在要求是这样的,求出,符合条件 name like '%aaa%' 的记录,但每个公司只能出一条我现在是这样实现的,先求出每个公司符合条件的最大的记录,组成一个集合,作为子查询,然后再求出详细信息如下select * from product
where pid in
(
select max(pid) as maxid
from product
where name like '%aaa%'
group by comid
limit 0,20
)
问题来了,我现在用的是mysql 4.1
他死活不让我在子查询中,用limit 0,20
如果把limit 0,20 放在外面的话,效率又极低谁能帮帮我,谢谢
from 表名 AS A
where [name] like '%aaa%'
and pid= (select top 1 pid from 表名 where cid=A.cid order by pid )
from 表名 AS A
where [name] like '%aaa%'
and pid= (select top 1 pid from 表名 where [name] like '%aaa%' and cid=A.cid order by pid )
select max(pid) as maxid
from product
where name like '%aaa%'
group by comid
的数目>20的话,那后20条不是没了?
如果是前20条的话为什么不加排序语句呢
select max(pid) as maxid
from product
where name like '%aaa%'
group by comid
limit 0,20之后在 select * from product
where pid in(字符串)
select max(pid) as maxid
from product
where name like '%aaa%'
group by comid
的数目>20的话,那后20条不是没了?
如果是前20条的话为什么不加排序语句呢
---------------------------------
我是为了分页啊
from 表名 AS A
where [name] like '%aaa%'
and pid= (select top 1 pid from 表名 where [name] like '%aaa%' and cid=A.cid order by pid )不好意思,你那个我测试下,速度奇慢啊
inner join
(select max(product.pid) as maxid
from product left join company on company.cid=product.cid
where
match(product.name) against('+light' in boolean mode)
and match(product.keywords) against('+light' in boolean mode)
and product.address='zhejiang'
and product.status=3
and product.rSGId=6
group by product.cId
order by (company.uGrade ) desc
limit 0,20) as virtual_table_2
on s.pid=virtual_table_2.maxid只要把从句也as一个表的话,问题就可以了
答者皆有分,我结了