我有这样的情况。
元值表
ID | question_id | m_id | m_value
----------------------------------
1 | 1 | 1 | Easy
2 | 1 | 2 | Physics
3 | 2 | 1 | Hard
4 | 2 | 2 | English
元表
ID | Meta
------------
1 | Difficulty
2 | Subject
我想编写一个存储过程，当Difficulty = Easy AND Subject = English时选择一行。有任何想法吗。？
这是我到目前为止所做的
BEGIN
SELECT
a.q_id,
a.q_text,
b.answer ,
IF(
c.m_id=1 AND c.m_value = d,
c.m_value,
IF(
c.m_id=2 AND c.m_value=s,
m_value,
m_value
)
) as selection
FROM
question_pool AS a,
answers_table AS b,
meta_prop_values AS c
WHERE
a.q_id = b.q_id
AND
a.q_id = c.q_id
ORDER BY
Rand() LIMIT n;
这是输出。但这不是我想要的
q_id q_text answer selection
--------------------------------------------------------------------------
7 <p>Another difficult question for English</p> Difficult 3 0
12 <p>Physics Equations 4 <span class="math-tex">\(x = {{b^2-4ac} \over 2a}\)</span></p> Easy 2 Physics
14 <p>Find values of x from <span class="math-tex">\(x = a^2 + b^2 - c\)</span> when a =5 b = 5 c= 10</p> 14 Hard
12 <p>Physics Equations 4 <span class="math-tex">\(x = {{b^2-4ac} \over 2a}\)</span></p> Easy 1 Physics
6 <p>This is a easy english question</p> Easy 03 0
6 <p>This is a easy english question</p> Easy O2 English
16 <p>Find values of x from <span class="math-tex">\(x = a^2 + b^2 - c\)</span> when a =20 b = 20 c=-40</p> 457 0
5 <p>This is another Question with Demo data</p> Nothing 0
5 <p>This is another Question with Demo data</p> Cravitto English
15 <p>Find values of x from <span class="math-tex">\(x = a^2 + b^2 - c\)</span> when a =10 b = 10 c=-40</p> 45 Hard
15 <p>Find values of x from <span class="math-tex">\(x = a^2 + b^2 - c\)</span> when a =10 b = 10 c=-40</p> 45 0
15 <p>Find values of x from <span class="math-tex">\(x = a^2 + b^2 - c\)</span> when a =10 b = 10 c=-40</p> 43 0
9 <p>Physics Equations 1 <span class="math-tex">\(x = {-b \pm \sqrt{b^2-4ac} \over 2a}\)</span></p> Easy 4 0
15 <p>Find values of x from <span class="math-tex">\(x = a^2 + b^2 - c\)</span> when a =10 b = 10 c=-40</p> 54 Mathematics
9 <p>Physics Equations 1 <span class="math-tex">\(x = {-b \pm \sqrt{b^2-4ac} \over 2a}\)</span></p> Easy 1 Medium
5 <p>This is another Question with Demo data</p> Junaid Rasheed Easy
2 <p>Testing <span class="math-tex">\(x = {-b \pm \sqrt{b^2-4ac} \over 2a}\)</span></p> 100 0
17 <p>Find values of x from <span class="math-tex">\(x = a^2 + b^2 - c\)</span> when a =50 b = 50 c=-40</p> 435 0
16 <p>Find values of x from <span class="math-tex">\(x = a^2 + b^2 - c\)</span> when a =20 b = 20 c=-40</p> 457 Hard
8 <p>A hard question for English</p> Hard 3 Medium
最佳答案
如果您只想知道问题编号。这将是一种方式。
SELECT Question_ID
FROM Meta_values V
INNER JOIN Meta_Table T
on V.m_ID = T.ID
WHERE m_value in ('Easy','English')
GROUP BY Question_ID
HAVING count(*)=2
请注意，只要您始终知道要查找的m_values数量（将2设置为该值），就会动态传递m_value和= 2。
如果需要更多数据，请将其用作现有数据或内联视图联接以将其限制为仅与该结果集匹配的问题。