SQL Server数据分析面试题(202008)

阳光穿透心脏的1/2处 2021-06-26 16:06 428阅读 0赞

这是某公司的一份SQL Server数据分析面试题，总共有4道题，此处挑选了其中的3道，另外1道比较简单，就不列出来了。

## 第一题： ##

题目：写一个SQL函数，能将一串字符串按指定的分隔符拆分成行。比如给定字符串"**查询,入库,出库,移库,盘点,设置**"，查询出来的结果是：  
![在这里插入图片描述][20210417172941335.png]  
审题：这是一道典型的`Split`函数题。看过一些写法，可以一段一段地截取，可以使用反转等等，但是核心的知识点就是`Index`。

做题：下面是我的写法，仅供参考。

CREATE FUNCTION [dbo].[Split](@Text NVARCHAR(4000),@SplitSymbol NVARCHAR(4000))  
        RETURNS @ResultTable TABLE ([VALUE] NVARCHAR(4000))  
    AS  
    BEGIN
        --变量定义
        DECLARE @StartIndex INT         --开始位置
        DECLARE @FindIndex  INT         --找到位置
        DECLARE @Content NVARCHAR(4000)    --找到内容
        
        --变量初始化
        SET @StartIndex=1                --T-SQL查找位置是从1开始的
        SET @FindIndex=0  
        
        --循环查找字符串分割符
        WHILE (@StartIndex<=LEN(@Text))
        BEGIN
            --返回查找位置
            SELECT @FindIndex=CHARINDEX(@SplitSymbol,@Text,@StartIndex)
    
            --查找位置返回0表示已查找完毕
            IF (ISNULL(@FindIndex,0)=0)
            BEGIN
                SET @FindIndex=LEN(@Text)+1
            END
             
            --截取字符串
            SET @Content=LTRIM(RTRIM(SUBSTRING(@Text,@StartIndex,@FindIndex-@StartIndex)))
    
            --初始化下次开始位置
            SET @StartIndex=@FindIndex+1
    
            --找到值插入结果表
            INSERT INTO @ResultTable ([VALUE]) VALUES (@Content)
        END
    
        RETURN
    END

执行：

SELECT * FROM Split('查询,入库,出库,移库,盘点,设置',',')

## 第二题： ##

题目：对于以下数据，补充一段SQL代码，计算出每个步骤的计划开始时间。

原数据：  
![在这里插入图片描述][20210417174255689.png]  
需要的结果：  
![在这里插入图片描述][2021041717432755.png]  
原始数据：

declare @t as table ([产品] nvarchar(50), [工序] nvarchar(50), [加工顺序] int, [加工时间] int, [计划开始时间] datetime)
    insert @t values 
        ('item-a', '备布', 1, 120, '2020-06-26 10:00:00'),
        ('item-a', '染色', 2, 480, null),
        ('item-a', '烘干', 3, 120, null),
        ('item-a', '查布', 4, 60, null),
        ('item-a', '定型过软', 5, 120, null),
        ('item-a', '成品检验', 6, 60, null),
        ('item-a', '入仓', 7, 60, null),
        ('item-b', '备布', 1, 120, '2020-06-24 06:00:00'),
        ('item-b', '预定型', 2, 180, null),
        ('item-b', '染色', 3, 480, null),
        ('item-b', '烘干', 4, 120, null),
        ('item-b', '查布', 5, 60, null),
        ('item-b', '定型过软', 6, 120, null),
        ('item-b', '成品检验', 7, 60, null),
        ('item-b', '入仓', 8, 60, null)

-- 请在此加入代码更新【计划开始时间】。
    
    -- 每个产品下一步的开始时间等于上一步的【计划开始时间】加上【加工时间】。【加工时间】的单位为分钟。
    
    -- 比如【item-a】的第2步【染色】的计划开始时间等于 2020-06-26 12:00:00.000

审题：这道题主要是相同产品依加工顺序进行时间的累加，由于加工序顺序是加1递增的，大大减低了这道题的难度，否则可能要考虑使用游标来写。

做题：下面是我的写法，仅供参考。

DECLARE @I INT =2,@MAX INT
    SELECT @MAX=MAX(加工顺序) FROM @t
    WHILE @I<=@MAX
    BEGIN
        UPDATE A SET A.计划开始时间=DATEADD(MI,B.加工时间,B.计划开始时间) FROM @t A INNER JOIN @t B ON A.产品=B.产品 AND A.加工顺序-1=B.加工顺序 WHERE A.加工顺序=@I
        SET @I=@I+1
    END
    SELECT * FROM @t

## 第三题： ##

题目：部门工资前三高的所有员工。

`Employee` 表包含所有员工信息，每个员工有其对应的工号 `Id`，姓名 `Name`，工资 `Salary` 和部门编号 `DepartmentId` 。  
![在这里插入图片描述][2021041717473233.png]  
`Department` 表包含公司所有部门的信息。  
![在这里插入图片描述][20210417174741439.png]  
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

Select id,name from Employee group by Department order by Salary ASC limit 3;

![在这里插入图片描述][2021041717480544.png]  
解释：

IT 部门中，`Max` 获得了最高的工资，`Randy` 和 `Joe` 都拿到了第二高的工资，`Will` 的工资排第三。销售部门（`Sales`）只有两名员工，`Henry` 的工资最高，`Sam` 的工资排第二。

数据：

declare @employee as table (Id int, [Name] varchar(30), [Salary] int, [DepartmentId] int)
    insert @employee([Id], [Name], [Salary], [DepartmentId])
        values (1, 'Joe', 8500, 1)
            , (2, 'Henry', 8000, 2)
            , (3, 'Sam', 6000, 2)
            , (4, 'Max', 9000, 1)
            , (5, 'Janet', 6900, 1)
            , (6, 'Randy', 8500, 1)
            , (7, 'Will', 7000, 1)
    
    declare @department as table (Id int, [Name] varchar(30))
    insert @department([Id], [Name])
    values(1, 'IT'), (2, 'Sales')

审题：题目描述有点长，主要要留意的是相同部门有同薪的人员。

做题：下面是我的写法，仅供参考。

DECLARE @Temp TABLE (DepartmentId INT,Salary INT)
    INSERT INTO @Temp (DepartmentId,Salary) SELECT DISTINCT DepartmentId,Salary FROM @employee
    
    SELECT A.DepartmentId,C.Name DepartmentName,A.Name,A.Salary
    FROM @employee A INNER JOIN 
        (
            SELECT T.* 
            FROM 
                (
                    SELECT DepartmentId,Salary,ROW_NUMBER() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS ROWNUMBER FROM @Temp
                ) T
            WHERE T.ROWNUMBER<=3
        ) B ON A.DepartmentId=B.DepartmentId AND A.Salary=B.Salary LEFT JOIN @department C ON A.DepartmentId=c.Id
    ORDER BY A.DepartmentId,A.Salary DESC,A.ID

总结：有些题看起来很简单，但是要看准考点，才可以临危不乱。道路千千万，你选哪一条？

[20210417172941335.png]: /images/20210626/97d93bdb9f1b428eb1a2a0931f338bd5.png
[20210417174255689.png]: /images/20210626/9d00a6171e2f4d0ab4ca8f2addf40d13.png
[2021041717432755.png]: /images/20210626/153cf46dda0f44e1aa8c95e2b3c208de.png
[2021041717473233.png]: /images/20210626/5ba8e8e062a8416cb18c45ae2f80eed0.png
[20210417174741439.png]: /images/20210626/8c92b9543bae491eafbfcb018c1425c3.png
[2021041717480544.png]: /images/20210626/9f00b855ef6f49109bf5ac9f41b6e900.png