Sql 按计数加入组

Question

这个查询是基于事务表编写的

SELECT Ecode,COUNT(*) AS recvd 
FROM Transaction_tbl 
WHERE Locid=5 AND dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' 
GROUP BY Ecode 
ORDER BY Ecode DESC 

在执行这个查询时，我是这样出去的

Ecode        recvd
E003           24
E001            2

我还有一张像 Employee master 的表，对应的 Employee name 是保存的，我如何将此表加入 Employee master 表

我想像这样出去

Ecode Ename Recvd
E003   jas   24
E001   deepu  14

Answer 1

尝试

SELECT e.Ecode, e.Ename, q.recvd
  FROM Employee e JOIN
(
    SELECT Ecode,COUNT(*) Recvd 
      FROM Transaction_tbl 
     WHERE Locid = 5 
       AND dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' 
     GROUP BY Ecode 
) q ON e.Ecode = q.Ecode
 ORDER BY e.Ecode DESC

要不就

SELECT e.Ecode, e.Ename, COUNT(*) Recvd 
  FROM Employee e JOIN Transaction_tbl t
    ON e.Ecode = t.Ecode
 WHERE Locid = 5 
   AND dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' 
 GROUP BY e.Ecode DESC

这是 SQLFiddle 演示。