특정 카디널리티를 가진 sql 쿼리(다대다)

특정 카디널리티를 가진 sql 쿼리(다대다)

마리아답에 테이블이 세 개 있어요

sensor
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| name          | varchar(64) | NO   | PRI | NULL    |       |
| reload_time   | int(11)     | NO   |     | NULL    |       |
| discriminator | varchar(20) | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

sensor_common_service
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| service_name  | varchar(64) | NO   | PRI | NULL    |       |
| sensor_name   | varchar(64) | NO   | PRI | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

common_service
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| service_name  | varchar(64) | NO   | PRI | NULL    |       |
| version       | int(11)     | NO   |     | NULL    |       |
| reload_time   | int(11)     | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

센서 세트를 가진 common_services를 모두 취득합니다.예를 들어 센서 온도와 습도를 가진 common_services를 모두 취득합니다.

그래서 만약에 제가

common_service1 : sensors [temperature]
common_service2 : sensors [temperature,humidity]
common_service3 : sensors [temperature,humidity, luminosity]

쿼리는 common_service2만 반환해야 합니다.

첫 번째 시도에서는 특정 카디널리티를 가진 매핑(접속) 테이블 간의 Join 쿼리를 조정하려고 했습니다.

그리고 이게 그 결과야

SELECT * FROM custom_service
JOIN (
      SELECT scm.service_name FROM sensor_custom_service scm
      WHERE scm.sensor_name IN (
                      SELECT s.name FROM sensor s
                      WHERE s.name='luminosity' OR s.name='temperature'
                      )
      GROUP BY scm.service_name HAVING COUNT(DISTINCT scm.sensor_name)=2
      ) AS jt
ON custom_service.service_name=jt.service_name;

또 하나.

SELECT scs.* FROM sensor_custom_service scs
where scs.sensor_name IN ( 'luminosity', 'temperature' )
GROUP BY scs.service_name;
HAVING COUNT(scs.sensor_name) = 2

그러나 이 쿼리에서는 다른 센서를 가진 common_services도 얻을 수 있습니다.이는 보유 카운트가 where 구를 충족하는 sensor_custom_service만 카운트하기 때문입니다.

위의 예를 사용하여 이 쿼리는 둘 다 반환됩니다.

common_service2 : sensors [temperature,humidity]
common_service3 : sensors [temperature,humidity]

이 쿼리는 이와 같이 크로스 연산자를 사용하는 것이 쉬울 것 같습니다.

SELECT scs.* FROM sensor_custom_service scs
where scs.sensor_name IN ( 'luminosity', 'temperature' )
INTERSECT
SELECT scs.* FROM sensor_custom_service scs
HAVING COUNT(scs.sensor_name) = 2

하지만 마리아브는 돌아온다.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTERSECT

지원되지 않기 때문에 (두 쿼리는 개별적으로 동작하기 때문에)

---

9000 쿼리를 사용한 솔루션.

select * 
from custom_service cs 
where 
  exists (select 1 from sensor_custom_service scs where 
      scs.service_name = cs.service_name and
      scs.sensor_name = 'luminosity') 
  AND
  exists (select 1 from sensor_custom_service scs where 
      scs.service_name = cs.service_name and
      scs.sensor_name = 'temperature')
  AND
  NOT exists (select  1  from sensor_custom_service scs where
      scs.service_name = cs.service_name and
      scs.sensor_name NOT IN ('temperature', 'luminosity'));

MariaDB 및 MySQL에는 다음 작업을 수행하기 위한 GROUP_CONCAT() 함수가 있습니다.

SELECT service_name,
       GROUP_CONCAT(sensor_name ORDER BY sensor_name) AS sensors
FROM sensor_common_service
GROUP BY service_name
HAVING sensors='humidity,temperature'

http://sqlfiddle.com/ #!9/2f574/1

주의:IN를 제공하다OR시멘틱스(semantics)AND의미론기본적으로 3개의 센서가 모두 필요한 경우 각각 개별 센서를 나타내는 3개의 조인(join)이 필요합니다.뭔가...

select * 
from common_service cs 
where 
  exists (select 1 from sensor_common_service scs where 
          scs.service_name = cs.service_name and
          scs.sensor_name = 'luminosity') 
  AND
  exists (select 1 from sensor_common_service scs where 
          scs.service_name = cs.service_name and
          scs.sensor_name = 'temperature')
  -- add more sensors along these lines

이는 또한 센서 수에 관계없이 작동하는 가변 쿼리를 작성하는 것이 불가능하다는 것을 의미합니다. (잘못되었음을 증명하고 싶습니다!)

서비스당 '온도'와 '습도'가 표시되는 횟수와 다른 항목이 표시되는 횟수를 세기만 하면 됩니다.결과는 첫 번째 숫자 = 2(둘 다 표시됨)와 두 번째 숫자 = 0(다른 아무것도 표시되지 않음)이 됩니다.

select  ss.Service_Name
from    Sensor_Service ss
group by ss.Service_Name
having  Sum( case when ss.Sensor_Name in( 'Temperature', 'Humidity' ) then 1 else 0 end ) = 2
    and Sum( case when ss.Sensor_Name in( 'Temperature', 'Humidity' ) then 0 else 1 end ) = 0;

여기에는 일반 SQL만 사용됩니다.Oracle과 MariaDB에서 테스트했습니다.다른 대부분의 경우 그대로 실행됩니다.

언급URL : https://stackoverflow.com/questions/29592096/sql-query-with-an-specific-cardinality-many-to-many