c# - 从数据库中获取数据并显示在 DataGridView 中

Question

我正在使用此查询从 SQL Server 2008 中获取数据:

select 
    g.godown_name, s.supplier_name, t.type_name,
    r.bags_instock, b.lb_per_bag, t.rate
from 
    tbl_godown g, tbl_yarn_supplier s, tbl_yarn_type t,
    tbl_yarn_receive r, tbl_godown_transaction gt, tbl_yarn_book b
where 
    r.book_id = b.book_id and
    s.supplier_id = b.supplier_id and
    t.type_id = b.type_id and
    gt.godown_id = g.godown_id and
    gt.receive_id = r.receive_id

结果是:

这里一些交易有相同的Godown, Suppliar, Tpe & lbs_per_bag

1. 1 & 7
2. 2,3,4,5
3. 8,9,10,11
4. 12
5. 6

如果任何行有相同的 Godown, Suppliar, Tpe & lbs_per_bag 他们不应该重复

结果应该是这样的。

谢谢

等待回复

Answer 1

我会这样做:

SELECT 
    g.godown_name, 
    s.supplier_name, 
    t.type_name,
    b.lb_per_bag, 
    SUM(r.bags_instock) As total_bags_instock, 
    SUM(t.rate * r.bags_instock) 
        /SUM(r.bags_instock) As average_rate

FROM tbl_godown_transaction  As gt
JOIN tbl_godown          As g  ON gt.godown_id   = g.godown_id 
JOIN tbl_yarn_receive    As r  ON gt.receive_id  = r.receive_id
JOIN tbl_yarn_book       As b  ON r.book_id      = b.book_id 
JOIN tbl_yarn_supplier   As s  ON s.supplier_id  = b.supplier_id 
JOIN tbl_yarn_type       As t  ON t.type_id      = b.type_id

GROUP BY
    g.godown_name, 
    s.supplier_name, 
    t.type_name,
    r.lb_per_bag