private void button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = “server=172.16.6.6;database=pda;uid=tally;pwd=kuyetecrebE8”;
con.Open();
string strSQL =”Select 日期,船公司,英文船名,中文船名,航次,进出口,SUM(e20) e20, SUM(e40) e40, SUM(e45) e45, SUM(f20) f20, SUM(f40) f40, SUM(f45) f45,sum(teu) 合计teu
from
(
select CONVERT(VARCHAR, CAST(b.enddate AS DATE), 111) AS 日期, b.WEIGHT as 船公司, b.shipname as 英文船名, b.cshipname as 中文船名, b.voyage as 航次, b.ieflag as 进出口,
CASE WHEN(a.ewflag = “E” AND a.ssize = “20”)THEN 1 ELSE 0 END AS e20,
CASE WHEN(a.ewflag = “E” AND a.ssize = “40”)THEN 1 ELSE 0 END AS e40,
CASE WHEN(a.ewflag = “E” AND a.ssize = “45”)THEN 1 ELSE 0 END AS e45,
CASE WHEN(a.ewflag = “F” AND a.ssize = “20”)THEN 1 ELSE 0 END AS f20,
CASE WHEN(a.ewflag = “F” AND a.ssize = “40”)THEN 1 ELSE 0 END AS f40,
CASE WHEN(a.ewflag = “F” AND a.ssize = “45”)THEN 1 ELSE 0 END AS f45,
case a.ssize WHEN “45” THEN 2.25 WHEN “40” THEN 2 WHEN “20” THEN 1 ELSE 2.25 END AS teu
FROM container a
left join ships b ON(A.SHIPNAME = B.SHIPNAME AND A.VOYAGE = B.VOYAGE)
where b.depart = “sy” and b.weight = “zgxl” and(b.enddate between “2016-03-00” and “2016-04-00”) and(tallymen <> “” and tallymen is not null) and(res <> “1” and res <> “5”)
)a
group by 日期, 船公司, 英文船名, 中文船名, 航次, 进出口
order by 日期”;
{
SqlConnection con = new SqlConnection();
con.ConnectionString = “server=172.16.6.6;database=pda;uid=tally;pwd=kuyetecrebE8”;
con.Open();
string strSQL =”Select 日期,船公司,英文船名,中文船名,航次,进出口,SUM(e20) e20, SUM(e40) e40, SUM(e45) e45, SUM(f20) f20, SUM(f40) f40, SUM(f45) f45,sum(teu) 合计teu
from
(
select CONVERT(VARCHAR, CAST(b.enddate AS DATE), 111) AS 日期, b.WEIGHT as 船公司, b.shipname as 英文船名, b.cshipname as 中文船名, b.voyage as 航次, b.ieflag as 进出口,
CASE WHEN(a.ewflag = “E” AND a.ssize = “20”)THEN 1 ELSE 0 END AS e20,
CASE WHEN(a.ewflag = “E” AND a.ssize = “40”)THEN 1 ELSE 0 END AS e40,
CASE WHEN(a.ewflag = “E” AND a.ssize = “45”)THEN 1 ELSE 0 END AS e45,
CASE WHEN(a.ewflag = “F” AND a.ssize = “20”)THEN 1 ELSE 0 END AS f20,
CASE WHEN(a.ewflag = “F” AND a.ssize = “40”)THEN 1 ELSE 0 END AS f40,
CASE WHEN(a.ewflag = “F” AND a.ssize = “45”)THEN 1 ELSE 0 END AS f45,
case a.ssize WHEN “45” THEN 2.25 WHEN “40” THEN 2 WHEN “20” THEN 1 ELSE 2.25 END AS teu
FROM container a
left join ships b ON(A.SHIPNAME = B.SHIPNAME AND A.VOYAGE = B.VOYAGE)
where b.depart = “sy” and b.weight = “zgxl” and(b.enddate between “2016-03-00” and “2016-04-00”) and(tallymen <> “” and tallymen is not null) and(res <> “1” and res <> “5”)
)a
group by 日期, 船公司, 英文船名, 中文船名, 航次, 进出口
order by 日期”;
SqlDataAdapter da = new SqlDataAdapter(strSQL, con); //创建DataAdapter数据适配器实例
DataSet ds = new DataSet();//创建DataSet实例
da.Fill(ds, “自定义虚拟表名”); //使用DataAdapter的Fill方法(填充),调用SELECT命令
dataGridView1.DataSource = ds.Tables[1].DefaultView;
con.Close();//关闭数据库
}
解决方案
40
string strSQL =@“Select 日期,船公司