此查询基于事务表编写
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演示.
