代码之家  ›  专栏  ›  技术社区  ›  nelsonwebs

反转架构时获取存储过程中的列数据

  •  0
  • nelsonwebs  · 技术社区  · 16 年前

    在SQL2008DB中,我需要使用两个数据表在存储过程中构建结果。表1看起来像这样。

    date         name  hour   amount   price 
    ------------------------------------------
    2009-10-12   tom   12     20       15.43 
    2009-10-13   fred  16     -10      6.98
    

    date         name   H12     H16
    --------------------------------
    2009-10-12   tom    15.75   0
    2009-10-13   fred   0       12.54
    

    我需要编写一个存储过程,其结果如下所示。

    date         name   hour   amount   price   result     actualsold 
    ---------------------------------------------------------------------
    2009-10-12   tom    12     20       15.43   positive   15.75  
    2009-10-13   fred   16    -10       6.98    negative   12.54
    

    我可以得到我需要的大部分结果。是的,简单的部分。

    SELECT date, 
           name, 
           hour, 
           amount, 
           price, 
           case 
             when amount > 0 then 'positive' 
             when amount < 0 then 'negative' 
           end as result
    FROM Table1
    

    如何从表2中获取值并将其添加到actualsold列(最好不使用光标)?

    我无法控制表架构。数据库正在SQL 2008 ENT上运行。

    4 回复  |  直到 16 年前
        1
  •  2
  •   momo    16 年前

    有时交叉连接优于UNPIVOT,有时则相反。下面是交叉连接方法,它甚至可以在SQL 2000中工作:

    SELECT
       A.date, 
       A.name, 
       A.hour, 
       A.amount, 
       A.price,
       result = 
          CASE
             WHEN A.amount > 0 THEN 'positive' 
             WHEN A.amount < 0 THEN 'negative'
          END,
       actualsold =
          CASE A.hour
             WHEN 12 THEN B.H12
             WHEN 16 THEN B.H16
          END
    FROM
       Table1 A
       INNER JOIN Table2 B ON A.date = B.date and A.name = B.name
    

    这是一个隐式的部分交叉联接,因为表1每个日期有许多行&名称,但表2每个日期只有一行&名称因此,表2中的每一行在联接中都会重复多次,但我们使用的列与每一行不同。

    取消PIVOT类似,将取消PIVOT查询放在派生表或CTE中:

    SELECT
       A.date,
       A.name,
       A.hour,
       amount,
       A.price,
       result =
          CASE
          WHEN A.amount > 0 THEN 'positive'
          WHEN A.amount < 0 THEN 'negative'
          END,
       B.actualsold
    FROM
       Table1 A
       INNER JOIN (
          SELECT *
          FROM (SELECT date, name, [12] = H12, [16] = H16 FROM Table2) X
             UNPIVOT (actualsold FOR hour IN ([12], [16])) U
       ) B ON A.date = B.date and A.name = B.name AND A.hour = B.hour
    

    上面两个查询的执行计划非常相似,尽管交叉连接方法稍微简单一些,但这并不能证明实际性能,除非在包含许多行的完整表中对实际数据进行了测试。UNPIVOT执行一个额外的左循环联接,而交叉联接方法没有。

    可以 要足够聪明不要这样做,我不知道)。虽然big 24 condition CASE语句很笨拙,但UNPIVOT在将列名转换为数字时也有自己的尴尬之处。

    以下是任何希望看到这些查询运行的人的设置代码:

    USE tempdb
    CREATE TABLE Table1 (
        date smalldatetime,
        name varchar(10),
        hour int,
        amount int,
        price decimal(15,2)
    )
    
    CREATE TABLE Table2 (
        date smalldatetime,
        name varchar(10),
        H12 decimal(15,2),
        H16 decimal(15,2),
    )
    
    INSERT Table1 VALUES ('20091012', 'tom', 12, 20, 15.43)
    INSERT Table1 VALUES ('20091013', 'fred', 16, -10, 6.98)
    INSERT Table2 VALUES ('20091012', 'tom', 15.75, 0)
    INSERT Table2 VALUES ('20091013', 'fred', 0, 12.54)
    
        2
  •  0
  •   Heinzi    16 年前

    未经测试:

    SELECT A.date, 
           A.name, 
           A.hour, 
           A.amount, 
           A.price, 
           case 
             when A.amount > 0 then 'positive' 
             when A.amount < 0 then 'negative' 
           end as result,
           (SELECT case A.hour when 1 then B.H1
                               when 2 then B.H2
                               ...
                               when 24 then B.H24 end
              FROM Table2 B
             WHERE A.date = B.date AND A.name = B.name) as actualsold
    FROM Table1 A
    

    这是假设所有小时都有列(H1,…,H24)。如果只有H12和H16,则可以减少 case 列表

        3
  •  0
  •   Matt Whitfield    16 年前

    使用UNPIVOT运算符将表2恢复为标准化形式(未测试)

    SELECT date,
           name,
           hour,
           Amount
    FROM   Table2
    UNPIVOT (Amount FOR [hour] IN ([H1], [H2], [H3], [H4], ... etc ...)) AS unpvt;
    
        4
  •  -1
  •   Daniel Pratt    16 年前

    您说过您可以更改表模式,但是您可以创建视图吗?如果可能,我将创建一个视图来纠正第二个表的结构,例如:

    CREATE VIEW vw_Table2 AS
    SELECT date, name, 12 as hour, H12 as sold
    UNION ALL
    SELECT date, name, 16 as hour, H16 as sold
    

    SELECT t1.date, 
        t1.name, 
        t1.hour, 
        t1.amount, 
        t1.price, 
        case 
         when t1.amount > 0 then 'positive' 
         when t1.amount < 0 then 'negative' 
        end as result,
        v2.sold as actualsold
    FROM Table1 as t1
    JOIN vw_Table2 as v2 ON
        v2.date = t1.date
        AND v2.name = t1.name
        AND v2.hour = t1.hour
    
    推荐文章