mysql - 单个查询中的多项选择，但有多个不同的字段名称

Question

当下面的 select 语句中有多个 Fieldname(Turnover 和 NetProfit)时，当我将 mySqlDataAdapter 填充到数据表时会发生错误。然后我会将其放入 datagridview 中。如何解决？

数据:

FieldName | Ticker| Value| Year
--
Turnover  | APPL  | 1555 | 2010
Turnover  | APPL  | 1688 | 2011 
Turnover  | APPL  | 1900 | 2012
Turnover  | APPL  | 1989 | 2013 
NetProfit | APPL  | 123  | 2010
NetProfit | APPL  | 158  | 2011 
NetProfit | APPL  | 200  | 2012
NetProfit | APPL  | 300  | 2013

代码:

Dim da As New MySqlDataAdapter(strSQL, cn)
Dim dt As New DataTable
da.Fill(dt) 'error occurred here!!! if more than 1 distinct fieldname
dgv1.DataSource = dt

查询:

SELECT 
 (SELECT format(Value,0) from Data WHERE Ticker = 'APPL' AND Year = 2010) As 'Y2010' ,
 (SELECT format(Value,0) from Data WHERE Ticker = 'APPL' AND Year = 2011) As 'Y2011' ,
 (SELECT format(Value,0) from Data WHERE Ticker = 'APPL' AND Year = 2012) As 'Y2012' ,
 (SELECT format(Value,0) from Data WHERE Ticker = 'APPL' AND Year = 2013) As 'Y2013'

输出:(数据表中没有净利润)(如果数据表只包含一个FieldName，比如Turnover，那么下面的输出就是营业额)

Y2010| Y2011| Y2012| Y2013
--
1555 | 1688 | 1900 | 1989

输出:(数据表中的 2 个字段名称，即营业额和净利润

(未成功)

我想要的输出:(目前，我添加了一个条件“WHERE Ticker =”)

FieldName | Y2010 | Y2011 | Y2012 | Y2013
--
Turnover  | 1555  | 1688  | 1900  | 1989
NetProfit | 123   | 158   | 200   | 300

下一步所需的输出:(对于所有代码)

Ticker| FieldName | Y2010| Y2011| Y2012| Y2013
--
APPL  | Turnover  | 1555 | 1688 | 1900 | 1989
APPL  | NetProfit | 123  | 158  | 200  | 300
MSFT  | Turnover  | 2555 | 2688 | 1600 | 3489
MSFT  | NetProfit | 133  | 248  | 205  | 300

Answer 1

这应该会得到你想要的结果

select distinct 
cast(data.Ticker as char(20)), 
cast(data.FieldName as char(20)),
d2010.Value as 'Y2010' ,
d2011.Value as 'Y2011' ,
d2012.Value as 'Y2012' ,
d2013.Value as 'Y2013' 
from data
inner join data as d2010 on data.FieldName like d2010.FieldName and d2010.Year = 2010
inner join data as d2011 on data.FieldName like d2011.FieldName and d2011.Year = 2011
inner join data as d2012 on data.FieldName like d2012.FieldName and d2012.Year = 2012
inner join data as d2013 on data.FieldName like d2013.FieldName and d2013.Year = 2013

注意:如果文本字段是文本类型，则需要进行distinct 转换。我在 MSSQL 中对此进行了测试，并在 MySQL 验证器中检查了语法。