时间戳

首页 > 解决方案 > 我不知道如何删除sql oracle pentaho cde中的重复行

sql - 我不知道如何删除sql oracle pentaho cde中的重复行

问题描述

早上好我的问题是，我正在 pentaho 中创建一个仪表板。我不知道我是否必须使用 pentaho 或 sql 来解决问题。为了能够成为我更喜欢sql。我有很多行显示我用来做不同的总和。我想要的不是显示重复的线条。sql如下：

    SELECT p.pname                                                              AS project, 
       t.pname                                                              AS type, 
       c.cname                                                              AS component, 
       Sum(w.timeworked / 3600) OVER (partition BY p.pname)                 AS suma_proyecto, 
       Sum(w.timeworked / 3600) OVER (partition BY p.pname,t.pname)         AS suma_proyecto_tipo,
       Sum(w.timeworked / 3600) OVER (partition BY c.cname,t.pname,p.pname) AS sum_by_all, 
       Sum(w.timeworked / 3600) OVER (partition BY c.cname)                 AS sum_by_component
FROM   jira.jiraissue j, 
       jira.worklog w, 
       jira.project p, 
       jira.issuetype t, 
       jira.component c, 
       jira.nodeassociation na 
WHERE  w.issueid=j.id 
AND    j.project=p.id 
AND    na.source_node_id = j.id 
AND    na.sink_node_entity = 'Component' 
AND    na.source_node_entity = 'Issue' 
AND    na.sink_node_id=c.id 
AND    t.id=j.issuetype 
AND    w.author=( 
       CASE 
              WHEN ${Autor} = ' Todos' THEN author 
              ELSE ${Autor} 
       END) 
AND    p.pname= ( 
       CASE 
              WHEN ${Proyecto}= ' Todos' THEN p.pname 
              ELSE ${Proyecto} 
       END) 
AND    t.pname=( 
       CASE 
              WHEN ${Tipo}= ' Todos' THEN t.pname 
              ELSE ${tipo} 
       END) 
AND    c.cname=( 
       CASE 
              WHEN ${Componente}= ' Todos' THEN cname 
              ELSE ${Componente} 
       END) 
AND    to_char(w.startdate,'yyyy-mm-dd') >=${FromDate} 
AND    to_char(w.startdate,'yyyy-mm-dd') <=${ToDate}

在此处输入图像描述

标签： sqloraclepentaho

解决方案

在 SQL 中，您可以为此目的使用 DISTINCT 关键字。

 SELECT DISTINCT p.pname                                                              AS project, 
   t.pname                                                              AS type, 
   c.cname                                                              AS component, 
   Sum(w.timeworked / 3600) OVER (partition BY p.pname)                 AS suma_proyecto, 
   Sum(w.timeworked / 3600) OVER (partition BY p.pname,t.pname)         AS suma_proyecto_tipo,
   Sum(w.timeworked / 3600) OVER (partition BY c.cname,t.pname,p.pname) AS sum_by_all, 
   Sum(w.timeworked / 3600) OVER (partition BY c.cname)                 AS sum_by_component
FROM   jira.jiraissue j, 
   jira.worklog w, 
   jira.project p, 
   jira.issuetype t, 
   jira.component c, 
   jira.nodeassociation na 
WHERE  w.issueid=j.id 
AND    j.project=p.id 
AND    na.source_node_id = j.id 
AND    na.sink_node_entity = 'Component' 
AND    na.source_node_entity = 'Issue' 
AND    na.sink_node_id=c.id 
AND    t.id=j.issuetype 
AND    w.author=( 
   CASE 
          WHEN ${Autor} = ' Todos' THEN author 
          ELSE ${Autor} 
   END) 
AND    p.pname= ( 
   CASE 
          WHEN ${Proyecto}= ' Todos' THEN p.pname 
          ELSE ${Proyecto} 
   END) 
AND    t.pname=( 
   CASE 
          WHEN ${Tipo}= ' Todos' THEN t.pname 
          ELSE ${tipo} 
   END) 
AND    c.cname=( 
   CASE 
          WHEN ${Componente}= ' Todos' THEN cname 
          ELSE ${Componente} 
   END) 
AND    to_char(w.startdate,'yyyy-mm-dd') >=${FromDate} 
AND    to_char(w.startdate,'yyyy-mm-dd') <=${ToDate}

推荐阅读