- 执行[SQL测试题脚(UTF-8) .txt ] 创建待测试的数据.
- 要求编写一个存储过程 spGetTest 统计T_Test表的数据 , 执行输出结果如下图:
面试题是要求用SQL Server ;我电脑上没有就用My sql ,思路都是一样的,SQL也差不多:
/*先判断表是否存在,存在就先删除表*/
DROP TABLE IF EXISTS `T_Test`;
/*mysql 创建表 T_Test*/
create table T_Test(
fUser_Name nvarchar(40),
fUser_Dep nvarchar(40),
fMoney numeric(24,6),
fDate datetime
);
/*插入数据*/
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小王','营业部',120.3,'2023/06/21 11:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小王','营业部',450,'2023/06/21 12:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小王','营业部',100,'2023/06/21 14:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小李','营业部',110.34,'2023/06/21 10:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小李','营业部',120,'2023/06/21 11:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('老刘','生产部',300,'2023/06/21 19:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('老刘','生产部',150,'2023/06/21 14:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('郭总','生产部',220,'2023/06/21 11:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('郭总','生产部',340,'2023/06/21 18:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('郭总','生产部',60,'2023/06/21 16:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('郭总','生产部',508,'2023/06/21 13:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('孙总','储运部',400.06,'2023/06/21 14:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('孙总','储运部',220,'2023/06/21 13:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('孙总','储运部',520,'2023/06/21 15:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小黄','储运部',720,'2023/06/21 16:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小黄','储运部',150,'2023/06/21 18:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('大彭','储运部',190.2,'2023/06/21 12:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('大彭','储运部',170,'2023/06/21 16:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('大彭','储运部',180,'2023/06/21 18:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('大彭','储运部',409,'2023/06/21 12:00:00');
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('二胖','储运部',360,'2023/06/21 17:00:00');
/*要求编写一个存储过程 spGetTest 统计T_Test表的数据 , 执行输出结果如下图*/
/*SQL*/
select * from T_Test where fUser_Dep='储运部' and fUser_Name='大彭'
SELECT
*
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
FORMAT( SUM( fMoney ), 2 ) AS '发生金额',
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '储运部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '大彭', '二胖', '孙总', '小黄' )) t UNION
SELECT
'储运部_合计:',
'',
FORMAT( SUM( t1.sum_1 ), 2 ),
''
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
SUM( fMoney ) AS sum_1,
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '储运部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '大彭', '二胖', '孙总', '小黄' )) t1 UNION
SELECT
*
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
FORMAT( SUM( fMoney ), 2 ) AS '发生金额',
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '生产部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '郭总', '老刘' )) t2 UNION
SELECT
'生产部_合计:',
'',
FORMAT( SUM( t3.sum_1 ), 2 ),
''
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
SUM( fMoney ) AS sum_1,
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '生产部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '郭总', '老刘' )) t3 UNION
SELECT
*
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
FORMAT( SUM( fMoney ), 2 ) AS '发生金额',
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '营业部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '小李', '小王' )) t4 UNION
SELECT
'营业部_合计:',
'',
FORMAT( SUM( t5.sum_1 ), 2 ),
''
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
SUM( fMoney ) AS sum_1,
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '营业部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '小李', '小王' )) t5
/*存储过程*/
CREATE PROCEDURE spGetTest()
BEGIN
SELECT
*
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
FORMAT( SUM( fMoney ), 2 ) AS '发生金额',
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '储运部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '大彭', '二胖', '孙总', '小黄' )) t UNION
SELECT
'储运部_合计:',
'',
FORMAT( SUM( t1.sum_1 ), 2 ),
''
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
SUM( fMoney ) AS sum_1,
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '储运部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '大彭', '二胖', '孙总', '小黄' )) t1 UNION
SELECT
*
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
FORMAT( SUM( fMoney ), 2 ) AS '发生金额',
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '生产部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '郭总', '老刘' )) t2 UNION
SELECT
'生产部_合计:',
'',
FORMAT( SUM( t3.sum_1 ), 2 ),
''
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
SUM( fMoney ) AS sum_1,
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '生产部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '郭总', '老刘' )) t3 UNION
SELECT
*
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
FORMAT( SUM( fMoney ), 2 ) AS '发生金额',
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '营业部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '小李', '小王' )) t4 UNION
SELECT
'营业部_合计:',
'',
FORMAT( SUM( t5.sum_1 ), 2 ),
''
FROM
(
SELECT
'' AS '部门',
fUser_Name AS '用户名称',
SUM( fMoney ) AS sum_1,
max( fDate ) AS '最大发生日期'
FROM
T_Test
WHERE
fUser_Dep = '营业部'
GROUP BY
fUser_Name
ORDER BY
field( fUser_Name, '小李', '小王' )) t5 ;
END
/*执行*/
CALL spGetTest()