我如何在Oracle查询中获得不相关的交易交易? [英] how can i get distinct trades deal no in oracle query?

问题描述

我有两个桌子.我必须加入才能获得模式类型和卖方可用时间.

我的问题是我有多个具有相同trades_deal_no的呼叫选项，因为它们当然是选项.

他们不会全部选中.假设我在6个看涨期权中有两个交易编号.我想要的是只获取第一个看涨期权交易的交易号.

下面是我现在得到的以及想要的示例以及查询.

感谢您的帮助.

I have two tables. I have to join to be able to get the mode type and seller availability time.

The problem I have is that I have multiple call options with the same trades_deal_no because they are of course options.

They will not pick all of them. So say I have two trades deal numbers in 6 call options. What I want is to get only the first call option trades deal numbers.

Example of what I am getting now and what I want is below as well as my query.

Thanks for the help.

SELECT DISTINCT COP.CONTRACT_ID,
  COP.CALL_OPTION_TYPE_ID,
  COP.TRADES_DEAL_NO,
  COP.POR_TYPE_ID,
  COP.POR_NAME,
  CO.MODE_TYPE,
  TO_CHAR(co.seller_availability_by, 'hh24:mi:ss') AS availability_time,
  COP.MW                       AS MAX_MW
FROM v_call_option_por COP
INNER JOIN v_call_option CO
ON CO.contract_id      = COP.contract_id
AND CO.call_option_type_id = cop.call_option_type_id
WHERE COP.contract_id    = '7'
AND COP.eff_start_date    <= TRUNC(TO_DATE('12/13/2011','MM/DD/YYYY'))
AND COP.eff_end_date    >= TRUNC(TO_DATE('12/13/2011','MM/DD/YYYY'))
ORDER BY 4 ASC,
  2 ASC;

我现在得到的是:

contract_id call_option_type_id trades_deal_no por_type_id por_name mode_type Availability_time max_mw
7 1 285536-1 1沙发1 7:00:00 140
7 1 282118-1 1阿卡迪亚1 07:00:00 140
7 2 285536-1 1沙发1 07:00:00 140
7 2 282118-1 1阿卡迪亚1 07:00:00 140
7 3 285536-1 1沙发1 07:00:00 140
7 3 282118-1 1阿卡迪亚1 07:00:00 140
7 4 285536-1 1沙发1 07:00:00 140
7 4 282118-1 1阿卡迪亚1 07:00:00 140
7 5 285536-1 1沙发1 07:00:00 140
7 5 282118-1 1阿卡迪亚1 07:00:00 140
7 6 285536-1 1沙发1 07:00:00 140
7 6 282118-1 1阿卡迪亚1 07:00:00 140


我想要的是:

contract_id call_option_type_id trades_deal_no por_type_id por_name mode_type Availability_time max_mw
7 1 285536-1 1沙发1 7:00:00 140
7 1 282118-1 1 Acadia 1 07:00:00 140

What I am getting Now:

contract_id call_option_type_id trades_deal_no por_type_id por_name mode_type availability_time max_mw
7 1 285536-1 1 couch 1 07:00:00 140
7 1 282118-1 1 Acadia 1 07:00:00 140
7 2 285536-1 1 couch 1 07:00:00 140
7 2 282118-1 1 Acadia 1 07:00:00 140
7 3 285536-1 1 couch 1 07:00:00 140
7 3 282118-1 1 Acadia 1 07:00:00 140
7 4 285536-1 1 couch 1 07:00:00 140
7 4 282118-1 1 Acadia 1 07:00:00 140
7 5 285536-1 1 couch 1 07:00:00 140
7 5 282118-1 1 Acadia 1 07:00:00 140
7 6 285536-1 1 couch 1 07:00:00 140
7 6 282118-1 1 Acadia 1 07:00:00 140


What I want:

contract_id call_option_type_id trades_deal_no por_type_id por_name mode_type availability_time max_mw
7 1 285536-1 1 couch 1 07:00:00 140
7 1 282118-1 1 Acadia 1 07:00:00 140

推荐答案

不确定我是否理解正确，但是如果您想将结果限制在最小的trades_deal_no，那么可能是这样的:

Not sure if I understood correctly but if you want to limit the results to the smallest trades_deal_no, then perhaps something like:

SELECT COP.CONTRACT_ID,
  COP.CALL_OPTION_TYPE_ID,
  COP.TRADES_DEAL_NO,
  COP.POR_TYPE_ID,
  COP.POR_NAME,
  CO.MODE_TYPE,
  TO_CHAR(co.seller_availability_by, 'hh24:mi:ss') AS availability_time,
  COP.MW                       AS MAX_MW
FROM v_call_option_por COP
INNER JOIN v_call_option CO
ON CO.contract_id      = COP.contract_id
AND CO.call_option_type_id = cop.call_option_type_id
WHERE COP.contract_id    = '7'
AND COP.eff_start_date    <= TRUNC(TO_DATE('12/13/2011','MM/DD/YYYY'))
AND COP.eff_end_date    >= TRUNC(TO_DATE('12/13/2011','MM/DD/YYYY'))
-- ADDITION
AND NOT EXISTS(SELECT 1
               FROM v_call_option_por COP2
               WHERE CO.contract_id      = COP2.contract_id
               AND CO.call_option_type_id = cop2.call_option_type_id
               AND COP2.contract_id    = '7'
               AND COP2.eff_start_date    &lt;= TRUNC(TO_DATE('12/13/2011','MM/DD/YYYY'))
               AND COP2.eff_end_date    &gt;= TRUNC(TO_DATE('12/13/2011','MM/DD/YYYY'))
               AND COP2.TRADES_DEAL_NO < COP.TRADES_DEAL_NO)
-- ADDITION END
ORDER BY 4 ASC,
  2 ASC;

您并没有说明第一"看涨期权交易的意思.因此，我将假定数字最少的那个.如果在时间上相同的原理行得通，但在查询中将需要一个可用的日期列.

You''re not stating what you mean with the "First" call option trades deal numbers. So I''m going to assume the one with the lowest number. If it would be by time the same principle works, but it would need a usable date column in the query.

SELECT COP.CONTRACT_ID,
  Min(COP.CALL_OPTION_TYPE_ID) COP.CALL_OPTION_TYPE_ID,
  COP.TRADES_DEAL_NO,
  COP.POR_TYPE_ID,
  COP.POR_NAME,
  CO.MODE_TYPE,
  TO_CHAR(co.seller_availability_by, ''hh24:mi:ss'') AS availability_time,
  COP.MW                       AS MAX_MW
FROM v_call_option_por COP
INNER JOIN v_call_option CO
ON CO.contract_id      = COP.contract_id
AND CO.call_option_type_id = cop.call_option_type_id
WHERE COP.contract_id    = ''7''
AND COP.eff_start_date    <= TRUNC(TO_DATE(''12/13/2011'',''MM/DD/YYYY''))
AND COP.eff_end_date    >= TRUNC(TO_DATE(''12/13/2011'',''MM/DD/YYYY''))
GROUP BY COP.CONTRACT_ID,
  COP.TRADES_DEAL_NO,
  COP.POR_TYPE_ID,
  COP.POR_NAME,
  CO.MODE_TYPE,
  co.seller_availability_by,
  COP.MW
ORDER BY 4 ASC,
  2 ASC;

这篇关于我如何在Oracle查询中获得不相关的交易交易?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！