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

我如何使用Group By(而不是自连接)来获取正确的数据(包括具体示例)

group-by sql-server-2000 tsql sql-server

IronicMuffin · 技术社区 · 16 年前

看看相关的问题,我认为这个具体的问题还没有被问到,所以就这样吧。

http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/06/12/60230.aspx

SELECT
 upc,
 MAX(CASE WHEN ip_start_date <= GETDATE() THEN ip_unit_price END) AS retail_amount,
 MAX(CASE WHEN ip_start_date <= GETDATE() THEN ip_price_multiple END) AS retail_multiplier_num,    
 MAX(CASE WHEN ip_start_date BETWEEN GETDATE() AND DATEADD(ww,1,GETDATE()) THEN ip_unit_price END) AS retail_amt_nxt_wk,
 MAX(CASE WHEN ip_start_date BETWEEN GETDATE() AND DATEADD(ww,1,GETDATE()) THEN ip_price_multiple END) AS retail_multipler_num_nxt_wk,
 MAX(CASE WHEN ip_start_date BETWEEN DATEADD(ww,1,GETDATE()) AND DATEADD(ww,2,GETDATE()) THEN ip_unit_price END) AS retail_amt_wk_after_nxt,
 MAX(CASE WHEN ip_start_date BETWEEN DATEADD(ww,1,GETDATE()) AND DATEADD(ww,2,GETDATE()) THEN ip_price_multiple END) AS retail_multiplier_num_wk_after_nxt
FROM 
 items AS im WITH (NOLOCK)
  retails AS ip WITH (NOLOCK)
   ON im.ID = ip.ID
GROUP BY
 upc

(请随意编辑标题。我想不出一个简洁的方法来问这个问题)

1 回复 | 直到 16 年前

Quassnoi 16 年前

在 SQL Server 2005+ :

SELECT  upc, retail_today.*, retail_next_week.*, retail_two_weeks.*
FROM    items im
OUTER APPLY
        (
        SELECT  TOP 1
                ip_unit_price, ip_price_multiple
        FROM    retail ip
        WHERE   ip.ip_start_date <= GETDATE()
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) retail_today
OUTER APPLY
        (
        SELECT  TOP 1
                ip_unit_price, ip_price_multiple
        FROM    retail ip
        WHERE   ip.ip_start_date BETWEEN GETDATE() AND DATEADD(ww, 1, GETDATE())
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) retail_next_week
OUTER APPLY
        (
        SELECT  TOP 1
                ip_unit_price, ip_price_multiple
        FROM    retail ip
        WHERE   ip.ip_start_date BETWEEN DATEADD(ww, 1, GETDATE()) AND DATEADD(ww, 2, GETDATE())
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) retail_two_weeks

在 SQL Server 2000 :

SELECT  upc,
        (
        SELECT  TOP 1
                ip_price
        FROM    retail ip
        WHERE   ip.ip_start_date <= GETDATE()
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) AS ip_price_today,
        (
        SELECT  TOP 1
                ip_price_multiple
        FROM    retail ip
        WHERE   ip.ip_start_date <= GETDATE()
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) AS ip_price_multiple_today,
        â¦
FROM    items im