（大众金融）SQL server面试题（2）-游戏打包销售折扣

今天，面试了一家公司，什么也不说先来三道面试题做做，第二题。

那么，我们就开始做题吧，谁叫我们是打工人呢。

题目是这样的：

销售平台进行游戏打包促销。将任意个游戏打包为一组，根据游戏数量制定折扣。
打包的游戏数量限定2个至4个。当包含2个游戏时折扣为9折，3个时8折，4个时6折。
问1：计算有多少种(个数)不同的打包组合方式。
问2：打包购买游戏时，分别计算出2个打包，3个打包和4个打包时价格最贵的包。要求最终用一个查询得出结果。

GNAME	AMT
A	29.33
B	19.22
C	25.81
D	16.79
E	20.78
F	25.32

---------------------------------------------------------------------------------------------------------------------------------

DROP TABLE IF EXISTS #T_GAME;				
CREATE TABLE #T_GAME (				
  ID INT IDENTITY(1,1),				
  GNAME VARCHAR(50),				
  AMT DECIMAL(10,2),				
  NOTE VARCHAR(255)				
);				
				
INSERT INTO #T_GAME(GNAME, AMT, NOTE) VALUES ('A', 29.33, NULL);				
INSERT INTO #T_GAME(GNAME, AMT, NOTE) VALUES ('B', 19.22, NULL);				
INSERT INTO #T_GAME(GNAME, AMT, NOTE) VALUES ('C', 25.81, NULL);				
INSERT INTO #T_GAME(GNAME, AMT, NOTE) VALUES ('D', 16.79, NULL);				
INSERT INTO #T_GAME(GNAME, AMT, NOTE) VALUES ('E', 20.78, NULL);				
INSERT INTO #T_GAME(GNAME, AMT, NOTE) VALUES ('F', 25.32, NULL);  				
				
				
SELECT T.GROUP_TYPE ,COUNT(1) AS"个数"				
FROM				
(				
SELECT '2' AS GROUP_TYPE, CONCAT(A.GNAME , B.GNAME) AS GNAME_GROUP , A.AMT + B.AMT AS AMT				
FROM #T_GAME A, #T_GAME B				
WHERE A.GNAME < B.GNAME				
UNION				
SELECT '3' AS GROUP_TYPE, CONCAT(A.GNAME, B.GNAME, C.GNAME) AS GNAME_GROUP, A.AMT + B.AMT + C.AMT AS AMT				
FROM #T_GAME A, #T_GAME B, #T_GAME C				
WHERE A.GNAME < B.GNAME				
AND B.GNAME < C.GNAME				
UNION				
SELECT '4' AS GROUP_TYPE, CONCAT(A.GNAME, B.GNAME, C.GNAME, D.GNAME) AS GNAME_GROUP, A.AMT + B.AMT + C.AMT + D.AMT AS AMT				
FROM #T_GAME A, #T_GAME B, #T_GAME C, #T_GAME D				
WHERE A.GNAME < B.GNAME				
AND B.GNAME < C.GNAME				
AND C.GNAME < D.GNAME				
) T				
GROUP BY GROUP_TYPE;				
				
SELECT TOP 1 GROUP_TYPE, GNAME_GROUP, AMT				
FROM				
(				
SELECT '2' AS GROUP_TYPE, CONCAT(A.GNAME , B.GNAME) AS GNAME_GROUP , (A.AMT + B.AMT) * 0.9 AS AMT				
FROM #T_GAME A, #T_GAME B				
WHERE A.GNAME < B.GNAME				
UNION				
SELECT '3' AS GROUP_TYPE, CONCAT(A.GNAME, B.GNAME, C.GNAME) AS GNAME_GROUP, (A.AMT + B.AMT + C.AMT) * 0.8 AS AMT				
FROM #T_GAME A, #T_GAME B, #T_GAME C				
WHERE A.GNAME < B.GNAME				
AND B.GNAME < C.GNAME				
UNION				
SELECT '4' AS GROUP_TYPE, CONCAT(A.GNAME, B.GNAME, C.GNAME, D.GNAME) AS GNAME_GROUP, (A.AMT + B.AMT + C.AMT + D.AMT) * 0.6 AS AMT				
FROM #T_GAME A, #T_GAME B, #T_GAME C, #T_GAME D				
WHERE A.GNAME < B.GNAME				
AND B.GNAME < C.GNAME				
AND C.GNAME < D.GNAME				
) T ORDER BY AMT DESC;

查询结果如下：