值是动态的,可以或多或少。如果任何值为真,我将得到结果.但是如果数组中的所有值都为真,我想获取记录记录。重要提示:数组中的值是动态的
SELECT J.ID , J.U_POST_ID,
J.TITLE,J.CREATION_DATE,J.STATUS,
R.FIRST_NAME, R.LAST_NAME,R.CLINICAL_CLINIC_NAME,
J.REQUIREMENT,J.STATE,J.CITY,J.DESCRIPTION,J.CALL_DUR,J.USER_ID
FROM df_job_meta M JOIN df_job_post J ON
M.JOB_ID = J.ID JOIN df_register_users R ON
R.ID = J.USER_ID WHERE J.STATUS='ACTIVE' AND R.OCCUPATION !='student' AND M.VALUE IN ('Fresher','Full Time') group BY J.ID
表:df_job_meta
--------------------------------------
|JOB_ID | META_KEY | VALUE |
-------------------------------------|
| 91 | experience |Fresher |
| 91 | work_hour |Full Time |
| 91 | pay_scale |Sharing |
| 91 | gender |Female |
| 92 | experience |3 to 5 Years |
| 92 | work_hour |Part Time |
---------------------------------------
我认为您需要的是将返回的记录数与IN
子句中的条件数相匹配。在这种情况下,基本上使用
SELECT M.JOB_ID
FROM df_job_meta M
WHERE M.VALUE IN ('Fresher','Full Time')
GROUP BY M.JOB_ID
HAVING COUNT(DISTINCT M.VALUE) = 2
数组中有 2 个值,因此计数必须等于 2。如果您有 N 个值,则查询应类似于
SELECT M.JOB_ID
FROM df_job_meta M
WHERE M.VALUE IN (1,2,...,N)
GROUP BY M.JOB_ID
HAVING COUNT(DISTINCT M.VALUE) = N
您可以使用 php 实现该部分,因为您有一个动态数组。当然,您需要将该查询与上面的主查询联接。
编辑
如果您没有每个JOB_ID
具有相同VALUE
的重复行,您可以简单地执行COUNT(M.VALUE)
而不是COUNT(DISTINCT M.VALUE)
编辑 2
我还包含了完整的 sql 语句。(由于未提供 ddl,可能有错误)
SELECT J.ID , J.U_POST_ID,
J.TITLE,J.CREATION_DATE,J.STATUS,
R.FIRST_NAME, R.LAST_NAME,R.CLINICAL_CLINIC_NAME,
J.REQUIREMENT,J.STATE,J.CITY,J.DESCRIPTION,J.CALL_DUR,J.USER_ID
FROM df_job_post J JOIN df_register_users R ON R.ID = J.USER_ID
WHERE J.STATUS='ACTIVE' AND R.OCCUPATION !='student'
AND J.ID IN
(
SELECT M.JOB_ID
FROM df_job_meta M
WHERE M.VALUE IN ('Fresher','Full Time')
GROUP BY M.JOB_ID
HAVING COUNT(DISTINCT M.VALUE) = 2
)
编辑 3
为了精确匹配,您可以将查询更改为
SELECT J.ID , J.U_POST_ID,
J.TITLE,J.CREATION_DATE,J.STATUS,
R.FIRST_NAME, R.LAST_NAME,R.CLINICAL_CLINIC_NAME,
J.REQUIREMENT,J.STATE,J.CITY,J.DESCRIPTION,J.CALL_DUR,J.USER_ID
FROM df_job_post J JOIN df_register_users R ON R.ID = J.USER_ID
WHERE J.STATUS='ACTIVE' AND R.OCCUPATION !='student'
AND J.ID IN
(
SELECT M.JOB_ID
FROM df_job_meta M
WHERE M.VALUE IN ('Fresher','Full Time')
GROUP BY M.JOB_ID
HAVING COUNT(DISTINCT M.VALUE) = 2 AND COUNT(DISTINCT M.VALUE) = (SELECT COUNT( * ) FROM df_job_meta WHERE JOB_ID = M.JOB_ID)
)
您基本上是通过以下方式检查返回的行计数是否等于实际计数:
COUNT(DISTINCT M.VALUE) = (SELECT COUNT( * ) FROM df_job_meta WHERE JOB_ID = M.JOB_ID)
如果您使用的是上述查询,请使用此方法:
$q = mysql_query("SELECT J.ID , J.U_POST_ID,
J.TITLE,J.CREATION_DATE,J.STATUS,
R.FIRST_NAME, R.LAST_NAME,R.CLINICAL_CLINIC_NAME,
J.REQUIREMENT,J.STATE,J.CITY,J.DESCRIPTION,J.CALL_DUR,J.USER_ID
FROM df_job_meta M JOIN df_job_post J ON
M.JOB_ID = J.ID JOIN df_register_users R ON
R.ID = J.USER_ID WHERE J.STATUS='ACTIVE' AND R.OCCUPATION !='student' AND M.VALUE IN ('Fresher','Full Time') group BY J.ID");
while($r=mysql_fetch_array($q))
{
echo $r['JOB_ID'].$r['META_KEY'].$r['VALUE'];
}