MySQL: 'ROUND' 附近的语法不正确

Question

我尝试从多个表中提取数据集，然后在提取数据时计算年龄，但无法使用 round 来计算年龄。

建议会很棒!

    SELECT client.ClientNumber
         , client.ClientActive AS Active
         , invoice.ClientNumber
         , invoice.InvoiceDate AS InvoiceDate
         , invoicedetail.InvoiceNumber AS Invoice
         , invoicedetail.ClientNumber
         , invoicedetail.Extended AS Cost
         , invoicedetail.ServiceDate AS Serviced
    FROM
      dbo.client
      INNER JOIN dbo.invoice
        ON client.ClientNumber = invoice.ClientNumber
      INNER JOIN dbo.invoicedetail
        ON invoice.ClientNumber = invoicedetail.ClientNumber
    WHERE
      client.ClientActive = 'Y'
    GROUP BY
      client.ClientNumber
    , client.ClientActive
    , invoice.ClientNumber
    , invoice.InvoiceDate
    , invoicedetail.InvoiceNumber
    , invoicedetail.ServiceDate
    , invoicedetail.Extended
    , invoicedetail.ClientNumber
    , invoicedetail.ClientNumber
    ORDER BY
      InvoiceDate

    ROUND(DATEDIFF(hour,CASE WHEN [InvoiceDate]>= GETDATE() THEN NULL ELSE [InvoiceDate] END, getdate())/8766.0,2) AS [ClientAge],
            (CASE WHEN ROUND(DATEDIFF(hour,CASE WHEN [InvoiceDate]>= GETDATE() THEN NULL ELSE [InvoiceDate] END, getdate())/8766.0,2)< 0.5 THEN '0-5 months'         
                     WHEN ROUND(DATEDIFF(hour,CASE WHEN [InvoiceDate]>= GETDATE() THEN NULL ELSE [InvoiceDate] END, getdate())/8766.0,2) BETWEEN 0.5 AND 0.99 THEN '6-11 months'
                     WHEN ROUND(DATEDIFF(hour,CASE WHEN [InvoiceDate]>= GETDATE() THEN NULL ELSE [InvoiceDate] END, getdate())/8766.0,2) BETWEEN 1 AND 5.99 THEN '1-5 years'
                     WHEN ROUND(DATEDIFF(hour,CASE WHEN [InvoiceDate]>= GETDATE() THEN NULL ELSE [InvoiceDate] END, getdate())/8766.0,2) BETWEEN 6 AND 10.99 THEN '6-10 years'
                     WHEN ROUND(DATEDIFF(hour,CASE WHEN [InvoiceDate]>= GETDATE() THEN NULL ELSE [InvoiceDate] END, getdate())/8766.0,2) BETWEEN 11 AND 15.99 THEN '11-15 years'
                     WHEN ROUND(DATEDIFF(hour,CASE WHEN [InvoiceDate]>= GETDATE() THEN NULL ELSE [InvoiceDate] END, getdate())/8766.0,2)>=16 THEN '16+ years'
                END) as [ClientAgeGroup]    

Answer 1

如果您希望将 ROUND 的结果包含在结果集中，则需要将其移至 SELECT 之后的字段列表中:

SELECT client.ClientNumber
     , client.ClientActive AS Active
     , invoice.ClientNumber
     , invoice.InvoiceDate AS InvoiceDate
     , invoicedetail.InvoiceNumber AS Invoice
     , invoicedetail.ClientNumber
     , invoicedetail.Extended AS Cost
     , invoicedetail.ServiceDate AS Serviced
     , ROUND(DATEDIFF(hour,CASE WHEN InvoiceDate>= GETDATE() THEN NULL ELSE    InvoiceDate END, getdate())/8766.0,2) AS [ClientAge],
        (CASE WHEN ROUND(DATEDIFF(hour,CASE WHEN InvoiceDate>= GETDATE() THEN NULL ELSE InvoiceDate END, getdate())/8766.0,2)< 0.5 THEN '0-5 months'         
                 WHEN ROUND(DATEDIFF(hour,CASE WHEN InvoiceDate>= GETDATE() THEN NULL ELSE InvoiceDate END, getdate())/8766.0,2) BETWEEN 0.5 AND 0.99 THEN '6-11 months'
                 WHEN ROUND(DATEDIFF(hour,CASE WHEN InvoiceDate>= GETDATE() THEN NULL ELSE InvoiceDate END, getdate())/8766.0,2) BETWEEN 1 AND 5.99 THEN '1-5 years'
                 WHEN ROUND(DATEDIFF(hour,CASE WHEN InvoiceDate>= GETDATE() THEN NULL ELSE InvoiceDate END, getdate())/8766.0,2) BETWEEN 6 AND 10.99 THEN '6-10 years'
                 WHEN ROUND(DATEDIFF(hour,CASE WHEN InvoiceDate>= GETDATE() THEN NULL ELSE InvoiceDate END, getdate())/8766.0,2) BETWEEN 11 AND 15.99 THEN '11-15 years'
                 WHEN ROUND(DATEDIFF(hour,CASE WHEN InvoiceDate>= GETDATE() THEN NULL ELSE InvoiceDate END, getdate())/8766.0,2)>=16 THEN '16+ years'
            END) as [ClientAgeGroup]
FROM
  dbo.client
  INNER JOIN dbo.invoice
    ON client.ClientNumber = invoice.ClientNumber
  INNER JOIN dbo.invoicedetail
    ON invoice.ClientNumber = invoicedetail.ClientNumber
WHERE
  client.ClientActive = 'Y'
GROUP BY
  client.ClientNumber
, client.ClientActive
, invoice.ClientNumber
, invoice.InvoiceDate
, invoicedetail.InvoiceNumber
, invoicedetail.ServiceDate
, invoicedetail.Extended
, invoicedetail.ClientNumber
, invoicedetail.ClientNumber
ORDER BY
  InvoiceDate