Блог

У меня есть очень простая таблица MS SQL со следующими данными (с именем столбца и типом данных):

 TableId     PersonName              Attribute           AttributeValue
(int)       (nvarchar 50)           (nvarchar 50)       (bit)
----------- ----------------------- ------------------- --------------
1           A                       IsHuman             1
2           A                       CanSpeak            1
3           A                       CanSee              1
4           A                       CanWalk             0
5           B                       IsHuman             1
6           B                       CanSpeak            1
7           B                       CanSee              0
8           B                       CanWalk             0
9           C                       IsHuman             0
10          C                       CanSpeak            1
11          C                       CanSee              1
12          C                       CanWalk             0
  

Теперь в результате мне нужно уникальное имя пользователя, которое имеет как атрибут isHuman, так и CanSpeak с AttributeValue = 1.

Ожидаемый результат должен быть (не должен включать C, поскольку этот имеет isHuman = 0)

 PersonName
------------
A
B
  

Пожалуйста, может ли какой-либо эксперт помочь мне в написании SQL-запроса для этого.

 SELECT PersonName 
  FROM MyTable 
 WHERE AttributeName = 'IsHuman' 
       AND AttributeValue = 1
INTERSECT
SELECT PersonName 
  FROM MyTable 
 WHERE AttributeName = 'CanSpeak' 
       AND AttributeValue = 1;
  

Очевидно, что этот подход не «масштабируется», если критерии могут меняться. Возможно, вам требуется реляционный оператор division, широко известный как «поставщик, который поставляет все детали», в частности division с остатком.

 SELECT PersonName 
FROM MyTable 
WHERE (AttributeName = 'IsHuman' AND AttributeValue = 1) OR 
      (AttributeName = 'CanSpeak' AND AttributeValue = 1) 
GROUP BY PersonName
HAVING COUNT(*) > 1
  

или

 SELECT PersonName 
FROM MyTable 
WHERE AttributeValue = 1 AND AttributeName IN ('IsHuman', 'CanSpeak') 
GROUP BY PersonName
HAVING COUNT(*) > 1
  

 SELECT PersonName FROM MyTable 
WHERE PersonName IN 
(SELECT T1.PersonName FROM MyTable T1 WHERE T1.Attribute = 'IsHuman' and T1.AttributeValue='1')
AND (Attribute = 'CanSpeak' AND AttributeValue='1')
  

Я думаю, что два внутренних соединения могут обеспечить вам хорошую производительность в зависимости от индексации и размеров таблицы.

 SELECT t.PersonName FROM table t 
INNER JOIN table t2 ON t.PersonName=t2.PersonName AND t3.Attribute = 'IsHuman' AND t2.AttributeValue = 1 
INNER JOIN table t3 ON t2.PersonName=t3.PersonName AND t3.Attribute = 'CanSpeak' AND t3.AttributeValue = 1 
  

или

 SELECT t.PersonName FROM table t 
INNER JOIN table t2 ON t.PersonName=t2.PersonName
INNER JOIN table t3 ON  t2.PersonName=t3.PersonName
WHERE t2.Attribute = 'IsHuman' AND t2.AttributeValue = 1 AND t3.Attribute = 'CanSpeak' AND t3.AttributeValue = 1 
  

Однако это решение можно было бы значительно упростить, если бы свойства isHuman и CanSpeak находились в отдельных таблицах со связующей таблицей идентификаторов между ними. Похоже, что эта таблица могла бы выиграть от некоторой нормализации.

Если вы не можете выполнить это, представление может помочь в производительности. Я дома без установленного SQL, поэтому я не могу проверить какие-либо аспекты производительности.

 select personname from yourtablename where personname in ('a','b') group by personname
  

На самом деле я использую это как проверочный вопрос для интервью. Никто из вас, люди, не получил бы работу.

Хорошо, возможно, вы бы так и сделали, но хотя используемые вами стратегии могут работать, а могут и не работать, они не поддаются обобщению и в них отсутствует базовое понятие реляционной алгебры, а именно псевдонимирование.

Правильный ответ (в том смысле, что это повысит вероятность того, что я найму вас, а также уменьшит важность того, что оптимизатор RDMS понимает это, и его можно распространить на другие, произвольно сложные случаи):

 SELECT t1.PersonName 
  FROM MyTable t1, MyTable t2
 WHERE t2.AttributeName = 'CanSpeak' 
       AND t2.AttributeValue = 1
       AND t1.AttributeName = 'IsHuman' 
       AND t1.AttributeValue = 1
       AND t1.PersonName = t2.PersonName;