private void btnS1_Click(object sender, EventArgs e)
{
string sql = @"select OrderID ,SUM(UnitPrice*Quantity) 销售总金额
from OrderDetails
group by OrderID";
dv.DataSource = DBHelperSQL.TableSelect(sql);
}
private void btbnS1_Click(object sender, EventArgs e)
{
string sql = @"select *
from Orders inner join OrderDetails on Orders.OrderID=OrderDetails.OrderID
where Orders.OrderID=@OrderID";
SqlParameter[] para = { new SqlParameter("@OrderID", txtOrderID.Text) };
dv.DataSource = DBHelperSQL.TableSelect(sql, para);
}
private void btnS2_Click(object sender, EventArgs e)
{
string sql = @"select a.ProductID ,SUM(a.UnitPrice*a.Quantity)/SUM(a.Quantity) 平均销售单价,SUM(a.Quantity)总销售数量,
(select top 1 UnitPrice from OrderDetails where ProductID=a.ProductID order by UnitPrice desc) 最高单价,
(select top 1 UnitPrice from OrderDetails where ProductID=a.ProductID order by UnitPrice asc) 最低单价
from OrderDetails a
group by ProductID";
dv.DataSource = DBHelperSQL.TableSelect(sql);
}
private void btnS2_Click(object sender, EventArgs e)
{
string sql = @"select *
from Orders
where OrderID in(select OrderID
from OrderDetails
where ProductID in(select ProductID
from Product
where SupplierName=@Name))" ;
SqlParameter[] para = { new SqlParameter("@Name", txtSupplierName.Text) };
dv.DataSource = DBHelperSQL.TableSelect(sql, para);
}
public void MyShow()
{
string sql = @"select * from Product";
dv.DataSource = DBHelperSQL.TableSelect(sql);
}