mysql - 如何在一个简单查询中合并两个单独的查询 - MySQL

Question

我想将两个单独的查询合并到一个简单的查询中。我怎样才能做到这一点？

查询:

select idProduct, date,price from products where idProduct like 'Prod01'

idProduct             Date         Price
    Pro01           2014-05-29      19.1
    Pro01           2014-05-29      18.8
    Pro01           2014-05-29      18.7
    Pro01           2014-05-29      18.9
    Pro01           2014-05-29      18.7
    Pro01           2014-05-29      18.5

select idProduct, date,price from products where idProduct like 'Prod02'

idProduct          Date         Price
Pro02           2014-05-29      29.1
Pro02           2014-05-29      28.8
Pro02           2014-05-29      28.7
Pro02           2014-05-29      28.9
Pro02           2014-05-29      28.7
Pro02           2014-05-29      28.5

我希望产品“Prod02”的价格位于第四列中。

我解释得很好吗？

基本上我需要这样的东西:

idProduct                     Date         Price of 'Prod01'   Price of 'Prod02'
   Pro01 / Pro02           2014-05-29      19.1                 28.1
   Pro01 / Pro02           2014-05-29      18.8                 26.1
   Pro01 / Pro02           2014-05-29      18.7                 24.1
   Pro01 / Pro02           2014-05-29      18.9                 22.1
   Pro01 / Pro02           2014-05-29      18.7                 21.1
   Pro01 / Pro02           2014-05-29      18.5                 22.1

Answer 1

SELECT *
FROM(
SELECT idProduct, Date,(Price) as Price01, '0' as Price02
FROM products
WHERE idProduct LIKE 'Prod01'
UNION
SELECT idProduct, Date,'0' as Price01, (Price) as Price02
FROM products 
WHERE idProduct like 'Prod02'
) as temp