Mehrfachabfragen für ein Raster

Ich habe einen Tisch wie folgt>

Party_Code | Buy_Sell | Trade_Qty | Market_Rate 036L09 1 350 20 036L09 2 300 30 

Ich versuche, ein Raster in diesen data wie folgt zu zeigen

 BuyQty | BuyRate | BuyAmt|SellQty | SellRate | SellAmt 350 20 7000 300 30 9000 

Dafür habe ich zwei Fragen gemacht >>

 select sum(Trade_qty) as BuyQty, sum(Market_Rate) as BuyAmt from tradeFile where Buy_Sell='1' and Party_Code='036L09' select sum(Trade_qty) as SellQty, sum(Market_Rate) as SellAmt from tradeFile where Buy_Sell='2' and Party_Code='036L09' 

Ich möchte, dass diese Abfragen für ein einziges Raster gelten. Dafür habe ich Code als >> geschrieben

  try { da = new SqlDataAdapter("select sum(Trade_qty) as BuyQty, sum(Market_Rate) as BuyAmt from tradeFile where Buy_Sell='1' and Party_Code='0L036'", con); DataSet ds = new DataSet(); da.Fill(ds); SqlDataAdapter sellDA = new SqlDataAdapter("select sum(Trade_qty) as SellQty, sum(Market_Rate) as SellAmt from tradeFile where Buy_Sell='2' and Party_Code='0L036'", con); DataSet dsSell = new DataSet(); sellDA.Fill(dsSell); gv.DataSource = ds.Tables[0]; gv.DataSource = dsSell.Tables[0]; } catch (Exception ex) { MessageBox.Show(ex.Message); } 

Aber es nimmt nur data aus der letzten dataquelle.

Wie kann ich das machen?

Diese Abfrage gibt das Ergebnis für einen bestimmten Party_Code

 select sum (case when Buy_sell=1 then Trade_Qty else 0 end) as BuyQty, sum (case when Buy_sell=1 then Market_Rate else 0 end) as BuyRate, sum (case when Buy_sell=1 then Trade_Qty*Market_Rate else 0 end) as BuyAmount, sum (case when Buy_sell=2 then Trade_Qty else 0 end) as SellQty, sum (case when Buy_sell=2 then Market_Rate else 0 end) as SellRate, sum (case when Buy_sell=2 then Trade_Qty*Market_Rate else 0 end) as SellAmount from tradeFile where Party_Code='0L036' 

und dieses gibt das Ergebnis für alle Party_Codes zurück

 select Party_Code, sum (case when Buy_sell=1 then Trade_Qty else 0 end) as BuyQty, sum (case when Buy_sell=1 then Market_Rate else 0 end) as BuyRate, sum (case when Buy_sell=1 then Trade_Qty*Market_Rate else 0 end) as BuyAmount, sum (case when Buy_sell=2 then Trade_Qty else 0 end) as SellQty, sum (case when Buy_sell=2 then Market_Rate else 0 end) as SellRate, sum (case when Buy_sell=2 then Trade_Qty*Market_Rate else 0 end) as SellAmount from tradeFile group by Party_Code 

verwenden Sie die Gruppe Nach der Abfrage wie unten, dies gibt Ihnen Ergebnis in der einzelnen datasatz.

 select sum(Trade_qty) as Qty, sum(Market_Rate) as Amt, (case when Buy_Sell = 1 then 'Buy' Else 'Sale' end) as TransactionType from tradeFile where Party_Code='036L09' group by Buy_Sell 

lass uns diese abfrage ausprobieren

 Select sum(t1.Trade_qty) as BuyQty, sum(t1.Market_Rate) as BuyAmt, sum(t2.Trade_qty) as SellQty, sum(t2.Market_Rate) as SellAmt FROM tradeFile INNER join tradeFile t2 ON t1.Party_Code=t2.Party_Code and t1.Buy_Sell='1' and t1.Party_Code='036L09' and t2.Buy_Sell='2' 

Versuchen Sie diese Abfrage, die es Ihr benötigtes Ergebnis in einer einzigen Abfrage zurückgibt

 SELECT a.Trade_Qty AS BuyQuant, a.Market_Rate AS BuyRate, a.Trade_Qty * a.Market_Rate AS BuyAmt, b.Trade_Qty AS SellQuant, b.Market_Rate AS SellRate, b.Trade_Qty * b.Market_Rate AS SellAmt FROM tradeFile a, tradeFile b WHERE a.Party_Code = b.Party_Code AND a.Party_Code = '036L09' AND a.Buy_Sell = 1 AND b.Buy_Sell = 2; 

Für die Bedingung mit mehreren Kauf verkaufen von 1 Party_code versuchen diese Abfrage

 SELECT a.Trade_Qty AS BuyQuant, a.Amt, b.Trade_Qty AS SellQuant, b.Amt FROM (SELECT Party_Code sum(Trade_Qty) As 'Trade_Qty', sum(Trade_Qty * Market_Rate) As 'Amt' FROM tradeFile WHERE Party_Code = '036L09' AND Buy_Sell = 1 GROUP BY Party_Code ) a, (SELECT Party_Code sum(Trade_Qty) As 'Trade_Qty', sum(Trade_Qty * Market_Rate) As 'Amt' FROM tradeFile WHERE Party_Code = '036L09' AND Buy_Sell = 2 GROUP BY Party_Code ) b WHERE a.Party_Code = b.Party_Code; 

Von der ersten Stelle, deine Frage ist tottaly falsch wie das, was du gesagt hast.

 select sum(Trade_qty) as BuyQty, sum(Market_Rate) as BuyAmt from tradeFile where Buy_Sell='1' and Party_Code='036L09' select sum(Trade_qty) as SellQty, sum(Market_Rate) as SellAmt from tradeFile where Buy_Sell='2' and Party_Code='036L09' 

Probieren Sie diese Fragen aus

 SELECT sum(a.Trade_qty) as BuyQty, a.Market_rate as BuyRate, a.Market_rate * sum(a.Trade_qty) as BuyAmt, sum(b.Trade_qty) as SellQty, b.Market_rate as SellRate, b.Market_rate * sum(b.Trade_qty) as SellAmt FROM tradeFile a INNER JOIN tradeFile b on b.id= a.id GROUP BY a.Market_rate,b.Market_rate WHERE (a.Buy_Sell ='1' and a.Party_Code='036L09') or (b.Buy_Sell='2'' and.Party_Code='036L09') 

Aber das ist immer noch generisch, du musst zwei tisch machen, 1 tisch zum verkauf und 1 tisch für kauf

über deine datasource, es war falsch, du hast über die

 gv.DataSource = ds.Tables[0]; 

in

 gv.DataSource = dsSell.Tables[0]; 

deshalb werden die letzten Abfragen angezeigt.