在另一个表上选择一列作为多列

I'm goin to start use mysql 5.1 community version.

我要开始使用mysql 5.1社区版。

First I have...

首先我有......

master_product_table

ID  Product_Code    Product_Name           Product_Details 
=====================================================================
1   000001         Raw Material 1        000001- Raw Material 1
2   000002         Raw Material 2        000002- Raw Material 2
3   000003         Raw Material 3        000003- Raw Material 3
4   000004         Raw Material 4        000004- Raw Material 4

where ID field is the PK

其中ID字段是PK

and master_document

Doc_no   Doc_date   Doc_type     Item_code1    Item_qty1     Item_price1     Item_code2     Item_qty2     Item_price2
=========================================================================================================================
000001   01-01-2013 BC. 2.3          1            200          $ 150             3             500           $800
000002   02-01-2013 BC. 2.7          2           1500          $ 800             4            6000          $2500
000003   03-01-2013 BC. 3.0          3           5000          $1500             1           12000          $8500
000004   04-01-2013 BC. 4.0          4          12000          $5000             2             750          $3000

where Doc_no field is the PK

其中Doc_no字段是PK

What I want to get is like this...

我想得到的就是这样......

Doc_no   Doc_date   Doc_type    Item_details1                Item_qty1    Item_price1       Item_details2                Item_qty2     Item_price2
========================================================================================================================================================
000001   01-01-2013 BC. 2.3    000001- Raw Material 1            200          $ 150       000003- Raw Material 3             500           $800
000002   02-01-2013 BC. 2.7    000002- Raw Material 2           1500          $ 800       000004- Raw Material 4            6000          $2500
000003   03-01-2013 BC. 3.0    000003- Raw Material 3           5000          $1500       000001- Raw Material 1           12000          $8500
000004   04-01-2013 BC. 4.0    000004- Raw Material 4          12000          $5000       000002- Raw Material 2             750          $3000

Any help on how to do this would be helpful thank you.

任何有关如何做到这一点的帮助都会有所帮助,谢谢。

3 个解决方案

#1

Try this:

SELECT md.Doc_no, md.Doc_date, md.Doc_type, 
       mp1.Product_Details Item_details1, md.Item_qty1, md.Item_price1, 
       mp2.Product_Details Item_details2, md.Item_qty2, md.Item_price2 
FROM master_document md 
INNER JOIN master_product_table mp1 ON md.Item_code1 = mp1.ID 
INNER JOIN master_product_table mp2 ON md.Item_code2 = mp2.ID ;

#2

Tried for first time SQL FIDDLE

第一次尝试SQL FIDDLE

Took approximately 30 mins

大约需要30分钟

Try the Following SQL Fiddle

尝试以下SQL小提琴

SELECT md.Doc_no, md.Doc_date, md.Doc_type, 
       pd1.Product_Details Item_details1, md.Item_qty1, md.Item_price1, 
       pd2.Product_Details Item_details2, md.Item_qty2, md.Item_price2 
FROM master_document md 
INNER JOIN master_product_table pd1 ON md.Item_code1 = pd1.ID 
INNER JOIN master_product_table pd2 ON md.Item_code2 = pd2.ID
order by md.Doc_no

#3

this is a quite basic question concerning SQL. Giving you a direct solution now will surely not help you in the future.

这是一个关于SQL的基本问题。现在给你一个直接的解决方案肯定不会帮助你。

Try to get a smaller example and start learning how to use 'SELECT x FROM y WHERE x = w', as well as 'SELECT x as z FROM y' (this renames the column x into z)

尝试获取一个较小的示例并开始学习如何使用'SELECT x FROM y WHERE x = w',以及'SELECT x as z FROM y'(这将列x重命名为z)

And then, if you get any more questions, add them here.

然后,如果您有任何疑问,请在此处添加。

Have fun ;)

玩的开心 ;)

#1