关于mysql：对此优化查询可以是什么？

What can be the optimized query for this?

我陷入了小问题。有一个名为 Table_Activities 的表。

Table_Activity (Act_id, Date, Activity_name, description).

我们需要据此生成一份报告。用户将从下拉列表中选择他想要生成报告的月份和年份。

我们需要按活动名称显示该月份和年份组的所有活动。

示例 – 用户选择 2012 年 6 月和 2012 年。

报告将是-

园艺

1
2
3

01/06/2012 – They have planted 100 trees.
14/06/2012 – something
27/06/2012 – something

培训

1
2
3

02/06/2012 – Detail description
15/06/2012 – something
28/06/2012 – something

我的问题是 mysql 查询以这种格式获取数据的方式是什么？？

1
2
3

select `Date`,description from tm_activities
where month(`Date`)=‘6’ and year(`Date`)=‘2012’
order by Activity_name,`date`

要返回问题中指示的确切格式，请尝试以下操作：

1
2
3
4
5
6
7
8

select concat(if(actdate=”,activity_name,date_format(actdate,‘%d/%m/%y’)),if(description<>”,concat(‘ – ‘,description),”)) as labelm from
(
(select ActDate,description,activity_name from tm_activities where month(ActDate)=‘6’ and year(ActDate)=‘2012’
)
union all
(Select distinct ”,”,activity_name from tm_activities where month(ActDate)=‘6’ and year(ActDate)=‘2012’)
)m order by activity_name,actdate
;

这里是 SQL FIDDLE。

输出如下：

1
2
3
4
5
6
7

Gardening
01/06/12 – They have planted 100 trees.
27/06/12 – Gar 2
Training
12/06/12 – Training 1
28/06/12 – Traning 2
30/06/12 – Traning 3

要通过 Activity_name 获取特定月份和年份组的数据，请尝试以下操作：

1
2
3
4
5
6
7

SELECT Activity_name
,GROUP_CONCAT(DATE_FORMAT(Date,"%d/%m/%Y")) as `Date`
,GROUP_CONCAT(Description) as `Description`
FROM Table_Activities
WHERE MONTH(Date) = MONTH(‘2012-06-01’)
AND YEAR(Date) = YEAR(‘2012-06-01’)
GROUP BY Activity_name

输出

1
2
3
4

ACTIVITY_NAME DATE DESCRIPTION
———————————————————————————–
Gardening 01/06/2012,27/06/2012 They have planted 100 trees.,Description3
Training 12/06/2012,28/06/2012 Description2,Description4

看到这个 SQLFiddle

1
2
3
4
5
6

Select
DATE_FORMAT(‘date_column’,"%D/%M/%Y") as `Date`,
other_column
from Table_Activities
where Month(Date) = Month(‘2012-06-01’)
AND Year(Date) = Year(‘2012-06-01’)

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/271066.html

关于mysql：对此优化查询可以是什么？

What can be the optimized query for this?

相关推荐

发表回复