如何在 sql 服务器 2008 中重写此查询

Question

WITH a
     AS (SELECT accen,
                accno,
                tranamt,
                COUNT(*) AS [b]
         FROM   dbo.tbl1
         WHERE  DATEt > 2 / 10 / 2013
         GROUP  BY accen,
                   accno,
                   tranamt
         HAVING COUNT(*) > 1)
SELECT code,
       COUNT(*)
FROM   a A
       INNER JOIN dbo.tbl1 t
         ON a.accen = q.accen
            AND a.accno = t.accno
            AND a.tranamt = t.tranamt
            AND t.datet > 2 / 10 / 2013
GROUP  BY code 

Answer 1

重写它的一种方法是

WITH a
     AS (SELECT *,
                COUNT(*) OVER (PARTITION BY accen, accno, tranamt ) AS [b]
         FROM   dbo.tbl1
         WHERE  DATEt > '20131002')
SELECT code,
       COUNT(*)
FROM   a
WHERE  b > 1
GROUP  BY code