根据列的值向临时表添加新列

Question

我想从下表创建一个临时表。

------------------------|--------
Date                    | Length
------------------------|--------
2014-08-28 00:00:00.000 | 1.5 
2014-08-28 00:00:00.000 | 2.6
2014-08-28 00:00:00.000 | 1.5
2014-08-28 00:00:00.000 | 3.3
2014-08-28 00:00:00.000 | 1.1
2014-08-28 00:00:00.000 | 8.5
2014-08-28 00:00:00.000 | 8.6
2014-08-28 00:00:00.000 | 11.3

让临时表看起来像下面的那个。

Date                    | Length  | Length_Range
------------------------|---------|--------------
2014-08-28 00:00:00.000 |  1.5    |   1-4
2014-08-28 00:00:00.000 |  2.6    |   1-4
2014-08-28 00:00:00.000 |  6.5    |   5-10
2014-08-28 00:00:00.000 |  3.3    |   1-4
2014-08-28 00:00:00.000 |  1.1    |   1-4
2014-08-28 00:00:00.000 |  8.5    |   5-10
2014-08-28 00:00:00.000 |  8.6    |   5-10
2014-08-28 00:00:00.000 |  11.3   |   11-15

我希望能够定义Length_Range。

Microsoft SQL Server 2016兼容性级别: Server 2005 (90)

Answer 1

使用case

select t.*,
       (case when length >= 1 and length < 4 then '1-4'
             when length < 10 then '5-10'
             when length < 15 then '11-15'
             else '16+'
        end) as length_range
into #temp_t
from t;

Answer 2

CREATE TABLE #TABLE1
    ([DATE] DATETIME, [LENGTH] FLOAT)
INSERT INTO #TABLE1
    ([DATE], [LENGTH])
VALUES
    ('2014-08-28 00:00:00', 1.5),
    ('2014-08-28 00:00:00', 2.6),
    ('2014-08-28 00:00:00', 1.5),
    ('2014-08-28 00:00:00', 3.3),
    ('2014-08-28 00:00:00', 1.1),
    ('2014-08-28 00:00:00', 8.5),
    ('2014-08-28 00:00:00', 8.6),
    ('2014-08-28 00:00:00', 1.3)

SELECT *,CASE  
WHEN LENGTH BETWEEN 1 AND 4 THEN '1-4'
WHEN LENGTH BETWEEN 5 AND 10 THEN '5-10'
WHEN LENGTH BETWEEN 11 AND 15 THEN '11-15' END AS LENGHT_RANGE
FROM #TABLE1

输出

Date                    Length  LENGHT_RANGE
2014-08-28 00:00:00.000  1.5          1-4
2014-08-28 00:00:00.000  2.6          1-4
2014-08-28 00:00:00.000  1.5          1-4
2014-08-28 00:00:00.000  3.3          1-4
2014-08-28 00:00:00.000  1.1          1-4
2014-08-28 00:00:00.000  8.5          5-10
2014-08-28 00:00:00.000  8.6          5-10
2014-08-28 00:00:00.000  1.3          1-4

Answer 3

你可以使用创建表..。作为选择..。语法

CREATE TABLE temp_table AS
SELECT [date], [length], 
       CASE  
         WHEN length BETWEEN 1 AND 4 THEN '1-4'
         WHEN length BETWEEN 5 AND 10 THEN '5-10'
         WHEN length BETWEEN 11 AND 15 THEN '11-15' 
       END AS LENGTH_RANGE
 FROM orig_table

资料来源：

Net -SQL上的技术:创建表作为语句

MSDN -以选择的形式创建表

Oracle -创建表

..。