sql - 将 SQL 查询中的值除以 1024 以获得以 KB 为单位的大小

Question

我按照以下查询提取字节大小:

 SELECT CASE_NUM, 
        CUST_CODE, 
        CUST_NAME, 
       ISNULL(C.[NR OF ATTACHMENTS], 0) 'NR OF ATTACHMENTS', 
       ISNULL(C.[SIZE OF ATTACHMENTS KB], 0) 'SIZE OF ATTACHMENTS KB' 
      FROM CUSTCASE
 LEFT OUTER JOIN (
       SELECT B.CASE_CODE, 
              ISNULL(COUNT(B.ATTACHED_DOC_ID), 0) 'NR OF ATTACHMENTS',  
              ISNULL(SUM(B.DOC_SIZE), 0) 'SIZE OF ATTACHMENTS KB' 
       FROM
       (SELECT CASE_CODE, 
               ATTACHED_DOC_ID, 
               ISNULL(A.DOC_SIZE, 0) 'DOC_SIZE' 
        FROM CONTACT
        LEFT OUTER JOIN (SELECT DOC_ID, 
                                DATALENGTH(DATA) 'DOC_SIZE'
                         FROM ATTACHED_DOCS) A 
       ON CONTACT.ATTACHED_DOC_ID = A.DOC_ID
       WHERE CONTACT.CASE_CODE IS NOT NULL) B
       GROUP BY B.CASE_CODE
    ) C 
    ON CUSTCASE.CASE_CODE = C.CASE_CODE 
    where  ISNULL(C.[NR OF ATTACHMENTS], 0) > 0

要获得 KB 的大小，我需要除以 1024 但无法在这种情况下应用它。

Answer 1

SELECT CASE_NUM, CUST_CODE, CUST_NAME, ISNULL(C.[NR OF ATTACHMENTS], 0) 'NR OF ATTACHMENTS', CASE WHEN ISNULL(C.[SIZE OF ATTACHMENTS KB], 0) = 0 THEN 0 ELSE C.[SIZE OF ATTACHMENTS KB]/1024) END AS 'SIZE OF ATTACHMENTS KB' 
FROM CUSTCASE
LEFT OUTER JOIN (SELECT B.CASE_CODE, ISNULL(COUNT(B.ATTACHED_DOC_ID), 0) 'NR OF ATTACHMENTS', CASE WHEN ISNULL(SUM(B.DOC_SIZE), 0) = 0 THEN 0 ELSE SUM(B.DOC_SIZE)/1024 END AS  'SIZE OF ATTACHMENTS KB' 
                 FROM (SELECT CASE_CODE, ATTACHED_DOC_ID, ISNULL(A.DOC_SIZE, 0) 'DOC_SIZE' 
                       FROM CONTACT
                       LEFT OUTER JOIN (SELECT DOC_ID, DATALENGTH(DATA) 'DOC_SIZE'
                                        FROM ATTACHED_DOCS) A ON CONTACT.ATTACHED_DOC_ID = A.DOC_ID
                       WHERE CONTACT.CASE_CODE IS NOT NULL) B
                       GROUP BY B.CASE_CODE) C ON CUSTCASE.CASE_CODE = C.CASE_CODE 
WHERE ISNULL(C.[NR OF ATTACHMENTS], 0) > 0