如何在另一个结构中转换后续数据表

I've the following data table:

我有以下数据表:

year, month,  customer, agent 
2013,  1,     abc,      yyyy 
2013,  2,     abc,      yyyy 
2013,  3,     abc,      zzzz 
2013,  4,     abc,      xxxx 
... 
2013,  12,    abc,      xxxx

I need to transform this table in a structure like

我需要在这样的结构中转换这个表

year, from, to, customer, agent
2013, 1,    2   abc,      yyyy
2013, 3,    3   abc,      zzzz
2013, 4,    12  abc,      xxxx

So i've tried using MAX and MIN function to identify MIN and MAX month for customer and agent.. but it don't work.

所以我尝试使用MAX和MIN函数来识别客户和代理商的MIN和MAX月份但是它不起作用。

Can you help me ? Thanks

你可以帮我吗 ?谢谢

1 个解决方案

#1

Try this:

SELECT A.year, 
       MIN(A.month) `from`,
       MAX(A.month) `to`, 
       A.customer, 
       A.agent
FROM tableA A
GROUP BY A.year,
         A.customer, 
         A.agent

Check the SQL FIDDLE DEMO

检查SQL FIDDLE DEMO

OUTPUT

| YEAR | FROM | TO | CUSTOMER | AGENT |
|------|------|----|----------|-------|
| 2013 |    4 | 12 |      abc |  xxxx |
| 2013 |    1 |  2 |      abc |  yyyy |
| 2013 |    3 |  3 |      abc |  zzzz |

#1