使用OVER()，如果客户有观看角斗士，则1否则0 SQL SERVER

Question

我想我需要一些指导，什么是错误的在我的查询。我正在试着做

Watched_Gladiator=CASE WHEN FilmName IN (CASE WHEN FilmName LIKE '%Gladiator%' THEN 1 END) then  OVER(PARTITION BY Cust_Nr) THEN 1 ELSE 0 END

试过这个了:

Watched_Gladiator=CASE WHEN FilmName IN (CASE WHEN FilmName LIKE '%Gladiator%' THEN Filmnamne END) then  OVER(PARTITION BY Cust_Nr) THEN 1 ELSE 0 END

我目前得到的错误是:

Incorrect syntax near the keyword 'OVER'.

这就是我的数据的样子

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

我想创建一个列(1或0)，显示客户是否观看了《角斗士》，那么1 ELSE 0。我该怎么做呢?

我创建了一个测试列，尝试使用简单的LIKE '%Gladiator%' THEN 1 ELSE 0。这种解决方案的问题是，如果客户已经观看了多次，它将不止一次地显示1(1)。我只需要1或0

我觉得我真的快要找到解决办法了。我刚开始使用OVER()和CASE WHEN，但很享受这种快感:=)

Answer 1

你的意思是:

SELECT Cust_Nr, Date, FilmName, 
       CASE WHEN FilmName LIKE '%Gladiator%' THEN 1 ELSE 0 END as WatchedGladiator
FROM YourTable
WHERE YourColumn = @somevalue

不工作吗?因为根据你提供的数据，它应该。

编辑:

根据Tim的评论，我将简单地将这一点添加到查询中。

   SELECT Cust_Nr, Date, FilmName, WatchedGladiator
   FROM
   (
      SELECT Cust_Nr, Date, FilmName, 
             CASE WHEN FilmName LIKE '%Gladiator%' THEN 1 ELSE 0 END as WatchedGladiator
      FROM YourTable
      WHERE YourColumn = @somevalue
   ) as wg
   WHERE WatchedGladiator = 1

Answer 2

以下是您想要的所有电影:

select r.*,
       (case when row_number() over (partition by filmname order by date) = 1
             then 1 else 0
        end) as IsWatchedFirstAndGladiator
from results r;

For just Gladiator:

select r.*,
       (case when filmname = 'Gladiator' and row_number() over (partition by filmname order by date) = 1
             then 1 else 0
        end) as IsWatchedFirst
from results r;

Answer 3

那么您想按客户分组并在该客户观看特定电影时添加一列吗?

你可以这样做:

SELECT Cust_Nr, MAX(Watched_Gladiator) 
FROM( SELECT Cust_Nr, 
            Watched_Gladiator = CASE WHEN EXISTS
            (
              SELECT 1 FROM CustomerFilm c2
              WHERE c2.Cust_Nr = c1.Cust_Nr 
              AND   c2.FilmName LIKE '%Gladiator%'
            ) THEN 1 ELSE 0 END
     FROM CustomerFilm c1 ) X
GROUP BY Cust_Nr

演示

但是如果您使用customer-table而不是这个表，那么就不需要group-by了。

Answer 4

尝试分组到客户/电影级别:

select
cust_nbr,
case when film_name like '%Gladiator%' then 1 else 0 end
from
(
select
    cust_nbr,
    film_name
    from
    <your table>
    group by
    cust_nbr,
    film_name
) t

或者，作为替代:

select distinct cust_nbr
from
<your table>
where 
filmname = 'Gladiator'