我有3张桌子,这是我的1张桌子,成员:

+---------+
| user_id |
+---------+
|       1 |
+---------+

and my table 2,Members7:

+---------------+---------------------------+---------+
| TotalWorkYear |         JobScope          | user_id |
+---------------+---------------------------+---------+
|             1 | Financial;pharmacy        |       1 |
|             6 | Doctor/Diagnosis;pharmacy |       1 |
|            10 | Accounting;pharmacy       |       1 |
+---------------+---------------------------+---------+

And table 3,Members7_2:

+-----------------+----------------+---------+
| TotalWorkYear_2 | KnowledgeSkill | user_id |
+-----------------+----------------+---------+
|               1 | abc;def;       |       1 |
|               6 | vw;xyz;def     |       1 |
|              10 | vw;xyz;        |       1 |
+-----------------+----------------+---------+

I need to find out the user_id that regexp 'pharmacy' in JobScope of Members7,and the total years of JobScope that regexp pharmacy is >=0,and also regexp 'def' in KnowledgeSkill of Members7_2,and the total years of KnowledgeSkill that regexp 'def' is >=0,

so now we can see that:

TotalWorkYear = 17

TotalWorkYear_2 = 7

user_id = '1'


这是我的查询:

SELECT Members.user_id,
SUM(IF(Members7.JobScope
REGEXP'Pharmacy',Members7.TotalWorkYear,0))年JobScope,Members7.JobScope AS JobScope,
SUM(IF(Member7_2.KnowledgeSkill
REGEXP'def',Member7_2.TotalWorkYear_2,0))yearsSkills,Members7_2.KnowledgeSkill AS KnowledgeSkill
来自会员
左加入Members7 ON Members.user_id = Members7.user_id
左加入Member7_2 ON Members.user_id = Members7_2.user_id
GROUP BY JobScope REGEXP'药房',
KnowledgeSkill REGEXP'def',user_id
拥有
JobScope REGEXP“药房”
AND yearsJobScope> = 0
AND KnowledgeSkill REGEXP'def'
AND yearsSkill> = 0


但输出为:

+ --------------- ++ ------------ +
| yearsJobScope |年技能|
+ --------------- ++ ------------ +
| 17 | 18 |
+ --------------- ++ ------------ +


如果我在HAVING下删除了REGEXP,我会看到:

+ --------- + --------------- + ----------------------- ---------------------- + ------------ + -------------- -+
| user_id | yearsJobScope |工作范围|年技能|知识技能|
+ --------- + --------------- + ----------------------- ---------------------- + ------------ + -------------- -+
| 1 | 1 |审计与税务;银行/金融;制药6 |大众; xyz; def |
| 1 | 6 |医生/诊断;药学| 6 |大众; xyz; def |
| 1 | 10 |通用/成本会计;药学| 6 |大众; xyz; def |
+ --------- + --------------- + ----------------------- ---------------------- + ------------ + -------------- -+


似乎只是对3行的TotalWorkYears求和。

有什么办法解决吗?

最佳答案

您与多个表的左连接将使一些技能计数不止一次。重写查询的简单方法是使用其他表的子查询。

SELECT m.user_id, yearsjobscope, yearsskill
FROM members m
JOIN (
  SELECT user_id, SUM(totalworkyear) yearsjobscope
  FROM members7 WHERE jobscope REGEXP 'Pharmacy'
  GROUP BY user_id) m7
  ON m.user_id = m7.user_id
JOIN (
  SELECT user_id, SUM(totalworkyear_2) yearsskill
  FROM members7_2 WHERE KnowledgeSkill REGEXP 'def'
  GROUP BY user_id) m7_2
  ON m.user_id = m7_2.user_id


An SQLfiddle to test with

最后,如果表变大,REGEX并不是一个好方法,那么您至少要将用分号分隔的值分成几行,以便索引/精确匹配可以覆盖它们。

关于mysql - REGEX特定字符的LEFT JOIN表和SUM值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17563693/

10-10 09:24