本文介绍了如何从主查询中选择计数到子查询中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将主查询中选择的记录数放入子查询中
I want to get the number of records that are selected in the main query into a subquery
我当前尝试执行的查询是:
Query that i am currently trying to execute is:
SELECT cat_id,category_name,seo_name,(SELECT count(category_name) FROM ccs_coupons WHERE (category_name LIKE category_name OR website_name LIKE category_name OR description LIKE LIKE category_name OR url_desc LIKE category_name )) FROM `ccs_coupons` WHERE category_name like 'a%' GROUP BY category_name ORDER BY category_name ASC LIMIT $page,$config
推荐答案
你可以只使用两个表之间的连接而不是相关子查询:
You can just use a join between the two tables instead of the correlated subquery:
SELECT c.id,
u.display_name,
c.comments_count
FROM users u
LEFT JOIN
(
SELECT COUNT(*) comments_count, author_id
FROM cms_comments
GROUP BY author_id
) c
ON c.author_id = u.id
WHERE c.comments_count > 150;
编辑,根据你的更新,你应该仍然可以使用这样的东西:
Edit, based on your update, you should still be able to use something like this:
SELECT c1.cat_id,
c1.category_name,
c1.seo_name,
c2.CountCategoryName
FROM `ccs_coupons` c1
LEFT JOIN
(
SELECT count(category_name) CountCategoryName, category_name,
website_name, description, url_desc
FROM ccs_coupons
GROUP BY category_name
) c2
on c2.category_name LIKE c1.category_name
or c2.website_name LIKE c1.category_name
or c2.description LIKE c1.category_name
or c2.url_desc LIKE c1.category_name
WHERE c1.category_name like 'a%'
ORDER BY c1.category_name ASC
LIMIT $page,$config;
甚至使用:
SELECT c1.cat_id,
c1.category_name,
c1.seo_name,
count(c2.category_name)
FROM `ccs_coupons` c1
INNER JOIN ccs_coupons c2
on c2.category_name LIKE c1.category_name
or c2.website_name LIKE c1.category_name
or c2.description LIKE c1.category_name
or c2.url_desc LIKE c1.category_name
WHERE c1.category_name like 'a%'
GROUP BY c1.category_name
ORDER BY c1.category_name ASC
LIMIT $page,$config;
这篇关于如何从主查询中选择计数到子查询中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!