您能解决这个简单的SQL查询吗? [英] Can you solve this simple SQL query?

问题描述

假设这是一个销售相机的网站.这是我的实体(表):

Suppose it's a website that sells photo cameras. Here are my entities (tables):

Camera: A simple camera
Feature: A feature like: 6mp, max resolution 1024x768, 

问题在于相机和功能之间，我之间存在多对多关系，所以我有一张额外的桌子:

The thing is between cameras and feature i've got a Many to Many relationship, so i have an extra table:

camera -> cameras_features -> feature

因此，查询很简单:

如何获取所有具有1,2和3功能的相机?

这就像构造位图索引.

可用于测试解决方案是否正确的数据

C1 has features 1,2,3
C2 has features 1,2,4
C3 has features 1,2

以下是查询和预期结果:

Here are querys and the expected result:

• 显示所有具有1,2和3功能的摄像机. C1
• 显示所有具有1,2和4功能的摄像机: C2
• 显示所有具有功能1和2的相机 C1 ， C2 和 C3

• Show all the cameras which have feature 1,2 and 3: C1
• Show all the cameras which have feature 1,2 and 4: C2
• Show all the cameras which have feature 1 and 2: C1, C2 and C3

这就是我所做的(它可以工作，但是确实很丑陋，不想使用它):

Here is what i did (it works, but it's really ugly, don't want to use it):

SELECT * FROM camera c

WHERE c.id IN (    
    (SELECT c.id FROM camera c JOIN cameras_features f ON (c.id=f.camera_id)
    WHERE f.feature_id=1)
        q1 JOIN -- simple intersect
    (SELECT c.id FROM camera c JOIN cameras_features f ON (c.id=f.camera_id)
    WHERE f.feature_id=2)
        q2 JOIN ON (q1.id=q2.id)
)

推荐答案

SELECT DISTINCT Camera.*
FROM Camera c
     INNER JOIN cameras_features fc1 ON c.id = fc1.camera_id AND fc1.feature_id = 1
     INNER JOIN cameras_features fc2 ON c.id = fc2.camera_id AND fc2.feature_id = 2

这里发生的是将摄像机过滤为功能1的摄像机，然后在该组中将摄像机过滤为功能2的摄像机.

What is happening here is that cameras will be filtered down to cameras with feature 1, then within this group, the cameras are gonna be filtered down to the ones with feature 2

这篇关于您能解决这个简单的SQL查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！