SQL查询问题. [英] SQL Query problem.

问题描述

我有两个表:
采购:
customer_id Purchase_value
1200
2 300
3 400

特价:
customer_id Sale_value
1200
2 100
5 500
7 100

我想以以下格式从上述两个表中选择数据
customer_id Purchase_value Sale_value
1200200
2300100
3 400 0
5 0 500
7 0 100

有人可以帮我吗?
在此先感谢

I have two table:
Purchase:
customer_id purchase_value
1 200
2 300
3 400

Sale:
customer_id Sale_value
1 200
2 100
5 500
7 100

I want to select data from the above two table in the following format
customer_id purchase_value Sale_value
1 200 200
2 300 100
3 400 0
5 0 500
7 0 100

can any body help me??
thanks in advance

推荐答案

首先创建表结构.

运行类似
的查询

First create the table structure.

The run a query like

select (customer_id, purchase_value, Sale_value)
into NewTable 
from Purchase 
inner join Sale on Purchase.customer_id = Sale.customer_id

尝试，

SELECT 
  customer_id, purchase_value, Sale_value
FROM 
  Purchase
FULL JOIN 
  Sales
ON Purchase.customer_id = Sales.customer_id

在没有定义值的地方，这不会给您"0"作为值.

This would not give you ''0'' as the value in the places where no value is defined.

您可以按照查询"行所述.我是在ORACLE 10克上做到的

SELECT C.ID，NVL(C.Sale_value，``0'')|| "" || NVL(PC.purchase_value，``0'')AMT
从销售C，购买PC
其中PC.ID(+)= C.ID
AND C.ID不为空
UNION
SELECT PC.ID，NVL(C.Sale_value，``0'')|| "" || NVL(PC.purchase_value，``0'')AMT
从销售C，购买PC
其中C.ID(+)= PC.ID
AND PC.ID不为空

You can follow the Query bellow. I did it on ORACLE 10 g

SELECT C.ID, NVL(C.Sale_value,''0'')|| '' '' || NVL(PC.purchase_value ,''0'') AMT
FROM SALES C, PURCHASE PC
WHERE PC.ID(+) = C.ID
AND C.ID IS NOT NULL
UNION
SELECT PC.ID, NVL(C.Sale_value,''0'')|| '' '' || NVL(PC.purchase_value ,''0'') AMT
FROM SALES C, PURCHASE PC
WHERE C.ID(+) = PC.ID
AND PC.ID IS NOT NULL

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