Question

我有一个搜索页,试图在两张桌子上建立一个复杂的搜索条件,上面看一看:

Users
ID     NAME
1      Paul
2      Remy

......

Profiles
FK_USERS_ID   TOPIC      TOPIC ID
1             language   1
1             language   2
1             expertise  1
1             expertise  2
1             expertise  3
2             language   1
2             language   2

The second table Profiles, lists the "languages" or the "expertises" (among other stuff) of each user, and topic id is a foreign key to another table depending on the topic (if topic is "language", than topic ID is the ID of a language in the languages table, etc......).

搜索需要找到像<代码”这样的东西,即用户名称和数字表示/编码,用户使用的“具有”语言1,有语言2,有专长1,有专门知识2。

任何帮助都会得到真正赞赏! 我在两个表格上填写了LEFT JOIN,尽管我不相信这是正确的选择。我的主要问题是“任务”。同一用户必须拥有1和2种语文,同时拥有1和2种专门知识。

我在PHP工作,我通常设法避免内部选任考试,甚至加入选举,但我认为这里即将出现内部选任考试?

Answer 1

你们可以通过建立一套与你简介表的标准相匹配的用户来做到这一点,例如:

SELECT FK_USERS_ID 
FROM Profiles 
WHERE topic= x  
AND TOPIC_ID IN (1,2) 
GROUP BY FK_USERS_ID 
HAVING COUNT(1) = 2

这里,你列出了与你需要的专题相对应的用户。通过用户分类,并具体说明应退还的行数,你可以有效地说“只有那些在专题z中具有x和 y的行文。有理由确保COUNT(1) = x拥有相同数目的不同接触机会。

然后,你可以查询用户表。

SELECT ID 
FROM Users 
WHERE name like  %PAU%  
AND ID IN (<insert above query here>)

你们也可以在加入和衍生的表格中这样做,但上文应当解释其本质。

EDIT: if you are looking for multiple combinations, you can use mysql s multi-column IN:

SELECT FK_USERS_ID 
FROM Profiles 
WHERE (topic,topic_id) IN (( x ,3),( x ,5),( y ,3),( y ,6))
GROUP BY FK_USERS_ID 
HAVING COUNT(1) = 4

这将寻找配对奶粉的用途,X-3、x-5、 y-3和 y-6。

你们应当能够方便地在实验室中建造专题节目——乳制品,把它装在地板上,并且仅仅把你产生的奶制品数量计算成一个变量,用于计算(1)号。见采用这种办法进行业绩讨论。

Answer 2

难道这只是一个简单的缩略语。

SELECT
  p.topic, p.topic_id
FROM
  profiles p
INNER JOIN
  users u
ON
  u.id = p.fk_users_id
WHERE
  u.name LIKE  %Paul%

这一询问将使符合模式的用户能够把所有语文和专长与他们的身份证相传,在这种情况下,以他们的名列保罗。这是你们喜欢的吗? 或者说什么?

Answer 3

select *
from users u, profiles p
where u.id = p.fk_users_id
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic =  language  
            and topic_id = 1)
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic =  language  
            and topic_id = 22)
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic =  expertise  
            and topic_id = 1)
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic =  expertise  
            and topic_id = 1)
and u.name like  %PAU%

EDIT:

Ok在@cairnz的答复中略有改动:

SELECT ID 
FROM Users 
WHERE name like  %PAU%  
AND ID IN (SELECT FK_USERS_ID 
           FROM Profiles 
           WHERE topic= x  
           AND ((TOPIC_ID = 1 AND TOPIC =  language ) 
                OR (TOPIC_ID = 2 AND TOPIC =  language )
                OR (TOPIC_ID = 1 AND TOPIC =  expertise )
                OR (TOPIC_ID = 2 AND TOPIC =  expertise ))
           GROUP BY FK_USERS_ID 
           HAVING COUNT(1) = 4)

Answer 4

我将根据日本国际海洋网络的每况“要求”的多次条件来做。我还将确保根据主要研究的每个部分编制概况表索引。

SELECT STRAIGHT_JOIN
      U.ID 
   FROM Users U
      JOIN Profiles P1
         on U.ID = P1.FK_User_ID
         AND P1.Topic_Id = 1
         AND P1.Topic = "language"
      JOIN Profiles P2
         on U.ID = P2.FK_User_ID
         AND P2.Topic_Id = 2
         AND P2.Topic = "language"
      JOIN Profiles P3
         on U.ID = P3.FK_User_ID
         AND P3.Topic_Id = 1
         AND P3.Topic = "expertise"
      JOIN Profiles P4
         on U.ID = P4.FK_User_ID
         AND P4.Topic_Id = 2
         AND P4.Topic = "expertise"
   WHERE
      u.name like  %PAU%

这样,所提供的其他答复中表达的任何额外标准都不会太大影响。这些表格的标准是,如果同时填写,如果丢失,将立即将其排除在结果之外,而不是试图为每个条目进行次选举(我认为这可能是你遇到的滞后)。

So, each of your "required" criteria would take the same "JOIN" construct, and as you can see, I m just incrementing the "alias" of the join instance.

友情链接