我正在对两个表进行一个简单的查询,并试图计算如果不将它们分组,我会得到相同ID的记录的数量。这些是表格:
TABLE作业
id--type--user_id--field_id
1--涂装-53-12
2-读取-53-12
3--写入-53-12
TABLE活动
id--is_test--user_id--field_id
7——是——53——12
8--编号-53-12
通过这个查询,我从表jobs
(绘画)中获得了我想要的单个记录,因为我在列jobs.id
上使用了GROUP BY
子句。如果我不使用GROUP BY,我会得到2条记录,因为对于相同的user_id
和field_id
有2个匹配的活动。
SELECT `jobs`.*,`activities`.`is_test`
FROM `jobs`
LEFT JOIN `activities` ON `activities`.`user_id`=`jobs`.`user_id`
WHERE `jobs`.`field_id`=12 AND `activities`.`field_id`=12 AND `jobs`.`type`='painting' AND `jobs`.`user_id`=53
GROUP BY `jobs`.`id`
ORDER BY `jobs`.`id` DESC;
我要做的是获取另一列,该列包含表activities
中匹配记录的实际数量。基本上,我想在具有相同user_id
和field_id
的activities
中获得2作为记录计数。
我试着选择这样的新列:
SELECT `jobs`.*,`activities`.`is_test`, COUNT(DISTINCT `jobs`.`id`) AS `tot_act`
但是对于新列CCD_ 10,这显然返回1而不是2。使其计数字段activities.is_test
也不起作用。
我试图避免子选择。
请不要注意activities.is_test
列,我不想看它是设置为"是"还是"否",我只想计算出有一条匹配的记录是"否"的,所以如果我不分组,总共有2条记录2是我试图将作为一个新列检索的值。
任何想法都将不胜感激!
您的第一个查询没有任何意义。Group by仅具有与输出列中的聚合函数组合的含义(select子句)。
activities.field_id上的where筛选器(昂贵地)将左联接转换为内部联接。
我还怀疑你的模式没有正常化。
获取包含表活动中匹配记录的实际数量的另一列
SELECT `jobs`.id, jobs.type, jobs.user_id,
Jobs.field_id,`activities`.`is_test`,
SUM(IF(activities.id IS NULL, 0,1))
FROM `jobs`
LEFT JOIN `activities`
ON `activities`.`user_id`=`jobs`.`user_id`
AND activities.field_id=12
WHERE `jobs`.`field_id`=12
AND `jobs`.`type`='painting'
AND `jobs`.`user_id`=53
GROUP BY `jobs`.id, jobs.type, jobs.user_id,
Jobs.field_id,`activities`.`is_test`
ORDER BY `jobs`.`id` DESC;
COUNT()遇到的每一个NON NULL值递增1。
在您的情况下,您需要的是活动表中的行数,而不是作业表。
SELECT
jobs.id
, jobs.type
, jobs.user_id
, Jobs.field_id
, COUNT(activities.id) activity_count
FROM jobs
LEFT JOIN activities ON jobs.user_id = activities.user_id
AND activities.field_id = 12
WHERE jobs.field_id = 12
AND jobs.type = 'painting'
AND jobs.user_id = 53
GROUP BY
jobs.id
, jobs.type
, jobs.user_id
, Jobs.field_id
ORDER BY
jobs.id DESC;
从您的样本数据中,查询产生以下结果:
| id | type | user_id | field_id | activity_count |
|----|----------|---------|----------|----------------|
| 1 | painting | 53 | 12 | 2 |
但是请注意,如果在select和grouping子句中包含activity.is_test,则不会得到2的计数。
请参阅此sqlfiddle
SELECT
`jobs`.id,
`jobs`.type,
`jobs`.user_id,
`jobs`.field_id,
count(1) as activities_count,
max(`activities`.is_test) as is_test
FROM
`jobs`
LEFT OUTER JOIN
`activities` ON
`activities`.user_id = `jobs`.user_id
AND `activities`.field_id = `jobs`.field_id
WHERE
`jobs`.field_id = 12
AND `jobs`.type = 'painting'
AND `jobs`.user_id = 53
GROUP BY
`jobs`.id,
`jobs`.type,
`jobs`.user_id,
`jobs`.field_id