blocks|key|1185550|text|使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1185551|GROUP+BY+year,+month+DESC";|code-block|syntax|javascript|1185552|而不是|1185553|GROUP+BY+MONTH(t.summaryDateTime)+DESC";|1185554|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Use

<pre><code>GROUP BY year, month DESC";
</code></pre>

Instead of 

<pre><code>GROUP BY MONTH(t.summaryDateTime) DESC";
</code></pre>

blocks|key|1185588|text|GROUP+BY+YEAR(t.summaryDateTime),+MONTH(t.summaryDateTime);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1185589|就是你想要的。|unstyled|1185590|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>GROUP BY YEAR(t.summaryDateTime), MONTH(t.summaryDateTime);
</code></pre>
is what you want.

blocks|key|1185637|text|仅按月份分组，必须将YEAR()添加到group+by|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1185638|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You are grouping by month only, you have to add YEAR() to the group by

blocks|key|971305|text|SELECT+YEAR(t.summaryDateTime)+as+yr,+GROUP_CONCAT(MONTHNAME(t.summaryDateTime))+AS+month+
FROM+trading_summary+t+GROUP+BY+yr|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|971306|尽管如此，你仍然需要在外部脚本中处理它，才能得到你想要的结构。|unstyled|971307|例如，使用PHP的explode从月份名称列表创建一个数组，然后使用json_encode()|971308|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECT YEAR(t.summaryDateTime) as yr, GROUP_CONCAT(MONTHNAME(t.summaryDateTime)) AS month 
FROM trading_summary t GROUP BY yr
</code></pre>

Still you would need to process it in external script to get exactly the structure you're looking for.

For example use PHP's explode to create an array from list of month names and then use json_encode()

blocks|key|1185761|text|你必须做这样的事情|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1185762|SELECT+onDay,+id,+
sum(pxLow)/count(*),sum(pxLow),count(`*`),
CONCAT(YEAR(onDay),"-",MONTH(onDay))+as+sdate+
FROM+...+where+stockParent_id+=16120+group+by+sdate+order+by+onDay|code-block|syntax|javascript|1185763|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You must do something like this 

<pre><code>SELECT onDay, id, 
sum(pxLow)/count(*),sum(pxLow),count(`*`),
CONCAT(YEAR(onDay),"-",MONTH(onDay)) as sdate 
FROM ... where stockParent_id =16120 group by sdate order by onDay
</code></pre>

blocks|key|971443|text|如下所示使用提取函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|971444|mysql>+SELECT+EXTRACT(YEAR+FROM+'2009-07-02');
+++++++->+2009|code-block|syntax|javascript|971445|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

use EXTRACT function like this

<pre><code>mysql&gt; SELECT EXTRACT(YEAR FROM '2009-07-02');
 -&gt; 2009
</code></pre>

blocks|key|1185839|text|我更喜欢|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1185840|SELECT
++++MONTHNAME(t.summaryDateTime)+as+month,+YEAR(t.summaryDateTime)+as+year
FROM
++++trading_summary+t+
GROUP+BY+EXTRACT(YEAR_MONTH+FROM+t.summaryDateTime);|code-block|syntax|javascript|1185841|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I prefer
<pre><code>SELECT
 MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM
 trading_summary t 
GROUP BY EXTRACT(YEAR_MONTH FROM t.summaryDateTime);
</code></pre>

blocks|key|1185850|text|GROUP+BY+DATE_FORMAT(summaryDateTime,'%25Y-%25m')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1185851|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>GROUP BY DATE_FORMAT(summaryDateTime,'%Y-%m')
</code></pre>

blocks|key|971740|text|你也可以这样做|type|unstyled|depth|inlineStyleRanges|entityRanges|data|971741|SELECT++SUM(amnt)+`value`,DATE_FORMAT(dtrg,'%25m-%25y')+AS+label+FROM+rentpay+GROUP+BY+YEAR(dtrg)+DESC,+MONTH(dtrg)+DESC+LIMIT+12|code-block|syntax|javascript|971742|按年和月排序。假设您想要从今年和本月一直到12个月的订单|971743|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You cal also do this

<pre><code>SELECT SUM(amnt) `value`,DATE_FORMAT(dtrg,'%m-%y') AS label FROM rentpay GROUP BY YEAR(dtrg) DESC, MONTH(dtrg) DESC LIMIT 12
</code></pre>

to order by year and month. Lets say you want to order from this year and this month all the way back to 12 month

blocks|key|971759|text|我是这样做的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|971760|GROUP+BY++EXTRACT(YEAR_MONTH+FROM+t.summaryDateTime);|code-block|syntax|javascript|971761|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is how I do it:

<pre><code>GROUP BY EXTRACT(YEAR_MONTH FROM t.summaryDateTime);
</code></pre>

blocks|key|971858|text|我想在group+by+year(+datetime)，month(datetime)和groupb+by+extract(来自datetime的year_month)之间添加差异。以下是我在这两个案例中尝试的查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|971859|从表中选择year(datetimecol)作为年，monthname(datetimecol)作为月|971860|按年分组(Datetimecol)和按月分组(Datetimecol)按年排序(Datetimecol)+desc；|971861|结果：|971862|年、月|971863|2020年5月|971864|选择年(+datetimecol)作为年，选择monthname(datetimecol)作为表中的月份GROUP+BY+EXTRACT(YEAR_MONTH+from+datetimecol)+order+by+year(datetimecol)+desc，month(datetimecol)+asc；|971865|971866|971867|2021年1月2021年2月2021年3月2021年4月2020年5月2020年5月2020年6月2020年7月2020年8月2020年9月2020年10月2020年11月|971868|(这就是我需要的结果)|971869|我的观察1.当我使用group+by+year(datetimecol)，month(datetimecol)时，由于datetime字段的原因，没有给出预期的结果..might。2.当我使用GROUP+BY+EXTRACT(YEAR_MONTH+FROM+datetimecol)+order+by+year(Datetimecol)执行第二个查询时，...its工作得非常好。|971870|总之，要按年获取月份，请使用以下查询。|971871|选择年(+datetimecol)作为年，选择monthname(datetimecol)作为表中的月份GROUP+BY+EXTRACT(YEAR_MONTH+from+datetimecol)+order+by+year(datetimecol)+desc，month(datetimecol)+asc；|971872|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|12|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|13|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|14|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|15|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|16|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|17|8|@]|9|@]|A|$]]|$1|N|3|G|5|6|7|18|8|@]|9|@]|A|$]]|$1|O|3|I|5|6|7|19|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1A|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1B|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1C|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1D|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|10|$]]

I would like add difference between group by year(datetime),month(datetime) and groupb by extract(year_month from datetime).
here is the query i tried with these two cases.
select year(datetimecol) as Year,monthname(datetimecol) as Month from table 
group by year(datetimecol) and month(datetimecol) order by year(datetimecol) desc;
result:
<h1>Year, Month</h1>
2020, May
select year( datetimecol) as Year,monthname(datetimecol) as Month from table
GROUP BY EXTRACT(YEAR_MONTH FROM datetimecol) order by year(datetimecol) desc,month(datetimecol) asc;
result:
<h1>Year, Month</h1>
2021, January
2021, February
2021, March
2021, April
2021, May
2020, May
2020, June
2020, July
2020, August
2020, September
2020, October
2020, November
2020, December
(this is the result i need)
My observation
1.when i am using with group by year(datetimecol),month(datetimecol) , not giving desired result ..might be because of datetime feild...
2.when i tired second query with GROUP BY EXTRACT(YEAR_MONTH FROM datetimecol) order by year(datetimecol)...its working absolutely fine.
in conclusion, for getting months by year wise use the following query.
select year( datetimecol) as Year,monthname(datetimecol) as Month from table
GROUP BY EXTRACT(YEAR_MONTH FROM datetimecol) order by year(datetimecol) desc,month(datetimecol) asc;

blocks|key|1186213|text|由于这些数据是逐月提取的交易摘要，我不得不做同样的事情，只想添加我使用的代码。我拥有的数据是以字符串形式保存在数据库中的，因此您的查询可能会更简单。无论哪种方式，作为一名交易员，这本质上是大多数人都会寻找的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1186214|select
++++DATE(DATE_FORMAT(STR_TO_DATE(closeDate,+'%25Y-%25m-%25d'),+'%25Y-%25m-01'))+AS+month_beginning,
++++COUNT(*)+AS+trades,
++++TRUNCATE(sum(profitLoss)/count(*),2)+as+'avgProfit',
++++TRUNCATE(sum(percentGain)/count(*),2)+as+'avgPercent',
++++sum(profitLoss)+as+'gi',
++++sum(profitLoss+>+0)/count(*)+AS+winPercent,
++++sum(profitLoss+<+0)/count(*)+as+'lossPercent',
++++max(profitLoss)+as+bigWinner,
++++min(profitLoss)+as+bigLoser
from+tradeHistory
group+by+month_beginning
order+by+month_beginning+DESC|code-block|syntax|javascript|1186215|但是，它将跳过数据中缺失的月份。因此，如果没有Jan的数据，就不会有一行。|1186216|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

As this data is being pulled for a trade summary by month, I had to do the same thing and would just like to add the code I use. The data I have is saved in the database as a string so your query may be simpler. Either way, as a trader, this is in essence what most people would be looking for:
<pre><code>select
 DATE(DATE_FORMAT(STR_TO_DATE(closeDate, '%Y-%m-%d'), '%Y-%m-01')) AS month_beginning,
 COUNT(*) AS trades,
 TRUNCATE(sum(profitLoss)/count(*),2) as 'avgProfit',
 TRUNCATE(sum(percentGain)/count(*),2) as 'avgPercent',
 sum(profitLoss) as 'gi',
 sum(profitLoss &gt; 0)/count(*) AS winPercent,
 sum(profitLoss &lt; 0)/count(*) as 'lossPercent',
 max(profitLoss) as bigWinner,
 min(profitLoss) as bigLoser
from tradeHistory
group by month_beginning
order by month_beginning DESC
</code></pre>
However, it will skip months that are missing in your data. So if there is no data for Jan, there won't be a row.

Given a table with a timestamp on each row, how would you format the query to fit into this specific json object format.

I am trying to organize a json object into years / months.

json to base the query off:

<pre><code>{
 "2009":["August","July","September"],
 "2010":["January", "February", "October"]
}
</code></pre>

Here is the query I have so far - 

<pre><code>SELECT
 MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM
 trading_summary t 
GROUP BY MONTH(t.summaryDateTime) DESC";
</code></pre>

The query is breaking down because it is (predictably) lumping together the different years.

Group by month and year in MySQL

数据库

给定表的每一行都有一个时间戳，如何格式化查询以适应这个特定的json对象格式。我正在尝试将一个json对象组织成年/月。json作为查询的基础：{  "2009":["August","July","September"],  "2010":["January", "February", "October"]}以下是我...

问在MySQL中按月和年分组
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中按月和年分组EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中按月和年分组
EN