选择sql查询问题 [英] select sql query problem

问题描述

我希望你能帮助我。


我正在尝试使用3个表生成一个报告 - sttakehm，sttakedm和stquem。


我写下面的查询，但它给出了unit_cost的所有值，我只需要与stquem.sequence_number = sttakedm.queue_reference和调整量匹配的单位成本。


关键是序列号在一行中，Kind =" C"和调整量在第二行，Kind =B。


我需要这个非常紧急，希望你能帮助我


SELECT sttakedm.card_number，sttakedm.product_code，sttakedm.adjustment_quantit，sttakedm.expected_quantity，sttakehm.warehouse，stquem.unit_cost，sttakehm.description1

FROM scheme.sttakedm sttakedm


INNER JOIN scheme.sttakehm on sttakehm.card_number = sttakedm.card_number

LEFT OUTER JOIN scheme.stquem on sttakehm.warehouse = stquem.warehouse


WHERE sttakehm.card_number = sttakedm.card_number AND sttakedm.card_number =''C03323''AND（stquem.sequence_number = sttakedm.queue_reference OR（sttakedm.kind =''B''或sttakedm.kind =''C''） ）和sttakedm.product_code = stquem.prod_code和sttakehm.warehouse = stquem.warehouse

I hope you can help me.

I am trying to generate a report using 3 tables - sttakehm, sttakedm and stquem.

I wrote below query but it is giving all values of unit_cost, I only need the unit cost which matches stquem.sequence_number=sttakedm.queue_reference and adjustment quantit.

The key is sequence number is in one line with Kind = "C" and adjustment quanity is on second line with Kind = "B".

I need this pretty urgent, I hope you can help me

SELECT sttakedm.card_number, sttakedm.product_code, sttakedm.adjustment_quantit, sttakedm.expected_quantity, sttakehm.warehouse, stquem.unit_cost, sttakehm.description1
FROM scheme.sttakedm sttakedm

INNER JOIN scheme.sttakehm on sttakehm.card_number = sttakedm.card_number
LEFT OUTER JOIN scheme.stquem on sttakehm.warehouse=stquem.warehouse

WHERE sttakehm.card_number = sttakedm.card_number AND sttakedm.card_number=''C03323'' AND (stquem.sequence_number=sttakedm.queue_reference OR (sttakedm.kind = ''B'' OR sttakedm.kind = ''C'')) AND sttakedm.product_code = stquem.prod_code AND sttakehm.warehouse=stquem.warehouse

推荐答案

示例数据和结果会有所帮助。我无法理解你在问什么。

Sample data and results would help. I have trouble understanding what you''re asking.

@Rabbit


嗨结果是这样的：< br $> b $ b股票方差模板

card_number description1仓库product_code adjustment_quantit expected_quantity unit_cost

C03323股票报价2011年12月1日FG 0015 0 0 16.9774

C03323股票拍卖2011年12月1日FG 0015 0 144 16.8842

C03323股票拍卖2011年12月1日FG 0015 0 144 16.8968

C03323股票拍卖2011年12月1日FG 0015 0 144 16.9774

来自sttakedm表的样本数据是：


card_number kind sequence_number queue_reference product_code bin_location status batch_code lot_number adjustment_quantit expected_quantity

C03323 C 000005 P3kKh！ 0030 0 0

C03323 B 000005 0030 A100 -35 107

C03323 S 000005 0030 A100 0 0

来自stquem表的样本数据为：


仓库产品sequence_number prod_code serial_number batch_number date_received bin_number lot_number expiry_date passed_inspection inspector_code inspection_date source_code conformity_ref quantity quantity_free unit_cost

FG 0030 P. * qc！ 0030 P15506 29/09/2011 0:00 A100 5193 Y 016593 0 0 38.9062

0 32.0154

FG 0030 MA ## G！ 0030 P00444 11/02/2005 0:00 A100 000493/001 Y 000493 0 0 32.0154

FG 0030 MAhy`！ 0030 P00491 18/02/2005 0:00 A100 000551/001 Y 000551 0 0 32.0154

FG 0030 NgOaL！ 0030 P09738 19/06/2008 0:00 A100 010345/001 Y 010345 0 0 32.0154

FG 0030 Nii> A！ 0030 P09896 10/07/2008 0:00 A100 010508/001 Y 010508 0 0 32.0154

table sttakehm仅用于创建卡号和仓库。该表中没有数据。它仅用于链接卡号，仓库和描述。


我希望这有帮助。

@Rabbit
Hi The result is coming like this:
Stock Variance Template

card_number description1 warehouse product_code adjustment_quantit expected_quantity unit_cost
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 0 16.9774
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 144 16.8842
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 144 16.8968
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 144 16.9774

Sample data from sttakedm table is:

card_number kind sequence_number queue_reference product_code bin_location status batch_code lot_number adjustment_quantit expected_quantity
C03323 C 000005 P3kKh! 0030 0 0
C03323 B 000005 0030 A100 -35 107
C03323 S 000005 0030 A100 0 0
And sample data from stquem table is:

warehouse product sequence_number prod_code serial_number batch_number date_received bin_number lot_number expiry_date passed_inspection inspector_code inspection_date source_code conformity_ref quantity quantity_free unit_cost
FG 0030 P.*qc! 0030 P15506 29/09/2011 0:00 A100 5193 Y 016593 0 0 38.9062
0 32.0154
FG 0030 MA##G! 0030 P00444 11/02/2005 0:00 A100 000493/001 Y 000493 0 0 32.0154
FG 0030 MAhy`! 0030 P00491 18/02/2005 0:00 A100 000551/001 Y 000551 0 0 32.0154
FG 0030 NgOaL! 0030 P09738 19/06/2008 0:00 A100 010345/001 Y 010345 0 0 32.0154
FG 0030 Nii>A! 0030 P09896 10/07/2008 0:00 A100 010508/001 Y 010508 0 0 32.0154

table sttakehm is only used to create card number and for which warehouse. There is no data from that table. It is used only to link card number, warehouse and description.

I hope this helps.

[quote = Rabbit; 3690606]示例数据结果会有所帮助。我无法理解你在问什么。[/

[quote=Rabbit;3690606]Sample data and results would help. I have trouble understanding what you''re asking.[/

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