SQL计数where子句 [英] SQL Count where clause

问题描述

我有以下SQL语句：

  SELECT [l.LeagueId] AS LeagueId，[l.LeagueName] AS NAME，[lp.PositionId] 
 FROM（Leagues l INNER JOIN 
 Lineups lp ON l.LeagueId = lp.LeagueId）
 WHERE（lp.PositionId = 1）OR 
 lp.PositionId = 3）OR 
（lp.PositionId = 2）
  

需要获取的位置的计数大于一个数字的行。例如：

  SELECT [l.LeagueId] AS LeagueId，[l.LeagueName] AS NAME，[lp.PositionId] 
 FROM（Leagues l INNER JOIN 
 Lineups lp ON l.LeagueId = lp.LeagueId）
 WHERE Count（lp.PositionId = 1）> 2 OR 
 Count（lp.PositionId = 3）> 6 OR 
 Count（lp.PositionId = 2）> 3 
  

在SQL中有办法吗？

  SELECT [l.LeagueId] AS LeagueId，[l.LeagueName] AS NAME 
 FROM（Leagues l INNER JOIN 
 Lineups lp ON l.LeagueId = lp.LeagueId）
 GROUP BY [l.LeagueId]，[ LeagueName] 
 HAVING SUM（CASP WHEN lp.PositionId = 1 THEN 1 ELSE 0 END）> 2 OR 
 SUM（CASP WHEN lp.PositionId = 3 THEN 1 ELSE 0 END）> 6 OR 
 SUM（CASP WHEN lp.PositionId = 2 THEN 1 ELSE 0 END）> 3 
  

I have the the following SQL statement:

 SELECT        [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME, [lp.PositionId]
 FROM            (Leagues l INNER JOIN
                     Lineups lp ON l.LeagueId = lp.LeagueId)
 WHERE        (lp.PositionId = 1) OR
                     (lp.PositionId = 3) OR
                     (lp.PositionId = 2)

What I really need is to get the rows where the count of the position is greater than a number. Something like:

 SELECT        [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME, [lp.PositionId]
 FROM            (Leagues l INNER JOIN
                     Lineups lp ON l.LeagueId = lp.LeagueId)
 WHERE        Count(lp.PositionId = 1) > 2 OR
                     Count(lp.PositionId = 3) > 6 OR
                     Count(lp.PositionId = 2) > 3

Is there any way to do this in SQL?

解决方案

How about this?:

SELECT        [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME
 FROM            (Leagues l INNER JOIN
                     Lineups lp ON l.LeagueId = lp.LeagueId)
 GROUP BY [l.LeagueId], [l.LeagueName]
 HAVING        SUM(CASE WHEN lp.PositionId = 1 THEN 1 ELSE 0 END) > 2 OR
                     SUM(CASE WHEN lp.PositionId = 3 THEN 1 ELSE 0 END) > 6 OR
                     SUM(CASE WHEN lp.PositionId = 2 THEN 1 ELSE 0 END) > 3

这篇关于SQL计数where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！