SQL中如何规避小数而非对数值取整？除取整外还有其他转整数方法吗？

Hey there! Let’s tackle your question step by step—first, how to work with numeric data without relying on rounding or truncating to avoid decimals, then other methods to convert values to pure integers beyond those basic rounding/truncation functions.

Avoiding Decimals Without Rounding/Truncating #

These approaches focus on preventing decimals from appearing in the first place, rather than fixing them after the fact:

• Use integer-based data types from the start
  If your use case doesn’t require decimal precision (e.g., counting items, tracking whole units), define your columns with integer types like INT, BIGINT, or SMALLINT instead of DECIMAL, FLOAT, or DOUBLE. This eliminates decimals entirely at the storage level.
  Example:

  CREATE TABLE inventory (
      item_id INT PRIMARY KEY,
      stock_quantity INT NOT NULL -- No decimals possible here
  );
  

• Convert values to integer units
  For data that inherently has decimals (like currency), scale the value to an integer unit before storing. For example, store dollars as cents: $19.99 becomes 1999 cents. When you need to display the original value, just divide by the scaling factor.
  Example:

  -- Inserting a $25.50 transaction as cents
  INSERT INTO customer_transactions (transaction_id, amount_cents)
  VALUES (101, 2550);
  
  -- Retrieving the value as dollars
  SELECT transaction_id, amount_cents / 100.0 AS amount_dollars
  FROM customer_transactions;
  

• Ensure integer-only operations
  When performing calculations, use integer operands to avoid decimal results. Most SQL dialects return integer results when dividing two integers (check your DB’s specific rules—e.g., PostgreSQL, MySQL, and SQL Server all do this for positive numbers).
  Example:

  -- 10 divided by 3 returns 3 (integer) instead of 3.333
  SELECT 10 / 3 AS integer_division_result;
  

Alternative Ways to Convert to Pure Integers (Beyond Rounding/Truncating) #

If you already have decimal values and need to turn them into integers without using ROUND(), TRUNC(), CEIL(), or FLOOR(), try these methods:

• String manipulation + casting
  Convert the numeric value to a string, extract everything before the decimal point, then cast it back to an integer. This works for both positive and negative numbers.
  Example (MySQL):

  SELECT CAST(SUBSTRING_INDEX(-12.78, '.', 1) AS INT) AS integer_part;
  -- Returns -12
  

  Example (SQL Server):

  SELECT CAST(LEFT(-12.78, CHARINDEX('.', -12.78) - 1) AS INT) AS integer_part;
  

• Scale and cast (for known decimal precision)
  If your values have a fixed number of decimal places, multiply by the appropriate factor to eliminate decimals, then cast to an integer. This is different from rounding because it’s a direct scaling (as long as you know the exact precision).
  Example:

  -- For values with 2 decimal places (e.g., currency)
  SELECT CAST(12.34 * 100 AS INT) AS scaled_integer;
  -- Returns 1234
  

• Use bitwise operations (for positive numbers)
  Some SQL dialects allow bitwise operations on numeric values, which implicitly convert decimals to integers by truncating the fractional part. Note: This only works for positive numbers and may not be supported in all databases.
  Example (MySQL):

  SELECT 12.78 & ~0 AS integer_result;
  -- Returns 12
  

内容的提问来源于stack exchange，提问作者ssbsuresh