SQL Server：In with和condition [英] SQL Server: In with and condition

问题描述

我有简单的查询，我使用IN关键字我想只显示包含所有项目的行。



示例：我有两个表1）视频2）类别



选择Video.videoid，title，categoryid来自Video.portalid上的视频连接类别= Category.portalid其中Category.CategoryId in（1,2,3） ）



此查询返回所有类别为1或2或3等的记录，但我只想显示具有所有这三个categoryId值的行。 br />


我也在使用group by但我觉得我错过了什么..



select Video.videoid ，标题，类别来自视频连接类别视频.portalid = Category.portalid其中Category.CategoryId in（1,2,3）

group by Video.VideoId，title，categoryid

有计数（不同的categoryid）= 3



我尝试了其他方式，但我还没有成功。

解决方案

我不确定这是easi但它是第一个浮现在脑海中的东西。加入3次到分类表，以确保它在那里三次。例如：

  SELECT  * 
  FROM 视频v 
  JOIN 类别c1  ON  v.portalid = c1.portalid < span class =code-keyword> AND  c1.CategoryID =  1  
  JOIN 类别c2  ON  v.portalid = c2.portalid  AND  c2.CategoryID =  2  
  JOIN 类别c3  ON  v.portalid = c3.portalid  AND  c3.CategoryID =  3  

根据您提取的数据，这可能会导致重复记录太多，因此您可能还需要在您的SELECT中添加DISTINCT。

您需要使用HAVING

  SELECT  V.VideoID，COUNT（CategoryID） as  CNT 
  FROM 视频v 
  WHERE  v.CategoryID  IN （ 1 ， 2 ， 3  ）
  GROUP   BY  V.VIdeoID 
  HAVING  COUNT（CategoryID）=  3    -    COUNT。 （1,2,3） 

在你的例子中（你应该删除categoryid）这就是为什么它会给你错误的答案：

  select  Video.videoid，title 
 来自视频 join 类别 on  Video.portalid = Category.portalid 其中​​ Category.CategoryId  （  1 ， 2 ， 3 ）
  group   by  Video.VideoId，title 
  计数（ distinct  categoryid）=  3  

I have simple query, i am using IN keyword I want to show only rows that have all the item.

Example : i have two table 1)Video 2) Category

select Video.videoid, title, categoryid from Video join Category on Video.portalid=Category.portalid where Category.CategoryId in (1,2,3)

This query return all the record that have category id 1 or 2 or 3 etc. but I want to show only rows that have all those three categoryId values.

I am also using group by but i think i am missing something..

select Video.videoid, title, categoryid from Video join Category on Video.portalid=Category.portalid where Category.CategoryId in (1,2,3)
group by Video.VideoId,title,categoryid
having count(distinct categoryid ) = 3

I tried some other way but I did not succeed yet.

解决方案

I'm not sure this is the easiest but it's the first that comes to mind. Join 3 times to Category table to make sure it is in there all three times. For example:

SELECT *
FROM Video v
JOIN Category c1 ON v.portalid = c1.portalid AND c1.CategoryID = 1
JOIN Category c2 ON v.portalid = c2.portalid AND c2.CategoryID = 2
JOIN Category c3 ON v.portalid = c3.portalid AND c3.CategoryID = 3

Depending on what data you are pulling this may cause too many duplicate record so you may also need to put DISTINCT in your SELECT.

You need to use HAVING

SELECT V.VideoID, COUNT(CategoryID) as CNT
FROM Video v
WHERE v.CategoryID IN (1,2,3)
GROUP BY V.VIdeoID
HAVING COUNT(CategoryID) = 3 --COUNT from list of necessary categories. (1,2,3)

In your example (you should remove categoryid from the group by. That's why it is give you wrong answer:

select Video.videoid, title 
from Video join Category on Video.portalid=Category.portalid where Category.CategoryId in (1,2,3)
group by Video.VideoId,title
having count(distinct categoryid ) = 3

这篇关于SQL Server：In with和condition的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！