mysql查询获取列中每个元素的计数

i have a table called category in which i have main category ids and names and each main category has sub category ids and names.i also have a product table with a category id column which either has a numeric sub category id or a 2 letter main category id eg:EL for electronics..my problem is how to get top categories ie., number of products in each category in descending order.

我有一个名为类别的表,其中我有主类别ID和名称,每个主类别有子类别ID和名称。我还有一个产品表,其类别ID列具有数字子类别ID或2字母主category id例如:EL for electronics ..我的问题是如何获得最高类别,即每个类别中的产品数量按降序排列。

category
{
sub_cat_id - numeric
sub_cat_name - varchar
main_cat_id - varchar (2 characters)
main_cat_name
}
products
{
categoryid,//this can be either main_cat_id or sub_cat_id 
}

pls help....

3 个解决方案

#1

if there is no namespace * between main category id and sub category id, you could :

如果主类别ID和子类别ID之间没有命名空间冲突,您可以:

select main_cat_id , count(*) as total
from category
where ( main_cat_id in (select categoryid from products) 
                       OR 
       sub_cat_id in (select categoryid from products)
       )
group by main_cat_id 
order by total desc

however , prima facie there seems to be a problem with the design of the category table. sub_cat should be a different table with appropriate constraints.

然而,初步看来,类别表的设计似乎存在问题。 sub_cat应该是具有适当约束的不同表。

#2

Seems like it should be straight-forward, can you post the "CREATE TABLE" statements and a few sample rows (I wasn't able to "reverse engineer" the "CREATE TABLE" from your syntax above...)

看起来它应该是直截了当的,你可以发布“CREATE TABLE”语句和一些示例行(我无法从上面的语法中“反向工程”“CREATE TABLE”...)

#3

Actually, I don't think you can do that with a single query. Due to the double nature of column categoryid in the table products (i.e. that it may be a foreign key or a category name), you'd have to somehow combine that column with the main_cat_id column of the table category following a join, and do a GROUP BY on the merged column, but that is not possible, AFAIK.

实际上,我不认为你可以用一个查询来做到这一点。由于表产品中列categoryid的双重性质(即它可能是外键或类别名称),您必须以某种方式将该列与连接后的表类别的main_cat_id列组合,并且合并列上的GROUP BY,但这是不可能的,AFAIK。

You can, of course, do two separate SQL queries (one for counting products directly in main categories, and another one for counting those in subcategories), and combine their results with some server side scripting.

当然,您可以执行两个单独的SQL查询(一个用于直接在主要类别中计算产品,另一个用于计算子类别中的产品),并将其结果与某些服务器端脚本组合。

#1