购买了早于角斗士日期 SQL SERVER 的电影 [英] purchased a movie earlier than Gladiator date SQL SERVER

问题描述

我需要指导和帮助来解决我从未在 SQL SERVER 中做过的问题.

I needed guidance and help with a question I have never done in SQL SERVER.

我有这个数据:

Cust_Nr     Date        FilmName
157649306   20150430    Inside Llewyn Davis 
158470722   20150504    Nick Cave: 20,000 Days On Earth
158467945   20150504    Out Of The Furnace 
158470531   20150504    FilmA
157649306   20150510    Gladiator
158470722   20150515    Gladiator

客户编号:158470722 购买了两部电影，1):Inside Llewyn Davis 和 2) Gladiator 在不同的日期.我在 SQL SERVER 中尝试做的是:

The customer number :158470722 has bought two movies,1):Inside Llewyn Davis and 2) Gladiator on different dates. What I am trying to do in SQL SERVER is to this:

 Cust_Nr    Date        FilmName                  Before Gladiator Purchase
    157649306   20150430    Inside Llewyn Davis              1
    158470722   20150504    Nick Cave: 20,000 Days On Earth  1
    158467945   20150504    Out Of The Furnace               0
    158470531   20150504    FilmA                            0
    157649306   20150510    Gladiator                        0
    158470722   20150515    Gladiator                        0

如果我们查看客户 nr 的日期:157649306，他在日期 20150430 购买了电影，并在 20150510 购买了角斗士.如何创建像上面那样的列显示客户是否在角斗士日期之前购买了电影?

If we look at the dates for the customer nr:157649306, he purchased a movie on a date ,20150430, and purchased Gladiator on the 20150510. How can I create a column like the one above to show if a customer has purchased a movie on a date earlier than the gladiator date?

推荐答案

您可以使用条件聚合和窗口/分析功能来做到这一点:

You can do this with conditional aggregation and window/analytic functionality:

SELECT *,CASE WHEN [Date] < MIN(CASE WHEN FilmName = 'Gladiator' 
                                     THEN [Date] 
                                END) OVER(PARTITION BY Cust_Nr)  
              THEN 1 
              ELSE 0 
         END AS Before_Gladiator
FROM Table1

演示:SQL Fiddle

这篇关于购买了早于角斗士日期 SQL SERVER 的电影的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！