How can I combine 2 queries?

问题:

I have these below queries in fastreport. I would like to combine them together into one query. How can I do this ?

SELECT 
    EAMEMID, COUNT (EAMATTNSTATUS)/2 as ABSENTDAYS1
FROM 
    viewDAILYSUM  
WHERE 
    (EAMEMID = :EAMEMID) 
    AND (EAMDATE BETWEEN :FDate AND :TDate)
    AND (EAMATTNSTATUS IN (7, 8))           
GROUP BY 
    EAMEMID  
ORDER BY 
    EAMEMID

---

SELECT 
    EAMEMID, COUNT(DISTINCT EAMDATE) ABSENTDAYS        
FROM 
    viewDAILYSUM  
WHERE 
    (EAMEMID = :EAMEMID) 
    AND (EAMDATE BETWEEN :FDate AND :TDate)
    AND (EAMATTNSTATUS IN (6))
GROUP BY 
    EAMEMID
ORDER BY 
    EAMEMID

回答1:

You can use conditional aggregation:

SELECT EAMEMID,
       SUM(CASE WHEN EAMATTNSTATUS in (7, 8) THEN 1 ELSE 0 END) / 2 as ABSENTDAYS1,
       COUNT(DISTINCT CASE WHEN EAMATTNSTATUS in (6) THEN EAMDATE
             END) as ABSENTDAYS  
FROM viewDAILYSUM  
WHERE (EAMEMID = :EAMEMID) AND
      (EAMDATE between :FDate and :TDate)
GROUP BY EAMEMID  
ORDER BY EAMEMID

回答2:

You can use union same way:

SELECT * FROM
(
  SELECT 
    1 AS NUMQUERY, EAMEMID, COUNT(EAMATTNSTATUS)/2 as ABSENTDAYS
  FROM 
    viewDAILYSUM  
  WHERE 
    (EAMEMID = :EAMEMID) 
    AND (EAMDATE BETWEEN :FDate AND :TDate)
    AND (EAMATTNSTATUS IN (7, 8))           
  GROUP BY 
    EAMEMID

  UNION

  SELECT 
    2, EAMEMID, COUNT(DISTINCT EAMDATE)        
  FROM 
    viewDAILYSUM  
  WHERE 
    (EAMEMID = :EAMEMID) 
    AND (EAMDATE BETWEEN :FDate AND :TDate)
    AND (EAMATTNSTATUS IN (6))
  GROUP BY 
    EAMEMID
)
ORDER BY NUMQUERY, EAMEMID

回答3:

You can do a join (left because the two select are not the same eamemid) on the dynamic tables

select 
    t1.EAMEMID, t1.ABSENTDAYS1, t2.ABSENTDAYS 
from
    (SELECT 
         EAMEMID, COUNT (EAMATTNSTATUS)/2 as ABSENTDAYS1
     FROM 
         viewDAILYSUM  
     WHERE 
         (EAMEMID = :EAMEMID) 
         AND (EAMDATE BETWEEN :FDate AND :TDate)
         AND (EAMATTNSTATUS IN (7, 8))           
     GROUP BY
         EAMEMID) t1
left join
    (SELECT 
         EAMEMID, COUNT(DISTINCT EAMDATE) ABSENTDAYS        
     FROM  
         viewDAILYSUM  
     WHERE 
         (EAMEMID = :EAMEMID) 
         AND (EAMDATE BETWEEN :FDate AND :TDate)
         AND (EAMATTNSTATUS IN (6))                                                             
     GROUP BY 
         EAMEMID) t2 on t1.EAMEMID = t2.EAMEMID
order by
    t1.EAMEMID