SQL技术需求：单查询实现会员book_limit调增及前后限额展示

Sure thing! Let's solve this problem exactly as you specified—with a single query, no views, and all data pulled straight from the Member table.

We need to display each member's ID, their original book_limit, and the adjusted value (increased by 2, capped at a maximum of 10). Here are two reliable, straightforward approaches:

Using LEAST() Function (Clean & Concise) #

Most modern databases (MySQL, PostgreSQL, SQL Server 2022+, etc.) support the LEAST() function, which returns the smallest value from a set of inputs. This is perfect for enforcing our cap requirement:

SELECT
    member_id AS "Member ID",
    book_limit AS "Original Book Limit",
    LEAST(book_limit + 2, 10) AS "Adjusted Book Limit"
FROM
    Member;

How this works:

• book_limit + 2 calculates the initial increased value
• LEAST(...) compares that result with 10, and picks the smaller one. So if the increased limit would exceed 10, it automatically defaults to the maximum cap of 10.

Using CASE Statement (Explicit & Broadly Compatible) #

If you're working with a database that doesn't support LEAST() (or prefer more explicit, readable logic), a CASE statement achieves the same outcome:

SELECT
    member_id AS "Member ID",
    book_limit AS "Original Book Limit",
    CASE
        WHEN book_limit + 2 > 10 THEN 10
        ELSE book_limit + 2
    END AS "Adjusted Book Limit"
FROM
    Member;

Here, we explicitly check if the increased limit would go over 10. If yes, we set it to the cap; otherwise, we use the value after adding 2.

Both queries meet all your requirements: they’re single standalone queries, no views are created, and all fields are pulled directly from the Member table.

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