Question[SQL]：Can you use a SQL statement to calculating it!

最新推荐文章于 2020-03-15 22:20:39 发布

转载最新推荐文章于 2020-03-15 22:20:39 发布 · 65 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/chenjunbiao/archive/2008/10/13/1760190.html

本文介绍了一个SQL案例，演示如何根据书籍的价格将结果显示为不同的类别。“10到20”用于价格在10到20美元之间的书籍，“unknown”用于价格未知的书籍，而“other”则用于所有其他价格。

Question：Can you use a SQL statement to calculating it!

How can I print "10 to 20" for books that sell for between $10 and $20，"unknown" for books whose price is null, and "other" for all other prices?

Answer：

    if OBJECT_ID('dbo.Table101301') is not null
        drop table dbo.Table101301
    create table dbo.Table101301
    (
        [bookid] int not null,
        [bookname] varchar(10),
        [price] varchar(10)
    )
    insert into dbo.Table101301 values(1,'book1', 1)
    insert into dbo.Table101301 values(2,'book2', 5)
    insert into dbo.Table101301 values(3,'book3', 8)
    insert into dbo.Table101301 values(4,'book4', 10)
    insert into dbo.Table101301 values(5,'book5', 12)
    insert into dbo.Table101301 values(6,'book6', null)
    insert into dbo.Table101301 values(7,'book7', 20)
    insert into dbo.Table101301 values(8,'book8', 21)
    insert into dbo.Table101301 values(9,'book9', null)

    select [bookid], [bookname],
        [price] = (
            case
                when [price]  is null then 'unknown'
                when [price] between 10 and 20 then '10 to 20'
                else [price]
            end
        )
        from dbo.Table101301

    drop table dbo.Table101301

转载于:https://www.cnblogs.com/chenjunbiao/archive/2008/10/13/1760190.html