试图结合两个硬SQL查询 [英] Trying to combine two hard SQL queries

问题描述

我需要的是这一个

  SELECT  r。 refnam，
 t.tstring，
 rc.cnt namecounter 
  FROM  refdev r 
  INNER   JOIN （ SELECT  refnam，
 COUNT（*）cnt 
  FROM  refdev rc 
  GROUP   BY  refnam 
）rc 
  ON  rc.refnam = r.refnam 
  LEFT   OUTER   JOIN  texte t 
  ON  r.sigtnr = t.textnr 
  WHERE  rc.cnt =  3  
  ORDER   BY  r.refnam 

结合

  SELECT  d.alarmzu 
  FROM  devtzu d， OUTER  refdev_DEF RD 
  WHERE  D.zustnr = R.zustnr 
  AND  D.dvtypnr = RD.dvtypnr  AND  R.refnam = RD.refnam 

所以基本上我想把alarmzu添加到第一个查询中，有两个额外的连接。





语法让我很生气！

解决方案

 选择 a.refnam，a.tstring，a.namecounter，a.alarmzu 来自（ SELECT  r.refnam，
 t.tstring，
 rc.cnt namecounter 
  FROM  refde vr 
  INNER   JOIN （ SELECT  refnam，
 COUNT（*）cnt 
  FROM  refdev rc 
  GROUP   BY  refnam 
）rc 
  ON  rc.refnam = r.refnam 
  LEFT   OUTER   JOIN  texte t 
  ON  r.sigtnr = t.textnr 
  WHERE  rc.cnt =  3  
  ORDER   BY  r .refnam 
 
  union  
 
  SELECT  d。 alarmzu，   as  tstring， 0   as  cnt 
  FROM  devtzu d， OUTER  refdev_DEF RD 
  WHERE  D.zustnr = R.zustnr 
  AND  D.dvtypnr = RD.dvtypnr  AND  R.refnam = RD.refnam 
）a 

我希望这会对你有所帮助

What i need is this one

SELECT r.refnam, 
       t.tstring,
       rc.cnt namecounter
FROM refdev r
INNER JOIN (SELECT refnam, 
                   COUNT(*) cnt
              FROM refdev rc
          GROUP BY refnam
           ) rc 
        ON rc.refnam = r.refnam
LEFT OUTER JOIN texte t
             ON r.sigtnr = t.textnr
WHERE rc.cnt = 3
ORDER BY r.refnam

Combined with

SELECT d.alarmzu
FROM devtzu d, OUTER refdev_DEF RD
WHERE D.zustnr = R.zustnr
AND D.dvtypnr = RD.dvtypnr AND R.refnam = RD.refnam

So basically i want to have alarmzu added to the first query, with two extra joins.


The syntax is driving me mad!!

解决方案

select a.refnam,a.tstring,a.namecounter,a.alarmzu from (SELECT r.refnam, 
       t.tstring,
       rc.cnt namecounter
FROM refdev r
INNER JOIN (SELECT refnam, 
                   COUNT(*) cnt
              FROM refdev rc
          GROUP BY refnam
           ) rc 
        ON rc.refnam = r.refnam
LEFT OUTER JOIN texte t
             ON r.sigtnr = t.textnr
WHERE rc.cnt = 3
ORDER BY r.refnam

union

SELECT d.alarmzu,"" as tstring,0 as cnt
FROM devtzu d, OUTER refdev_DEF RD
WHERE D.zustnr = R.zustnr
AND D.dvtypnr = RD.dvtypnr AND R.refnam = RD.refnam
)a

I hope this will be helpful for you.

这篇关于试图结合两个硬SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！