我需要将T-SQL查询转换为Oracle支持的查询的帮助 [英] I need help converting T-SQL query to Oracle supported query

问题描述

我正在SQL Server中执行此查询，并且工作正常，但是当我尝试在Oracle中执行它时，它没有给出相同的结果.

I am executing this query in SQL Server and it is working fine but when I try to execute it in Oracle, it is not giving the same results.

您可以在我的附带照片中看到一位客户的数据，该客户的代码为1，2,4，8，他的代码为1,2,4时应该得到0.70的值，然后代码为8的他应该得到0.70的值取0.75，因此相乘后应返回0.52作为值.我在Oracle中通过用nvl替换为null来尝试了它，但是它返回1而不是0.52.请帮助我将此查询转换为oracle支持的查询，该查询将返回相同的结果.

You can see in my attached photo the data of one customer, which have got the code 1, 2,4, 8 and he should get 0.70 value for having code 1,2,4 and then for having code 8 he should get 0.75 so after multiplication it should return 0.52 as value. I tried it in Oracle by replacing is null by nvl but it returned 1 instead of 0.52. Please help me convert this query in an oracle supported query which will return the same results.

这是我的查询

 SELECT [id] ,[name],r = isnull(nullif(
    max(CASE WHEN [code]  IN (1,2,4) then 0.70 else 0 end)
  ,0),1)
* isnull(nullif(
   min(CASE WHEN [code]  IN (1,2) then 0 else 1 end) 
    * max(CASE WHEN [code]  IN (4) then 0.20 else 0 end)
  ,0),1)
* isnull(nullif(
    max(CASE WHEN [code] IN (8) then 0.75 else 0 end)
  ,0),1)
  FROM  (values (1, 'ali',4)
        ,(1, 'ali',1)
        ,(1, 'ali',8)
        ,(1, 'ali',2)
        ,(2, 'sunny',1)
     ,(4, 'arslan',4)) as t(id, name,code)
  GROUP BY id, name;

推荐答案

如果我了解您的要求(即，在每种情况下，ID/名称组合都需要指定所有代码)，则此可能会做你所追求的.如果您想将答案保留在小数点后2位，则可能需要在val列上添加某种trunc/floor/round函数，

If I understand what you're after (ie. for each of the cases, the id/name combination needs to have all the codes specified), then this will probably do what you're after. You may want to add some sort of trunc/floor/round function on the val column if you're after the answer to 2 decimal places, though:

with t as (select 1 id, 'ali' name, 4 code from dual union all
           select 1 id, 'ali' name, 1 code from dual union all
           select 1 id, 'ali' name, 8 code from dual union all
           select 1 id, 'ali' name, 2 code from dual union all
           select 2 id, 'ali' name, 4 code from dual union all
           select 2 id, 'ali' name, 8 code from dual union all
           select 3 id, 'bob' name, 1 code from dual union all
           select 3 id, 'bob' name, 2 code from dual union all
           select 3 id, 'bob' name, 8 code from dual),
   res as (select id,
                  name,
                  case when count(distinct case when code in (1, 2, 4) then code end) = 3 then 0.7 
                       when count(distinct case when code in (1, 2) then code end) = 2 then 0.5
                       else 1
                  end case_1_2_and_poss_4,
                  case when count(distinct case when code = 8 then code end) = 1 then 0.75 else 1 end case_8
           from   t
           group by id, name)
select id,
       name,
       case_1_2_and_poss_4 * case_8 val
from   res;

        ID NAME        VAL
---------- ---- ----------
         1 ali       0.525
         2 ali        0.75
         3 bob       0.375

这篇关于我需要将T-SQL查询转换为Oracle支持的查询的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！