是否有一个查询，根据存储在字段中以逗号分隔的值的计数，将结果作为排序结果集获取？

70gysomp 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(350)

嗨，我最近开始在symfony4上工作。我有4个表是按照eav规则设计的。eav\u entity、eav\u attribute、eav\u entity\u instance、eav\u value\u text包含以下字段。

mysql> select * from eav_entity;
+----+---------+---------+---------------------+---------------------+
| id | code    | label   | created_at          | updated_at          |
+----+---------+---------+---------------------+---------------------+
|  5 | user    | User    | 2020-05-07 21:53:48 | 2020-05-07 21:53:48 |
|  6 | project | Project | 2020-05-07 21:53:48 | 2020-05-07 21:53:48 |
+----+---------+---------+---------------------+---------------------+

mysql> select * from eav_attribute;
+----+-----------+-----------------+-----------+-----------+-------------+---------------------+---------------------+
| id | entity_id | code            | type      | is_unique | is_required | created_at          | updated_at          |
+----+-----------+-----------------+-----------+-----------+-------------+---------------------+---------------------+
| 33 |         5 | full_name       | text      |         0 |           1 | 2020-05-07 21:59:20 | 2020-05-07 21:59:20 |
| 34 |         5 | email           | text      |         1 |           1 | 2020-05-07 22:00:32 | 2020-05-07 22:00:32 |
| 35 |         5 | phone_number    | varchar   |         1 |           1 | 2020-05-07 22:00:51 | 2020-05-07 22:00:51 |
| 36 |         5 | password        | varchar   |         1 |           1 | 2020-05-07 22:01:02 | 2020-05-07 22:01:02 |
| 43 |         6 | project_name    | varchar   |         0 |           1 | 2020-05-11 00:49:41 | 2020-05-11 00:49:41 |
| 44 |         6 | project_id      | varchar   |         1 |           1 | 2020-05-11 00:49:41 | 2020-05-11 00:49:41 |
| 45 |         6 | project_manager | varchar   |         0 |           1 | 2020-05-11 00:49:41 | 2020-05-11 00:49:41 |
| 46 |         6 | players         | text      |         0 |           1 | 2020-05-11 00:49:41 | 2020-05-11 00:49:41 |
| 47 |         5 | is_admin        | boolean   |         0 |           0 | 2020-05-12 10:21:33 | 2020-05-12 10:21:33 |
| 49 |         5 | is_enabled      | boolean   |         0 |           0 | 2020-05-16 17:34:03 | 2020-05-16 17:34:03 |
| 51 |         5 | last_activity   | timestamp |         0 |           0 | 2020-05-16 22:20:45 | 2020-05-16 22:20:45 |
| 58 |         5 | experience      | varchar   |         0 |           0 | 2020-05-18 18:26:30 | 2020-05-18 18:26:30 |
| 59 |         5 | branch          | varchar   |         0 |           0 | 2020-05-18 22:18:53 | 2020-05-18 22:18:53 |
+----+-----------+-----------------+-----------+-----------+-------------+---------------------+---------------------+

mysql> select * from eav_entity_instance;
+----+-----------+---------------------+---------------------+
| id | entity_id | created_at          | updated_at          |
+----+-----------+---------------------+---------------------+
| 38 |         5 | 2020-05-16 22:21:50 | 2020-05-16 22:21:50 |
| 39 |         5 | 2020-05-17 21:52:03 | 2020-05-17 21:52:03 |
| 40 |         5 | 2020-05-17 21:53:10 | 2020-05-17 21:53:10 |
| 41 |         6 | 2020-05-17 21:57:24 | 2020-05-17 21:57:24 |
| 42 |         6 | 2020-05-17 22:20:38 | 2020-05-17 22:20:38 |
| 53 |         5 | 2020-05-19 21:47:32 | 2020-05-19 21:47:32 |
| 54 |         5 | 2020-05-19 21:49:07 | 2020-05-19 21:49:07 |
| 55 |         5 | 2020-05-19 21:49:09 | 2020-05-19 21:49:09 |
| 56 |         5 | 2020-05-20 20:57:05 | 2020-05-20 20:57:05 |
+----+-----------+---------------------+---------------------+

mysql> select * from eav_value_text;
+----+-------------+--------------+----------------------+---------------------+---------------------+
| id | instance_id | attribute_id | value                | created_at          | updated_at          |
+----+-------------+--------------+----------------------+---------------------+---------------------+
| 63 |          38 |           33 | Raj                  | 2020-05-16 22:21:50 | 2020-05-16 22:21:50 |
| 64 |          38 |           34 | raj@gmail.com        | 2020-05-16 22:21:50 | 2020-05-16 22:21:50 |
| 65 |          39 |           33 | Ank                  | 2020-05-17 21:52:03 | 2020-05-17 21:52:03 |
| 66 |          39 |           34 | ank@gmail.com        | 2020-05-17 21:52:03 | 2020-05-17 21:52:03 |
| 67 |          40 |           33 | Bas                  | 2020-05-17 21:53:10 | 2020-05-17 21:53:10 |
| 68 |          40 |           34 | bas@gmail.com        | 2020-05-17 21:53:10 | 2020-05-17 21:53:10 |
| 69 |          41 |           46 | 38                   | 2020-05-17 21:57:24 | 2020-05-17 21:57:24 |
| 70 |          42 |           46 | 38,39                | 2020-05-17 22:20:38 | 2020-05-17 22:20:38 |
| 81 |          53 |           34 | vij@gmail.com        | 2020-05-19 21:47:32 | 2020-05-19 21:47:32 |
| 82 |          54 |           34 | abd@gmail.com        | 2020-05-19 21:49:07 | 2020-05-19 21:49:07 |
| 83 |          55 |           34 | jam@gmail.com        | 2020-05-19 21:49:09 | 2020-05-19 21:49:09 |
| 84 |          53 |           33 | Vij                  | 2020-05-19 21:59:35 | 2020-05-19 21:59:35 |
| 85 |          54 |           33 | Abd                  | 2020-05-19 22:04:59 | 2020-05-19 22:04:59 |
| 86 |          56 |           34 | raja@gmail.com       | 2020-05-20 20:57:05 | 2020-05-20 20:57:05 |
| 87 |          55 |           33 | Jam                  | 2020-05-21 16:07:30 | 2020-05-21 16:07:30 |
+----+-------------+--------------+----------------------+---------------------+---------------------+

project和user是两个具有不同属性的实体。
在eav\u entity\u示例中，您可以看到用户和雇员具有不同的属性\u id。
我的问题是我需要生成一个查询，以便以一种没有用户参与的项目的方式返回用户的instance\ id。
在eav\ U属性表中有一个名为players的属性，其id为46。
它的值存储在eav\u value\u文本表中，其中用户的instance\u id用逗号分隔。
有谁能帮我查询一下，使每个示例的\u id以逗号分隔的值进行匹配，计算每个用户参与了多少个项目，并按计数的顺序返回结果。
例如，instance\u id 38是user的，在instance\u id为41和42的两个项目中重复。同样地，示例id为39的用户只在一个值42中找到。
因此，由于示例id为38的用户有2个项目，而示例id为1的用户有1个项目，所以如果我们在asc中对其排序，结果应该如下所示，如果我们在desc中对其排序，结果应该相反。

+---+
|id +
+---+
|38 |
|39 |
+---+

sql mysql php symfony4

来源：https://stackoverflow.com/questions/61939645/is-there-a-query-to-get-result-as-sorted-result-set-based-on-the-count-of-value

1条答案

按热度按时间

fxnxkyjh1#

在sql server中，您可以使用函数string\ u split，但通过快速的google搜索发现，mysql中不存在该函数。但是我的搜索结果确实指向了这个站点，您可以通过以下方式使用这些信息来解决您的问题（sql fiddle）：

CREATE TABLE ProjectMembers 
    ( instance_id int
    , value varchar(500)
    );
INSERT INTO ProjectMembers(instance_id, value)
VALUES    (41, '38')
        , (42, '38,39');

/* Be sure that the amount of numbers in this table is at least as long as the 
   maximum amount of project members in a project */
CREATE TABLE Numbers
  (Number int);
INSERT INTO Numbers(Number)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT Member, COUNT(*) AS Nr FROM ( 
  SELECT 
    instance_id, 
    substring_index(
      substring_index(value, ',', Number), 
      ','
      , -1
    ) AS Member
  FROM ProjectMembers
  JOIN Numbers
    ON char_length(value) 
      - char_length(replace(value, ',', '')) 
      >= Number - 1
) t
GROUP BY 1
ORDER BY Nr DESC;

赞(0）回复(0）举报 2021-08-09

我来回答

是否有一个查询，根据存储在字段中以逗号分隔的值的计数，将结果作为排序结果集获取？

1条答案

相关问题

热门标签

最新问答