sumif sql通过vb.net中的OLEDB

Question

我希望在“ BLC”列中的数据库“ GSGTS/T3”中的SUMIF结果，其中包含“ PNM”列中的数字标准。我无法为SUMIF过程创建SQL。下面的说明：

• “ PNM”中的数字值为185000的结果，即1000

QTY  CIU        DPR
10    100000     5   =10X10000X(1-5/100)= 95000
5     20000      10  =5X20000X(1-10/100)= 95000
TOTAL                =185000   

GSDTS AS t1                 
PNM     ITM        QTY  CIU    NOD  DPR
1000    TEST 1000   10  10000   01  5
1000    TEST 1001   5   20000   02  10
1002    TEST 1000   12  10000   01  15
1002    TEST 1001   6   20000   02  6

GSGTS AS t3                             
CSB GDN             PNM      DTS     DUD    SAC  DIS    BLC  DIS2
    A.04.01.002.001 1000    13-May-22       01-01           
    A.04.01.002.001 1002    13-May-22       01-02           
result                              
GSGTS AS t3                             
CSB GDN              PNM    DTS       DUD   SAC  DIS    BLC    DIS2
    A.04.01.002.001 1000    13-May-22       01-01       185000  
    A.04.01.002.001 1002    13-May-22       01-02       214800  

Private Sub fillDataGridView3()
        Try
            'Dim query As String = "select PNM, sum((qty*ciu)*(1-dpr/100)) AS BLC from GSDTS group by PNM"
 Dim sql As String = "update GSGTS SET BLC=( select sum((qty*ciu)*(1-dpr/100.0)) from GSDTS t2 WHERE GSGTS.[PNM] = t2.[PNM] ) WHERE GDN = 'A.04.01.002.001';"
            Using con As OleDbConnection = New OleDbConnection(cn)
                Using cmd As OleDbCommand = New OleDbCommand(query, con)
                    'cmd.Parameters.AddWithValue("@CODE", ComboBox1.SelectedValue)
                    Using da As New OleDbDataAdapter(cmd)
                        Dim dt As DataTable = New DataTable()
                        da.Fill(dt)
                        da.Dispose()
                        source3.DataSource = dt
                        Me.DataGridView3.DataSource = source3
                        Me.DataGridView3.Refresh()
                    End Using
                End Using
            End Using
        Catch ex As Exception
        End Try
    End Sub
Sub UpdateGsgts()
    Try
        'Dim sql As String = "update GSGTS as t3 inner join GSDTS as t2 on t3.[PNM] = t2.[PNM] set t3.[BLC] = [CIU] WHERE GDN = 'A.04.01.002.001'AND PNM=@PNM"
           Dim sql As String = "select PNM, sum((qty*ciu)*(1-dpr/100)) AS BLC from GSDTS group by PNM"
        Using conn As New OleDbConnection(cn),
               cmd As New OleDbCommand(sql, conn)
               'cmd.Parameters.AddWithValue("@PNM", ComboBox1.SelectedValue)
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using

    Catch myerror As OleDbException
        MessageBox.Show("Error: " & myerror.Message)
    End Try
End Sub

Answer 1

您可以计算pnm明智blc如下：

架构和插入语句：

 create table GSDTS ( PNM int, ITM varchar(100), QTY int, CIU int, NOD int, DPR int);
 insert into GSDTS values(1000,    'TEST 1000',   10,  10000,   01,  5);
 insert into GSDTS values(1000,    'TEST 1001',   5,   20000,   02,  10);
 insert into GSDTS values(1002,    'TEST 1000',   12,  10000,   01,  15);
 insert into GSDTS values(1002,    'TEST 1001',   6,   20000,   02,  6);

查询：

 select PNM, sum((qty*ciu)*(1-dpr/100)) BLC from GSDTS
 group by PNM

输出：

pnm	blc
1000	185000.0000
1002	214800.0000.0000

db＆lt; 这里

“

“ https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle = c6bf68e50cac5f4aeaeaeae4ae4c974ef1d64c4f1d64c4f

create table GSDTS ( PNM int, ITM varchar(100), QTY int, CIU int, NOD int, DPR int);
   insert into GSDTS values(1000,    'TEST 1000',   10,  10000,   01,  5);
   insert into GSDTS values(1000,    'TEST 1001',   5,   20000,   02,  10);
   insert into GSDTS values(1002,    'TEST 1000',   12,  10000,   01,  15);
   insert into GSDTS values(1002,    'TEST 1001',   6,   20000,   02,  6);
   
   
   create table GSGTS (GDN varchar(50), PNM int, BLC int);
   insert into GSGTS values('A.04.01.002.001',1000,    0);
   insert into GSGTS values('A.04.01.002.001',1002,    0);

>在

 select * from GSGTS

rel =“ nofollow bollow noreferrer”> nore noreferrer “ ：

| GDN             |  PNM | BLC
| :-------------- | ---: | --:
| A.04.01.002.001 | 1000 |   0
| A.04.01.002.001 | 1002 |   0

更新查询：

update GSGTS SET BLC=( select sum((qty*ciu)*(1-dpr/100.0)) from GSDTS t2 WHERE GSGTS.[PNM] = t2.[PNM] ) WHERE GDN = 'A.04.01.002.001';

更新后从GSGTS表中进行选择：

select * from GSGTS

输出：

| GDN             |  PNM |    BLC
| :-------------- | ---: | -----:
| A.04.01.002.001 | 1000 | 185000
| A.04.01.002.001 | 1002 | 214800

db＆lt;这里