你可以写一个subquery
,在你的start到end次之间得到你的job COUNT
,同时写一个GROUP_CONCAT
,把你的所有jobs分组到一个逗号分隔的行中:
SELECT jobs AS "Jobs", job_count AS "Job Count"
FROM
(
SELECT
1,
GROUP_CONCAT(job SEPARATOR ', ') as jobs,
COUNT(job) AS job_count
FROM
sample_table
WHERE
start_time >= '1:00'
AND
end_time <= '4:00'
GROUP BY
1
) a
Result:个个
Jobs | Job Count |
---|---|
Job1, Job2, Job7 | 3 |
或者,您可以使用CTE
:
WITH cte AS (SELECT
1,
GROUP_CONCAT(job SEPARATOR ', ') as jobs,
COUNT(job) AS job_count
FROM
sample_table
WHERE
start_time >= '1:00'
AND
end_time <= '4:00'
GROUP BY
1
) SELECT jobs AS "Jobs", job_count AS "Job Count" FROM cte
Result:个个
Jobs | Job Count |
---|---|
Job1, Job2, Job7 | 3 |
DB<;&>小提琴here.
Notes:个
我为分组使用了一个虚设值1
,这就是为什么我使用subquery
和CTE
,如果您不介意输出列,您可以删除subquery
或CTE
.
您可以向GROUP_CONCAT
添加DISTINCT
和ORDER BY
属性,但这可能会影响您的性能.
GROUP_CONCAT(DISTINCT job ORDER BY job ASC SEPARATOR ', ')
indexed
.我不确定您的键列是什么,但设置indexes
将加快查询速度.请参见How MySQL Uses Indexes.