代码之家 › 专栏 › 技术社区 › Chaitanya

查询的正确性

sql-server

Chaitanya · 技术社区 · 16 年前

我有兴趣知道我的问题有多正确张贴 here

注意:我不想用很多代码来填充这个地方,非常简短。非常感谢。

SELECT a.BookingId                         AS BookingId,
       CAST(b.TransactionDateTime AS DATE) AS TransactionDate
FROM   TC33_AuditTrial A
       JOIN TC33_AuditTrial b
       ON     a.AuditId                    = b.AuditId
WHERE  a.TransactionType                   = 'S'
AND    CAST(a.TransactionDateTime AS DATE) =
       (SELECT CAST(b.TransactionDateTime AS DATE)
       FROM    TC33_AuditTrial b
       WHERE   b.BookingId = a.BookingId
       AND     b.AuditId   =
               (SELECT MAX(b.AuditId)
               FROM    TC33_AuditTrial b
               WHERE   a.BookingId       = b.BookingId
               AND     b.TransactionType = 'R'
               )
       )
AND    a.TransactionValue =
       (SELECT SUM(b.TransactionValue)
       FROM    TC33_AuditTrial b
       WHERE   a.BookingId       = b.BookingId
       AND     b.TransactionType = 'R'
       )

2 回复 | 直到 16 年前

Martin Smith 16 年前

未删除

我最初的回答是

SELECT BookingId, CONVERT(VARCHAR(8), TransactionDateTime, 112) AS TransactionDate
FROM TC33_AuditTrial
GROUP BY BookingId, CONVERT(VARCHAR(8), TransactionDateTime, 112)
HAVING 
SUM(CASE TransactionType WHEN 'S' THEN TransactionValue END) <= 
SUM(CASE TransactionType WHEN 'R' THEN TransactionValue END)
ORDER BY BookingID

我很满意,因为它只是通过了一次数据。

然后我注意到规范的更新部分,它必须限制在包含预订条目的第一个日期。我能想到的唯一方法是添加一个子查询,这大大增加了查询的成本。

感觉可能有一些聪明的方法可以在不使用子查询的情况下获得这个限制,但如果是这样的话,到目前为止我还没有找到它!

WITH X AS
(
SELECT   BookingId,
         RANK() OVER(PARTITION BY BookingId ORDER BY CONVERT(VARCHAR(8), TransactionDateTime, 112)) AS Rnk,
         CONVERT(VARCHAR(8), TransactionDateTime, 112) AS TransactionDate,
         SUM(CASE TransactionType WHEN 'S' THEN TransactionValue END) AS S,
         SUM(CASE TransactionType WHEN 'R' THEN TransactionValue END) AS R
FROM     TC33_AuditTrial t1
GROUP BY BookingId,
         CONVERT(VARCHAR(8), TransactionDateTime, 112)
)

SELECT BookingId, TransactionDate
FROM X
WHERE Rnk = 1 AND S<=R
ORDER BY BookingID

8kb 16 年前

我也得跳进去。。。

SELECT
  BookingID,
  CONVERT(VARCHAR(8), TransactionDateTime, 112) AS TransactionDate
FROM (
SELECT 
  BookingID,
  TransactionDateTime,
  COALESCE(pvt.S,0) AS Sale,
  COALESCE(pvt.R,0) AS Receipt,
  ROW_NUMBER() OVER (
    PARTITION BY BookingID ORDER BY (TransactionDateTime)) AS Rn
FROM (
  SELECT
    BookingID,
    DATEADD(DD, DATEDIFF(DD,0,TransactionDateTime), 0) AS TransactionDateTime,
    TransactionType,
    TransactionValue
  FROM TC33_AuditTrial
  ) t
PIVOT (
  SUM(TransactionValue)
  FOR TransactionType IN (S,R)
) AS pvt
) t2
WHERE Receipt >= Sale
AND Rn = 1