从mysql表中获取数据

I have a table with following format:

我有一个表格格式如下:

prod_code   prod_pos      prod_nm     prod_price

1           1             product1     100

1           2             product2     200

1           3             product3     400

1           4             product4     500

Now I need to fetch the record in following manner

现在我需要以下面的方式获取记录

product code, product_nm and productprice where prod_pos='1' and product_pos and prod_prod_price where prod_nm='product4'

产品代码,product_nm和productprice,其中prod_pos ='1',product_pos和prod_prod_price,其中prod_nm ='product4'

Its bit complicated may be confusing too but please help me with this guys.

它有点复杂也可能令人困惑,但请帮助我这些家伙。

Thanks in advance

提前致谢

4 个解决方案

#1

Taking a wild stab in the dark:

在黑暗中采取野刺:

SELECT * 
  FROM table1
 WHERE prod_pos = 1
    OR prod_nm = 'product4';

#2

This should do it

这应该做到这一点

select prod_code
       , Prod_NM
       , Prod_Price 
FROM yourtable 
WHERE 
Prod_Pos = 1 
 AND 
   Prod_NM = 'Product4';

#3

SELECT * 
FROM product_table
WHERE 
   prod_pos = 1 
AND 
   prod_NM = 'Product4';

or if u want this 1 column less:

或者如果你想减少这一列:

SELECT 
  prod_code as p_code_or_p_pos
  , prod_price
  , prod_nm
FROM product_table
WHERE 
   Prod_Pos = 1 
union ALL
SELECT 
  product_pos as p_code_or_p_pos
  , prod_price
  , ''
FROM product_table
WHERE 
   Prod_Pos = 1

#4

EDIT:

SELECT A.PRODUCT_CODE FIELD1, A.PRODUCT_NM FIELD2, A.prod_price FIELD3,
B.prod_pos FIELD4, B.prod_price FIELD5
FROM product_table A, product_table B
WHERE  A.prod_pos = 1 AND B.prod_nm = 'product4'

EDIT AGAIN:

with product_table as
(
select 1  prod_code,         1    prod_pos,         'product1'  prod_nm,   100 prod_price from dual
union
select 1  prod_code,         2    prod_pos,         'product2'  prod_nm,   200 prod_price from dual
union
select 1  prod_code,         3    prod_pos,         'product3'  prod_nm,   300 prod_price from dual
union
select 1  prod_code,         4    prod_pos,         'product4'  prod_nm,   500 prod_price from dual

)
SELECT A.prod_code FIELD1, A.prod_nm FIELD2, A.prod_price FIELD3,
B.prod_pos FIELD4, B.prod_price FIELD5
FROM product_table A, product_table B
WHERE  A.prod_pos = 1 AND B.prod_nm = 'product4'

Gives following output for the input given in question:

为给出的输入提供以下输出:

FIELD1                 FIELD2   FIELD3                 FIELD4                 FIELD5                 
---------------------- -------- ---------------------- ---------------------- -------------
1                      product1 100                    4                      500

ORIGINAL ANSWER:

Check if you need something like this:

检查您是否需要这样的东西:

SELECT 'TYPE1' TYPE, product_code FIELD_1 , product_nm FIELD_2
WHERE prod_pos = 1

UNION 
SELECT 'TYPE2' TYPE, prod_pos FIELD_1 , prod_price FIELD_2
WHERE prod_nm = 'product4';

This will return an output like

这将返回类似的输出

TYPE   |  FIELD_1   | FIELD_2
TYPE1  |  1         | product1
TYPE2  |  4         | 500

You can access in the same resultset with FIELD_1 and FIELD_2 differentiated by type

您可以使用FIELD_1和FIELD_2按类型区分相同的结果集

#1