用查询拉出gridview [英] Pulling gridview with query

问题描述

度过愉快的一天;



我不懂英文，我用google翻译，如果有翻译错误，我道歉。



预先感谢您的帮助。



创建GridView3

我应该写什么样的查询。



asp.net，C＃，sql experss，我在当地广播。



gridview1-table1（sql）/ gridview2-table2（sql）/ gridview3-query？



收到的表

column1 - 第2栏

product1 -5 kg

product2 -3 kg

product2 -4 kg

product1 -3 kg < br $>


外拨表

column1-column2

product1-3 kg

product2- 3公斤

产品1-4公斤

产品2-2公斤



gridview3结果

column1-column2

product1-1 kg

product2-2 kg



我是什么尝试过：

Have a nice day;

I do not know english, I use to google translate, I apologize if there is a translation error.

Thanks in advance for your help.

To create GridView3
what kind of query should I write.

asp.net,C#,sql experss,I am broadcasting local.

gridview1-table1(sql)/gridview2-table2(sql)/gridview3-query ?

incoming table
column1 -column2
product1 -5 kg
product2 -3 kg
product2 -4 kg
product1 -3 kg

Outgoing table
column1-column2
product1-3 kg
product2-3 kg
product1-4 kg
product2-2 kg

gridview3 result
column1-column2
product1-1 kg
product2-2 kg

What I have tried:

SqlDataSource3.SelectCommand = "WITH    entries AS( SELECT marka, kimyasal_kodu, kimyasal_adi, SUM(kilo) AS kilo FROM byh_kimyasal_giris_tbl GROUP BY marka, kimyasal_kodu, kimyasal_adi), exits AS(SELECT marka, kimyasal_kodu, kimyasal_adi, SUM(kilo) AS kilo FROM byh_kimyasal_kullanim_tbl GROUP BY marka, kimyasal_kodu, kimyasal_adi )SELECT g.marka, g.kimyasal_kodu, g.kimyasal_adi, g.kilo, COALESCE(c.kilo, 0) AS kilo, g.kilo - COALESCE(c.kilo, 0) AS remaining FROM entries g LEFT JOIN exits c ON g.marka = c.marka AND g.kimyasal_kodu = c.kimyasal_kodu AND g.kimyasal_adi = c.kimyasal_adi";

GridView3.DataSource = SqlDataSource3;
GridView3.DataBind();

推荐答案

您好，请尝试以下查询

Hi Try Below query

DECLARE @IncommingT1 AS TABLE (C1 VARCHAR(64), Data int)
DECLARE @OutGoingT2 AS TABLE (C1 VARCHAR(64), Data int)

INSERT INTO @IncommingT1
(
    C1,
    Data
)
VALUES
(   'product1', -- C1 - varchar(64)
    5   -- Data - int
),(   'product2', -- C1 - varchar(64)
    3   -- Data - int
),(   'product2', -- C1 - varchar(64)
    4   -- Data - int
),(   'product1', -- C1 - varchar(64)
    3   -- Data - int
);
INSERT INTO @OutGoingT2
(
    C1,
    Data
)
VALUES
(   'product1', -- C1 - varchar(64)
    3   -- Data - int
),(   'product2', -- C1 - varchar(64)
    3   -- Data - int
),(   'product1', -- C1 - varchar(64)
    4   -- Data - int
),(   'product2', -- C1 - varchar(64)
    2   -- Data - int
)

SELECT it.C1,(SUM(DISTINCT it.Data)) - (SUM(DISTINCT ogt.Data)) AS C2  FROM  @IncommingT1 AS it 
LEFT JOIN @OutGoingT2 AS ogt ON ogt.C1 = it.C1
GROUP BY it.C1,ogt.c1

MS SQL管理Studioda工作结果是正确，但aspx.cs也给出了错误的结果。



incomming_tbl.kilo（12 + 3 + 5） - outgoing_tbl.kilo（2 + 2）= 16但是结果= 18结果是错误的。



我在哪里弄错了？

MS SQL Management Studioda is working the result is correct but aspx.cs also gives incorrect results.

"incomming_tbl.kilo (12+3+5)" - "outgoing_tbl.kilo(2+2)"= 16 but "result=18" the result is wrong.

Where am I making the mistake?

SqlDataSource1.SelectCommand = "SELECT Incomming_tbl.colorName,(SUM(DISTINCT Incomming_tbl.kilo)) - (SUM(DISTINCT OutGoing_tbl.kilo)) AS kilo FROM Incomming_tbl AS Incomming_tbl LEFT JOIN OutGoing_tbl AS OutGoing_tbl ON OutGoing_tbl.colorName = Incomming_tbl.colorName GROUP BY Incomming_tbl.colorName,OutGoing_tbl.colorName";

这篇关于用查询拉出gridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！