SQL - 另一个子查询
SELECT
*
FROM
(
SELECT
qu.job_id AS `qu_job_id` , qu.engineer_id, qu.id AS `quote_id` , jb.author, jb.job_id, jb.job_title, SUBSTRING(jb.job_description, 1, 200) AS `short_description` , jb.image_ref, jb.timestamp
FROM
jobs AS `jb`
LEFT JOIN
quotes AS `qu`
ON
qu.job_id = jb.job_id
WHERE
jb.author = " . ID . "
GROUP BY
jb.job_id
ORDER BY
jb.timestamp DESC
) AS `jobs`
LEFT JOIN
(
SELECT
COUNT(id) AS `total_replies` , job_id
FROM
quote_comments
WHERE
job_id = jobs.job_id
) AS `replies`
ON
replies.job_id = jobs.job_id
我的问题使用结果从子查询是否有可能(以上不作为是工作)把这个结果从第一子查询,以防止在第二比较? (即job_id = jobs.job_id
)SQL - 另一个子查询
问候
编辑
(这里作为评论有更多的空间)
SELECT
qu.engineer_id,
qu.id AS `quote_id`,
jb.author,
jb.job_id,
jb.job_title,
SUBSTRING(jb.job_description, 1, 200) AS `short_description` ,
jb.image_ref,
jb.timestamp,
count(qu.id) AS comment_count
FROM
" . JOBS . " AS `jb`
LEFT JOIN
" . QUOTES . " AS `qu`
ON
qu.job_id = jb.job_id
WHERE
jb.author = " . ID . "
GROUP BY
jb.job_id,
qu.engineer_id,
qu.id,
jb.author,
jb.job_title,
SUBSTRING(jb.job_description, 1, 200),
jb.image_ref,
jb.timestamp
ORDER BY
jb.timestamp DESC
我最初的问题是不正确略。我实际上想要得到报价的数量,而不是报价评论。
上述建议在下面看起来似乎乍一看,但它返回的工作有多个报价submited×次数与每个报价计数为1。有任何想法吗?
SELECT
qu.engineer_id,
qu.id AS `quote_id`,
jb.author,
jb.job_id,
jb.job_title,
SUBSTRING(jb.job_description, 1, 200) AS `short_description` ,
jb.image_ref,
jb.timestamp,
count(qc.id) AS comment_count
FROM
jobs AS `jb`
LEFT JOIN quotes AS `qu` ON qu.job_id = jb.job_id
LEFT JOIN quote_comments AS `qc` ON qu.job_id = qc.job_id
WHERE
jb.author = " . ID . "
GROUP BY
jb.job_id,
qu.engineer_id,
qu.id,
jb.author,
jb.job_title,
SUBSTRING(jb.job_description, 1, 200)
jb.image_ref,
jb.timestamp
ORDER BY
jb.timestamp DESC
这将消除子查询解析并使您的总体查询运行得更快。我删除了报价工作ID,因为我们正在加入,因此只需要拉一次。我注意到我们直接从工作中拼接引用评论。您可能希望通过报价,让你看到有多少评论每帖有多少报价评论,而不是有整体的工作联系起来
感谢上述小周,并像一个魅力工作。谢谢 – 2012-01-27 20:32:09
等等,不,我不挑剔,因为它没有完全回答,我会在问题中解释更多。 – 2012-01-27 20:37:44
如果你将一个表'A'加入到2个表'B'和'C'中,这两个表都与A有一对多的关系,然后你用A.something'做一个'group,在集合函数中会得到错误的结果。 – 2012-01-27 23:16:25
SELECT
jb.author,
jb.job_id,
jb.job_title,
SUBSTRING(jb.job_description, 1, 200) AS `short_description` ,
jb.image_ref,
jb.timestamp,
count(qu.id) AS quote_count
FROM
jobs AS `jb`
LEFT JOIN quotes AS `qu` ON qu.job_id = jb.job_id
WHERE
jb.author = " . ID . "
GROUP BY
jb.job_id,
jb.author,
jb.job_title,
SUBSTRING(jb.job_description, 1, 200)
jb.image_ref,
jb.timestamp
ORDER BY
jb.timestamp DESC
这将拉动由ID撰写,然后做的工作对提交的工作报价进行计数。 Unfortunateley获得报价作业的数量,你必须从报价表
编辑您的第一个查询离开过工程师ID和报价ID:
SELECT
*
FROM
( SELECT
qu.job_id AS `qu_job_id` , qu.engineer_id, qu.id AS `quote_id`,
jb.author, jb.job_id, jb.job_title,
SUBSTRING(jb.job_description, 1, 200) AS `short_description`,
jb.image_ref, jb.timestamp
FROM
jobs AS `jb`
LEFT JOIN
quotes AS `qu`
ON
qu.job_id = jb.job_id
WHERE
jb.author = " . ID . "
GROUP BY
jb.job_id
--- ORDER BY --- not needed
--- jb.timestamp DESC --- here
) AS `jobs`
LEFT JOIN
(
SELECT
COUNT(*) AS `total_replies` , job_id
FROM
quote_comments
--- WHERE
--- job_id = jobs.job_id
GROUP BY --- replacing the WHERE
job_id --- with a GROUP BY
) AS `replies`
ON
replies.job_id = jobs.job_id
ORDER BY --- order clause
timestamp DESC --- moved here
是的,它是可能的。首先,你不能在子查询中使用ORDER BY。这是你得到的错误吗? – 2012-01-27 20:06:42
它看起来像你试图得到一份工作,得到任何工作报价(或没有),以及作业的报价评论数。如果这是正确的,我想我知道答案。 – Hasteur 2012-01-27 20:11:37
我想要做的是从3个表中获取信息。所有这三个在comman,job_id和author_id中都有两个东西。我想要获得所有工作,并且还要获得1个查询中每个工作收到的评论数量。 (p.s.顺序工作正常) – 2012-01-27 20:11:44