有一张SQL数据表EM,有销售合同号,员工编号,销售日期和销售金额4个字段,如何用winform编写计算出2010年各个季度中,公司销售总份数与总销售金额。
数据库查询语言我已经写好了:
select year(ShippedDate) as 年份,
sum
(
case when
month(ShippedDate) in (1,2,3))
then count(accid) as 销售总份数,sum(account) as 销售总金额 end
else 0
) as 第一季度
from EM where ShippedDate like'2010%'
group by year(ShippedDate)
order by year(ShippedDate)如何把这么复杂的查询语言在WINFORM下实现,望请告知!小弟自学,不太懂,请多见谅
数据库查询语言我已经写好了:
select year(ShippedDate) as 年份,
sum
(
case when
month(ShippedDate) in (1,2,3))
then count(accid) as 销售总份数,sum(account) as 销售总金额 end
else 0
) as 第一季度
from EM where ShippedDate like'2010%'
group by year(ShippedDate)
order by year(ShippedDate)如何把这么复杂的查询语言在WINFORM下实现,望请告知!小弟自学,不太懂,请多见谅
button1
dataGridView1引用:
using System.Data.SqlClient;
using System.Data;代码: private void button1_Click(object sender, EventArgs e)
{
System.Data.DataTable dt = new System.Data.DataTable();
string strConn = "...你的连接字符串";
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
try
{
System.Data.DataTable dt = new System.Data.DataTable();
getStrConn();
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
string strSql="select year(ShippedDate) as 年份,"
+"sum"
+" ("
+" case when "
+" month(ShippedDate) in (1,2,3)) "
+" then count(accid) as 销售总份数,sum(account) as 销售总金额 end"
+" else 0"
+" ) as 第一季度"
+"from EM where ShippedDate like'2010%'"
+"group by year(ShippedDate)"
+"order by year(ShippedDate)";
comm.CommandText = strSql;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = comm;
da.Fill(dt);
dataGridView1.DataSource = table;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "错误提示");
}
finally
{
conn.Close();
}
}