SQL简单查询语句

zuomingyu2 2011-03-31 06:43:31

有4个表po_hdr、po_dat、gr_hdr它们之间的关系是一对多、一对一，查找每一条po_hdr 对应的po_dat的item_qty的总和大于10的的gr_hdr.gr_no请问怎么写？





select gr_hdr.gr_no

from po_hdr,po_dat,gr_hdr

where po_hdr.po_no = po_dat.po_no

      AND po_hdr.gr_no = gr_hdr.gr_no

      --AND ---------- SUM(po_dat.item_qty) > 10

...全文

1095 42 打赏收藏转发到动态举报

写回复

用AI写文章

42 条回复

切换为时间正序

请发表友善的回复…

发表回复

dingdlmu 2011-04-03

打赏
举报

用partition分区

select gr_hdr.gr_no
from po_hdr,po_dat,gr_hdr
where po_hdr.po_no = po_dat.po_no
AND po_hdr.gr_no = gr_hdr.gr_no
qualify sum(gr_hdr.item_qty) over(partition by po_hdr.po_no)>10

要是不行的话再联系 429551142@qq.com

yudx_26 2011-04-03

打赏
举报

学习中

ross_ 2011-04-03

打赏
举报

学习。。。。

ross_ 2011-04-03

打赏
举报

学习中。。。。

zxdak 2011-04-01

打赏
举报

你们是在写sql语句么？

l396634084 2011-04-01

打赏
举报

都是浮云，一个漏写条件，一个漏自居

Music杰 2011-03-31

打赏
举报

你们怎么搞的！！

zuomingyu2 2011-03-31

打赏
举报

哎还是未能解决我的问题不过分还是先给你们明天再继续问希望还能得到你们的指教，谢谢！

长笛党希望 2011-03-31

打赏
举报

学习中。。。。

快溜 2011-03-31

打赏
举报

你到底是不是MM啊

AcHerat 2011-03-31

打赏
举报

可以简单点！



and (select (sum(qty_received) - sum(qty_ordered))*1./sum(qty_ordered) from po_dat where po_no=poda.po_no) < -0.05

快溜 2011-03-31

打赏
举报

and (select sum(qty_ordered)-SUM(qty_ordered)/ sum(qty_ordered) from po_no=poda.po_no)<-0.5

AcHerat 2011-03-31

打赏
举报

上边是大于10

AcHerat 2011-03-31

打赏
举报



--也可以





SELECT      poda.po_no,poda.line_no, poda.item_code, poda.item_name, poda.client_item, poda.qty_received, 

                        poda.qty_ordered, poda.unit, poda.item_price, 

                        ((poda.qty_received -poda.qty_ordered)/poda.qty_ordered) as po_re_dif,

                        phd.client_name, poda.curr_code, poda.qty_ordered - poda.qty_received AS reming, 

                        phd.eta_date, (gr_dat.item_qty - poda.qty_ordered) 

                        / poda.qty_ordered AS dif, (((SELECT SUM (pd1.qty_received) from po_dat as pd1 inner join po_hdr on 

                        pd1.po_no = phd.po_no)  - (SELECT SUM (pd2.qty_ordered) from po_dat as pd2 

                        inner join po_hdr on pd2.po_no = phd.po_no) ) / (SELECT SUM (pd1.qty_ordered)

                         from po_dat as pd1 inner join po_hdr on pd1.po_no = phd.po_no) ) AS gr_dif,

                         (poda.qty_ordered - poda.qty_received) 

                        * poda.item_price AS sy,  poda.qty_ordered * poda.item_price AS price_total, 

                        gr_hdr.gr_date, gr_hdr.gr_no, gr_dat.item_qty AS qty, 

                        poda.qty_received AS Expr1, phd.po_date, gr_dat.item_qty * poda.item_price AS gr_total_price, 

                        phd.client_code, gr_dat.item_qty

                        ,(select anly_code4 from item as its where its.item_code = poda.item_code) as anly_code4

                         ,phd.on_board_date,po_status

            FROM         po_dat as poda, po_hdr as phd, gr_dat, gr_hdr

            WHERE     poda.po_no = phd.po_no AND phd.po_no = gr_dat.ref_doc_no AND poda.item_code = gr_dat.item_code 

                      AND gr_dat.gr_no = gr_hdr.gr_no AND 

                                  (gr_hdr.gr_status <> 'C') AND (phd.po_status <> 'C') 

                      AND phd.po_no between  @po_no_fr_tmp and @po_no_to_tmp

                     AND CONVERT([VARCHAR](8),phd.eta_date , 112) between @po_eta_date_fr_tmp and @po_eta_date_to_tmp

                    AND gr_hdr.gr_no between @gr_no_fr_tmp and @gr_no_to_tmp

                    AND phd.client_code between @suppliers_fr and @suppliers_to

                     AND CONVERT([VARCHAR](8),phd.po_date , 112) between @po_date_fr and @po_date_to

                    AND CONVERT([VARCHAR](8),gr_hdr.gr_date , 112) between @gr_date_fr_tmp and @gr_date_to_tmp

                    and exists (select 1 from po_dat where po_no=poda.po_no group by po_no having sum(item_qty) > 0)---这里

                     --AND ( 

                        --(phd.po_status = 'X' ) or abs(((SELECT SUM (pd1.qty_received) from po_dat as pd1

                     --inner join po_hdr on pd1.po_no = phd.po_no)-(SELECT SUM (pd2.qty_ordered) from po_dat as pd2 

                        --inner join po_hdr on pd2.po_no = phd.po_no) )/(SELECT SUM (pd1.qty_ordered)

                        -- from po_dat as pd1 inner join po_hdr on pd1.po_no = phd.po_no)) <111

                     --)

                     --(phd.po_status = 'X' ) or (((SELECT SUM (pd1.qty_received) from po_dat as pd1

                     -- inner join po_hdr on 

                        --pd1.po_no = phd.po_no)  - (SELECT SUM (pd2.qty_ordered) from po_dat as pd2 

                        --inner join po_hdr on pd2.po_no = phd.po_no) ) / (SELECT SUM (pd1.qty_ordered)

                        -- from po_dat as pd1 inner join po_hdr on pd1.po_no = phd.po_no) ) < -0.05)

                       --AND (phd.po_status = 'X' )or ((poda.qty_received - poda.qty_ordered)/poda.qty_ordered  < 0.05 )

                       --GROUP BY poda.po_no,poda.item_code,poda.item_name,poda.client_item,poda.qty_received,

                       --poda.qty_ordered,poda.unit,poda.item_price,phd.client_name,poda.curr_code,phd.eta_date,gr_dat.item_qty

                       --,phd.po_no,poda.line_no,gr_hdr.gr_date,gr_hdr.gr_no,phd.po_date,phd.client_code,phd.on_board_date,

                       --phd.po_status

            ORDER BY phd.po_no, poda.line_no, gr_hdr.gr_no

zuomingyu2 2011-03-31

打赏
举报



 and ( ((select sum(qty_received) from po_dat where po_no=poda.po_no) - (select sum(qty_ordered) from po_dat where po_no=poda.po_no)) / (select sum(qty_ordered) from po_dat where po_no=poda.po_no))< -0.05

我要的是这个条件

AcHerat 2011-03-31

打赏
举报



--上边复制错了



SELECT      poda.po_no,poda.line_no, poda.item_code, poda.item_name, poda.client_item, poda.qty_received, 

                        poda.qty_ordered, poda.unit, poda.item_price, 

                        ((poda.qty_received -poda.qty_ordered)/poda.qty_ordered) as po_re_dif,

                        phd.client_name, poda.curr_code, poda.qty_ordered - poda.qty_received AS reming, 

                        phd.eta_date, (gr_dat.item_qty - poda.qty_ordered) 

                        / poda.qty_ordered AS dif, (((SELECT SUM (pd1.qty_received) from po_dat as pd1 inner join po_hdr on 

                        pd1.po_no = phd.po_no)  - (SELECT SUM (pd2.qty_ordered) from po_dat as pd2 

                        inner join po_hdr on pd2.po_no = phd.po_no) ) / (SELECT SUM (pd1.qty_ordered)

                         from po_dat as pd1 inner join po_hdr on pd1.po_no = phd.po_no) ) AS gr_dif,

                         (poda.qty_ordered - poda.qty_received) 

                        * poda.item_price AS sy,  poda.qty_ordered * poda.item_price AS price_total, 

                        gr_hdr.gr_date, gr_hdr.gr_no, gr_dat.item_qty AS qty, 

                        poda.qty_received AS Expr1, phd.po_date, gr_dat.item_qty * poda.item_price AS gr_total_price, 

                        phd.client_code, gr_dat.item_qty

                        ,(select anly_code4 from item as its where its.item_code = poda.item_code) as anly_code4

                         ,phd.on_board_date,po_status

            FROM         po_dat as poda, po_hdr as phd, gr_dat, gr_hdr

            WHERE     poda.po_no = phd.po_no AND phd.po_no = gr_dat.ref_doc_no AND poda.item_code = gr_dat.item_code 

                      AND gr_dat.gr_no = gr_hdr.gr_no AND 

                                  (gr_hdr.gr_status <> 'C') AND (phd.po_status <> 'C') 

                      AND phd.po_no between  @po_no_fr_tmp and @po_no_to_tmp

                     AND CONVERT([VARCHAR](8),phd.eta_date , 112) between @po_eta_date_fr_tmp and @po_eta_date_to_tmp

                    AND gr_hdr.gr_no between @gr_no_fr_tmp and @gr_no_to_tmp

                    AND phd.client_code between @suppliers_fr and @suppliers_to

                     AND CONVERT([VARCHAR](8),phd.po_date , 112) between @po_date_fr and @po_date_to

                    AND CONVERT([VARCHAR](8),gr_hdr.gr_date , 112) between @gr_date_fr_tmp and @gr_date_to_tmp

					AND poda.po_no in (select po_no from po_dat group by po_no having sum(item_qty) > 10)

                     --AND ( 

                        --(phd.po_status = 'X' ) or abs(((SELECT SUM (pd1.qty_received) from po_dat as pd1

                     --inner join po_hdr on pd1.po_no = phd.po_no)-(SELECT SUM (pd2.qty_ordered) from po_dat as pd2 

                        --inner join po_hdr on pd2.po_no = phd.po_no) )/(SELECT SUM (pd1.qty_ordered)

                        -- from po_dat as pd1 inner join po_hdr on pd1.po_no = phd.po_no)) <111

                     --)

                     --(phd.po_status = 'X' ) or (((SELECT SUM (pd1.qty_received) from po_dat as pd1

                     -- inner join po_hdr on 

                        --pd1.po_no = phd.po_no)  - (SELECT SUM (pd2.qty_ordered) from po_dat as pd2 

                        --inner join po_hdr on pd2.po_no = phd.po_no) ) / (SELECT SUM (pd1.qty_ordered)

                        -- from po_dat as pd1 inner join po_hdr on pd1.po_no = phd.po_no) ) < -0.05)

                       --AND (phd.po_status = 'X' )or ((poda.qty_received - poda.qty_ordered)/poda.qty_ordered  < 0.05 )

                       --GROUP BY poda.po_no,poda.item_code,poda.item_name,poda.client_item,poda.qty_received,

                       --poda.qty_ordered,poda.unit,poda.item_price,phd.client_name,poda.curr_code,phd.eta_date,gr_dat.item_qty

                       --,phd.po_no,poda.line_no,gr_hdr.gr_date,gr_hdr.gr_no,phd.po_date,phd.client_code,phd.on_board_date,

                       --phd.po_status

            ORDER BY phd.po_no, poda.line_no, gr_hdr.gr_no

AcHerat 2011-03-31

打赏
举报



SELECT      poda.po_no,poda.line_no, poda.item_code, poda.item_name, poda.client_item, poda.qty_received, 

                        poda.qty_ordered, poda.unit, poda.item_price, 

                        ((poda.qty_received -poda.qty_ordered)/poda.qty_ordered) as po_re_dif,

                        phd.client_name, poda.curr_code, poda.qty_ordered - poda.qty_received AS reming, 

                        phd.eta_date, (gr_dat.item_qty - poda.qty_ordered) 

                        / poda.qty_ordered AS dif, (((SELECT SUM (pd1.qty_received) from po_dat as pd1 inner join po_hdr on 

                        pd1.po_no = phd.po_no)  - (SELECT SUM (pd2.qty_ordered) from po_dat as pd2 

                        inner join po_hdr on pd2.po_no = phd.po_no) ) / (SELECT SUM (pd1.qty_ordered)

                         from po_dat as pd1 inner join po_hdr on pd1.po_no = phd.po_no) ) AS gr_dif,

                         (poda.qty_ordered - poda.qty_received) 

                        * poda.item_price AS sy,  poda.qty_ordered * poda.item_price AS price_total, 

                        gr_hdr.gr_date, gr_hdr.gr_no, gr_dat.item_qty AS qty, 

                        poda.qty_received AS Expr1, phd.po_date, gr_dat.item_qty * poda.item_price AS gr_total_price, 

                        phd.client_code, gr_dat.item_qty

                        ,(select anly_code4 from item as its where its.item_code = poda.item_code) as anly_code4

                         ,phd.on_board_date,po_status

            FROM         po_dat as poda, po_hdr as phd, gr_dat, gr_hdr

            WHERE     poda.po_no = phd.po_no AND phd.po_no = gr_dat.ref_doc_no AND poda.item_code = gr_dat.item_code 

                      AND gr_dat.gr_no = gr_hdr.gr_no AND 

                                  (gr_hdr.gr_status <> 'C') AND (phd.po_status <> 'C') 

                      AND phd.po_no between  @po_no_fr_tmp and @po_no_to_tmp

                     AND CONVERT([VARCHAR](8),phd.eta_date , 112) between @po_eta_date_fr_tmp and @po_eta_date_to_tmp

                    AND gr_hdr.gr_no between @gr_no_fr_tmp and @gr_no_to_tmp

                    AND phd.client_code between @suppliers_fr and @suppliers_to

                     AND CONVERT([VARCHAR](8),phd.po_date , 112) between @po_date_fr and @po_date_to

                    AND CONVERT([VARCHAR](8),gr_hdr.gr_date , 112) between @gr_date_fr_tmp and @gr_date_to_tmp

					AND poda.po_no in (select po_no from po_dat group by po_no having (item_qty) > 10) --多了个sum

                     --AND ( 

                        --(phd.po_status = 'X' ) or abs(((SELECT SUM (pd1.qty_received) from po_dat as pd1

                     --inner join po_hdr on pd1.po_no = phd.po_no)-(SELECT SUM (pd2.qty_ordered) from po_dat as pd2 

                        --inner join po_hdr on pd2.po_no = phd.po_no) )/(SELECT SUM (pd1.qty_ordered)

                        -- from po_dat as pd1 inner join po_hdr on pd1.po_no = phd.po_no)) <111

                     --)

                     --(phd.po_status = 'X' ) or (((SELECT SUM (pd1.qty_received) from po_dat as pd1

                     -- inner join po_hdr on 

                        --pd1.po_no = phd.po_no)  - (SELECT SUM (pd2.qty_ordered) from po_dat as pd2 

                        --inner join po_hdr on pd2.po_no = phd.po_no) ) / (SELECT SUM (pd1.qty_ordered)

                        -- from po_dat as pd1 inner join po_hdr on pd1.po_no = phd.po_no) ) < -0.05)

                       --AND (phd.po_status = 'X' )or ((poda.qty_received - poda.qty_ordered)/poda.qty_ordered  < 0.05 )

                       --GROUP BY poda.po_no,poda.item_code,poda.item_name,poda.client_item,poda.qty_received,

                       --poda.qty_ordered,poda.unit,poda.item_price,phd.client_name,poda.curr_code,phd.eta_date,gr_dat.item_qty

                       --,phd.po_no,poda.line_no,gr_hdr.gr_date,gr_hdr.gr_no,phd.po_date,phd.client_code,phd.on_board_date,

                       --phd.po_status

            ORDER BY phd.po_no, poda.line_no, gr_hdr.gr_no

快溜 2011-03-31

打赏
举报

不应该啊，你怎么写的

zuomingyu2 2011-03-31

打赏
举报

[Quote=引用 13 楼 acherat 的回复:]

SQL code

SELECT poda.po_no,poda.line_no, poda.item_code, poda.item_name, poda.client_item, poda.qty_received,
poda.qty_ordered, poda.unit, poda.item_price,
……
[/Quote]

Msg 130, Level 15, State 1, Procedure report_po, Line 209
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

zuomingyu2 2011-03-31

打赏
举报

[Quote=引用 11 楼 ssp2009 的回复:]

SQL code
SELECT poda.po_no,poda.line_no, poda.item_code, poda.item_name, poda.client_item, poda.qty_received,
poda.qty_ordered, poda.unit, poda.item_price,
……
[/Quote]

Msg 147, Level 15, State 1, Procedure report_po, Line 209
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.