如何使用SQL或CrystalReports輕松地將公制值轉換為英制

我正在使用 Crystal Reports 16 為客戶生成報告，然后在另一家公司的軟體中使用這些報告。我不允許修改資料庫結構，所以我必須在 SQL Server 的查詢中進行所有資料操作。

這些報告中的大部分長度都是公制的，但少數報告需要在第 32 位將該長度轉換為英制的列。

這是我在查詢中所做的以轉換這些長度

CASE
        WHEN a.PCE_LEN_FRACTION <= 16 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'
        WHEN a.PCE_LEN_FRACTION > 16 AND a.PCE_LEN_FRACTION <= 47 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/32'
        WHEN a.PCE_LEN_FRACTION > 47 AND a.PCE_LEN_FRACTION <= 79 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/16'
        WHEN a.PCE_LEN_FRACTION > 79 AND a.PCE_LEN_FRACTION < 110 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 3/32'
        WHEN a.PCE_LEN_FRACTION >= 110 AND a.PCE_LEN_FRACTION <= 141 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/8'
        WHEN a.PCE_LEN_FRACTION > 141 AND a.PCE_LEN_FRACTION < 172 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 5/32'
        WHEN a.PCE_LEN_FRACTION >= 172 AND a.PCE_LEN_FRACTION <= 204 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 3/16'
        WHEN a.PCE_LEN_FRACTION > 204 AND a.PCE_LEN_FRACTION < 235 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 7/32'
        WHEN a.PCE_LEN_FRACTION >= 235 AND a.PCE_LEN_FRACTION <= 266 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/4'
        WHEN a.PCE_LEN_FRACTION > 266 AND a.PCE_LEN_FRACTION < 297 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 9/32'
        WHEN a.PCE_LEN_FRACTION >= 297 AND a.PCE_LEN_FRACTION <= 329 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 5/16'
        WHEN a.PCE_LEN_FRACTION > 329 AND a.PCE_LEN_FRACTION < 360 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 11/32'
        WHEN a.PCE_LEN_FRACTION >= 360 AND a.PCE_LEN_FRACTION <= 391 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 3/8'
        WHEN a.PCE_LEN_FRACTION > 391 AND a.PCE_LEN_FRACTION < 422 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 13/32'
        WHEN a.PCE_LEN_FRACTION >= 422 AND a.PCE_LEN_FRACTION <= 454 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 7/16'
        WHEN a.PCE_LEN_FRACTION > 454 AND a.PCE_LEN_FRACTION < 485 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 15/32'
        WHEN a.PCE_LEN_FRACTION >= 485 AND a.PCE_LEN_FRACTION <= 516 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/2'
        WHEN a.PCE_LEN_FRACTION > 516 AND a.PCE_LEN_FRACTION < 547 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 17/32'
        WHEN a.PCE_LEN_FRACTION >= 547 AND a.PCE_LEN_FRACTION <= 579 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 9/16'
        WHEN a.PCE_LEN_FRACTION > 579 AND a.PCE_LEN_FRACTION < 610 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 19/32'
        WHEN a.PCE_LEN_FRACTION >= 610 AND a.PCE_LEN_FRACTION <= 641 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 5/8'
        WHEN a.PCE_LEN_FRACTION > 641 AND a.PCE_LEN_FRACTION < 672 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 21/32'
        WHEN a.PCE_LEN_FRACTION >= 672 AND a.PCE_LEN_FRACTION <= 704 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 11/16'
        WHEN a.PCE_LEN_FRACTION > 704 AND a.PCE_LEN_FRACTION < 735 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 23/32'
        WHEN a.PCE_LEN_FRACTION >= 735 AND a.PCE_LEN_FRACTION <= 766 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 3/4'
        WHEN a.PCE_LEN_FRACTION > 766 AND a.PCE_LEN_FRACTION < 797 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 25/32'
        WHEN a.PCE_LEN_FRACTION >= 797 AND a.PCE_LEN_FRACTION <= 829 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 13/16'
        WHEN a.PCE_LEN_FRACTION > 829 AND a.PCE_LEN_FRACTION < 860 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 27/32'
        WHEN a.PCE_LEN_FRACTION >= 860 AND a.PCE_LEN_FRACTION <= 891 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 7/8'
        WHEN a.PCE_LEN_FRACTION > 891 AND a.PCE_LEN_FRACTION < 922 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 29/32'
        WHEN a.PCE_LEN_FRACTION >= 922 AND a.PCE_LEN_FRACTION <= 954 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 15/16'
        WHEN a.PCE_LEN_FRACTION > 954 AND a.PCE_LEN_FRACTION < 985 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 31/32'
        WHEN a.PCE_LEN_FRACTION >= 985 AND a.PCE_LEN_INCHES < 11 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES   1 as varchar)   '"'
        WHEN a.PCE_LEN_FRACTION >= 985 AND a.PCE_LEN_INCHES = 11 THEN
            CAST(a.PCE_LEN_FEETS  1 as varchar)   ''' 0"'
        ELSE ''
    END

它適用于我從查詢中獲得的資料量，但我在特定查詢中使用了 4 次相同的東西。

在 Crystal 報表中，我找不到任何可以將公制直接轉換為英制的內置函式，這可能會提高性能，而不必在我的查詢中做任何事情。

有誰知道使用 SQL 或 Crystal Reports 將指標轉換為英制的更有效方法？

uj5u.com熱心網友回復：

如果您首先四舍五入到最接近的 1/32 英寸整數倍，您可以測驗可被 32、16、8、4 和 2 整除的分子，以將您的案例陳述減少到一小部分。然后，每個案例都會計算并將您的分數格式化為整數、一半、四分之一等。

讓你的分數已經從整體中分離出來并表示為 1/1000 會使事情變得有點復雜，尤其是在處理 32/32 向上取整的情況時。

下面是一個示例，它演示了具有一系列生成資料的更緊湊的分數格式化程式的兩個版本。

DECLARE @Data TABLE (
    Value Decimal(10,3),
    Inch INT,
    Thousands INT,
    ThirtySeconds INT,
    Carry INT,
    InchFraction VARCHAR(100),
    TotalThirtySeconds INT,
    InchFraction2 VARCHAR(100)
    )

-- Generate data from 0.950 thru 2.050
INSERT @Data (Value)
SELECT TOP 1101 (ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1) / 1000.000   0.950
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) D1(D)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) D2(D)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) D3(D)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) D4(D)

UPDATE @Data SET Inch = FLOOR(Value), Thousands = Value * 1000 % 1000

-- Calcuating from integer thousands to 32nds to fraction
UPDATE @Data SET ThirtySeconds = ROUND(Thousands * 32.0 / 1000, 0)
UPDATE @Data SET Carry = CASE WHEN ThirtySeconds = 32 THEN 1 ELSE 0 END
UPDATE @Data SET InchFraction = CONVERT(VARCHAR(10), Inch   Carry)
      CASE
      WHEN ThirtySeconds %32 = 0 THEN ''
      WHEN ThirtySeconds %16 = 0 THEN ' '   CONVERT(VARCHAR(10), ThirtySeconds / 16)   '/2'
      WHEN ThirtySeconds %8 = 0 THEN ' '   CONVERT(VARCHAR(10), ThirtySeconds / 8)   '/4'
      WHEN ThirtySeconds %4 = 0 THEN ' '   CONVERT(VARCHAR(10), ThirtySeconds / 4)   '/8'
      WHEN ThirtySeconds %2 = 0 THEN ' '   CONVERT(VARCHAR(10), ThirtySeconds / 2)   '/16'
      ELSE ' '   CONVERT(VARCHAR(10), ThirtySeconds)   '/32'
      END

-- Calculation direct from Value
UPDATE @Data SET TotalThirtySeconds = Round(Value * 32, 0)
UPDATE @Data SET InchFraction2 = CONVERT(VARCHAR(10), TotalThirtySeconds / 32)
      CASE
      WHEN TotalThirtySeconds %32 = 0 THEN ''
      WHEN TotalThirtySeconds %16 = 0 THEN ' '   CONVERT(VARCHAR(10), TotalThirtySeconds % 32 / 16)   '/2'
      WHEN TotalThirtySeconds %8 = 0 THEN ' '   CONVERT(VARCHAR(10), TotalThirtySeconds % 32 / 8)   '/4'
      WHEN TotalThirtySeconds %4 = 0 THEN ' '   CONVERT(VARCHAR(10), TotalThirtySeconds % 32 / 4)   '/8'
      WHEN TotalThirtySeconds %2 = 0 THEN ' '   CONVERT(VARCHAR(10), TotalThirtySeconds % 32 / 2)   '/16'
      ELSE ' '   CONVERT(VARCHAR(10), TotalThirtySeconds  % 32)   '/32'
      END

-- Show summary
SELECT FromValue = MIN(Value), ThruValue = MAX(Value), InchFraction, InchFraction2
FROM @Data
GROUP BY InchFraction, InchFraction2
ORDER BY MIN(Value)

-- Show details including intermediate values
SELECT *
FROM @Data
ORDER BY Value

部分結果：

有關演示和完整結果，請參閱

標籤：sql sql服务器 水晶报告 转换器

上一篇：根據時間間隔聚合我的SQL結果？

下一篇：如何將具有重復主鍵記錄的平面檔案匯入SQL表