Mysql
將 2 行合併為一行
| Year | Category | Number_of_products | | 19 |Fruits1 | 25 | | 19 |Fruits2 | 45 | | 20 |Fruits1 | 50 | | 20 |Fruits2 | 50 |
你知道如何轉換這張桌子,所以它會這樣嗎?將不勝感激任何幫助
| Year | Category | Number_of_products | |------|----------|--------------------| |19 |Fruits | 70 | |20 |Fruits | 100 |
我認為它適用於 SQL Server。如果您使用 MySql,您可以更改語法以與您的系統兼容:
with cte as (select year, case when Category='Fruits1' then 'Fruits' when Category='Fruits2' then 'del' else Category end as Category, case when Category='Fruits1' then Number_of_products + (select Number_of_products from table1 as t2 where t2.year=t1.year and t2.Category='Fruits2' ) when Category='Fruits2' then -1 else Number_of_products end as value from table1 as t1) select * from cte where value>0
您可以使用:
select `Year`, LEFT(Category,6) as new_Category, sum(Number_of_products) as tot_Number_of_products from test_tbl where LEFT(Category,6) ='Fruits' ---this could be written as " Category like 'Fruits%' " group by `Year`,new_Category order by `Year` asc ;
我推薦什麼。
- 不要使用關鍵字和保留字,例如
YEAR
,如果可能,更好的做法是重命名。- 在 Category 列中,您有 Fruits1,Fruits2 之類的值,這似乎是多餘的,您可以將它們分組到一個主要類別中,例如 Fruits。