从MySQL表中选择，按匹配的类别数排序 [英] Select from MySQL table, ordered by the matching number of categories

问题描述

我有一张桌子，像这样:

I have a table, like this:

id:int | name:String | categories:String

示例行:

1 | "Lorem1" | "A, B, C" 
2 | "Lorem2" | "A, B" 
3 | "Lorem3" | "A, C" 
4 | "Lorem4" | "B" 

我还有一个表格，您可以在其中检查u所涉及的类别.这应该是选择顺序的指南.

I also have a form, where you can check the categories which u are intrested in. This should be the guide for the order of the select.

首先返回具有所有选定类别的行，然后获得具有较少匹配项的行. (如果该行没有任何类别，则不会显示)

First you get back the rows, that has all the selected categories, then you get which has, less match. (If the row has none of the categories, it won't show up)

例如有人检查:

• A和B，它们应按以下顺序返回行:Lorem1，Lorem2，Lorem3，Lorem 4

• A and B, they should get back the rows in this order: Lorem1, Lorem2, Lorem3, Lorem 4

A和C，它们应按以下顺序返回行:Lorem1，Lorem3，Lorem2

A and C, they should get back the rows in this order: Lorem1, Lorem3, Lorem2

这就是我要做的.我对编程很陌生，并且出现了这个问题.

This is what I'm trying to make. I am quite new to programing, and this problem showed up.

我也知道，也许我应该为类别和对象之间的连接创建一个新表.

I also know, maybe I should make a new table for the connections between the categories, and the objects.

推荐答案

您数据的规范化版本可能是:

A normalized version of you data could be:

create table items (
  id int,
  name varchar(50),
  primary key (id),
  index (name)
);

create table categories (
  id int,
  name varchar(50),
  primary key (id),
  index (name)
);

create table items_categories (
  item_id int,
  category_id int,
  primary key (item_id, category_id),
  index (category_id, item_id),
  foreign key (item_id) references items(id),
  foreign key (category_id) references categories(id)
);

insert into items (id, name) values
  (1, 'Lorem1'),
  (2, 'Lorem2'),
  (3, 'Lorem3'),
  (4, 'Lorem4');

insert into categories (id, name) values
  (1, 'A'),
  (2, 'B'),
  (3, 'C'),
  (4, 'D');

insert into items_categories (item_id, category_id) values
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 1),
  (2, 2),
  (3, 1),
  (3, 3),
  (4, 2);

现在-当您搜索类别"A"和"B"中的项目时，SELECT查询将是:

Now - When you search for items in categories 'A' and 'B', the SELECT query would be:

select i.*, count(*) as matches
from items i
join items_categories ic on ic.item_id = i.id
join categories c on c.id = ic.category_id
where c.name in ('A', 'B')
group by i.id
order by matches desc, i.name;

结果:

| id  | name   | matches |
| --- | ------ | ------- |
| 1   | Lorem1 | 2       |
| 2   | Lorem2 | 2       |
| 3   | Lorem3 | 1       |
| 4   | Lorem4 | 1       |

如果要搜索类别"A"和"C"，请将WHERE子句更改为

If you want to search in categories 'A' and 'C', change the WHERE clause to

where c.name in ('A', 'C')

结果将是:

| id  | name   | matches |
| --- | ------ | ------- |
| 1   | Lorem1 | 2       |
| 3   | Lorem3 | 2       |
| 2   | Lorem2 | 1       |

在DB Fiddle上查看

您甚至可以使用以下方式模拟"您的原始架构:

You can even "emulate" your original schema with

select i.*, group_concat(c.name separator ', ') as categories
from items i
join items_categories ic on ic.item_id = i.id
join categories c on c.id = ic.category_id
group by i.id

结果:

| id  | name   | categories |
| --- | ------ | ---------- |
| 1   | Lorem1 | A, B, C    |
| 2   | Lorem2 | A, B       |
| 3   | Lorem3 | A, C       |
| 4   | Lorem4 | B          |

反之则很难. (对我而言)这是使用规范化架构的主要原因.

It would be much harder to do it the other way round. That is (for me) a major reason to use a normalized schema.

一本好书:是将分隔列表存储在数据库列中真的不好吗?

这篇关于从MySQL表中选择，按匹配的类别数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！