Sql按计数加入Group

发布时间：2020-12-12 06:53:04 所属栏目：MsSql教程来源：网络整理

导读：此查询基于事务表编写 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 recvdE003 24E001 2 我

此查询基于事务表编写

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名称是save,我怎么可以将这个表加入Employee master表

我想把这个拿出去

Ecode Ename Recvd
E003   jas   24
E001   deepu  14

解决方法

尝试

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,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演示.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!