非常具有挑战性的SQL面试(不能使用存储过程)

Question

您有一个具有两列的SQL表table：name和pen。这两列都是文本字符串。

name  | pen
---------------    
mike  | red
mike  | red
mike  | blue
mike  | green
steve | red
steve | yellow
anton | red
anton | blue
anton | green
anton | black
alex  | black
alex  | green
alex  | yellow
alex  | red

人名作为输入参数给出。

请编写SQL语句(而不是存储过程)，返回具有与给定人的笔集相同或更宽/更大的唯一笔集的人的姓名。

示例：

• 输入: mike
• 产出:安东

迈克有(红色，蓝色，绿色)。

安东有更多的小玩意(红色，蓝色，绿色)+黑色。

• 投入: steve
• 产出: alex

史蒂夫有(红色，黄色)。

亚历克斯有(红色，黄色)+ green+黑色。

迈克，安东没有印-他们没有黄色。

• 投入: alex
• 产出：

Answer 1

这里有一个方法(在线演示)，假设输入名为"steve“。

这可以重新定义为“寻找不存在史蒂夫拥有但他们不拥有的钢笔的所有用户”

SELECT DISTINCT name
FROM   table t1
WHERE  NOT EXISTS (SELECT *
                   FROM   table t2
                   WHERE  name = 'steve'
                          AND NOT EXISTS (SELECT *
                                          FROM   table t3
                                          WHERE  t2.pen = t3.pen
                                                 AND t1.name = t3.name))  
AND t1.name <> 'steve' /*Exclude input name from results*/

有关其他技术，请参阅本文

Answer 2

  with test1 as
   (select a.name nm, count(distinct a.pen) ct
      from table a, table b
     where b.pen = a.pen
       and b.name = 'anton'
     group by a.name
     order by 2 desc),
  test2 as
   (select  nm name1
    from test1
    where ct = (select max(ct) from test1))
  select distinct c.name 
    from table c
   where c.name in (select name1
                      from test2
                      where name1 not in (case
                             when (select count(distinct name1) from test2) > 1 then
                              'anton'
                             else
                              ' '
                           end))

Answer 3

Table Format:

E_NAME  E_PEN
----------------
mike    green 
mike    blue  
mike    red   
mike    red   
steve   red   
steve   yellow
anton   red   
anton   blue  
anton   green 
anton   black 
alex    black 
alex    green 
alex    yellow
alex    red   


Query: 



SELECT A.E_NAME, A.E_PEN
  FROM V_NAME A, V_NAME B
 WHERE TRIM(UPPER(A.E_NAME)) = TRIM(UPPER(B.E_NAME))
   AND TRIM(UPPER(A.E_NAME)) != 'MIKE'
   AND TRIM(UPPER(A.E_PEN)) = TRIM(UPPER(B.E_PEN(+)))

Procedure to take Input from user: 

CREATE OR REPLACE PROCEDURE E_TEST(I_NAME VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE E_TABLE AS
                   SELECT A.E_NAME,A.E_PEN FROM V_NAME A,V_NAME B
                   WHERE TRIM(UPPER(A.E_NAME)) = TRIM(UPPER(B.E_NAME))
                   AND TRIM(UPPER(A.E_NAME)) != ''' ||
                    I_NAME || '''
                   AND TRIM(UPPER(A.E_PEN))= TRIM(UPPER(B.E_PEN(+)))';
END;

Name: Nikhil Shinde
E-Mail: nikhilshinde3jun@gmail.com