mysql使用查询将记录注入(inject)表

Question

首先-这是面试中被问到的问题。

我有一个包含两列的表格，year 和 value。

• 假设在 year 列中我有 18、19、20 等等，
• 在 value 列中，我有 0.024、0.036、0.048 等等...

他们想让我做的是从那个表中创建月度相同的表，所以现在 18 是 216 (18*12) 而 19 是 228。

但主要思想是现在我每个月都有一条记录，所以现在月份列应该是:216,217,218,219,220,221,222,223,224,225,226,227(每个月一条记录)，依此类推，原始表中的每年我应该有 12 条记录那一年的每个月。

value 列现在应该在新表中相对更新所以假设我有 18 年和值 0.024 一个月:216 值:0.024，月:217 值:0.025 和等等……

如果我们通过编程语言做到这一点，for 循环就可以做到这一点，但我不知道与 sql 中的 for 循环有任何类似的事情......

这是输入表:请运行代码段以查看它的外观

<table>
<tr><th>year </th><th>value </th></tr>
<tr><td>18 </td><td>0.024 </td></tr>
<tr><td>19 </td><td>0.036 </td></tr>
<tr><td>20 </td><td>0.048 </td></tr>
  <tr><td>21 </td><td>0.060 </td></tr>
</table>

输出应该是这样的:请运行代码片段以查看它的样子

<table>
<tr><th>month </th><th>value </th></tr>
<tr><td>216 </td><td>0.024 </td></tr>
  <tr><td>217 </td><td>0.025 </td></tr>
  <tr><td>218 </td><td>0.026 </td></tr>
  <tr><td>219 </td><td>0.027 </td></tr>
  <tr><td>220 </td><td>0.028 </td></tr>
  <tr><td>221 </td><td>0.029 </td></tr>
  <tr><td>222 </td><td>0.030 </td></tr>
  <tr><td>223 </td><td>0.031 </td></tr>
  <tr><td>224 </td><td>0.032 </td></tr>
  <tr><td>225 </td><td>0.033 </td></tr>
  <tr><td>226 </td><td>0.034 </td></tr>
  <tr><td>227 </td><td>0.035 </td></tr>
  <tr><td>228 </td><td>0.036 </td></tr>
  <tr><td>... </td><td>... </td></tr>
  <tr><td>... </td><td>... </td></tr>
</table>

将这些片段想象成问题的输入和输出......

Answer 1

因为是面试题，所以我不会用 MySql 来回答你，而是用 TSQL(虽然翻译起来很简单，我把这个练习留给你)

SQLFiddleDemo

SELECT (tab.year * 12 + num) AS month
     ,value + 0.001 * num AS value
FROM tab 
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5),
                   (6), (7), (8), (9), (10), (11)) AS t(num)
ORDER BY 1;

工作原理:

1)从tab获取数据

2) 用数字 0 - 11 的子查询生成笛卡尔积，每行彼此

3) 在 SELECT 中计算月份为 tab.year * 12 + num 和值并添加新别名

4) 按月排序

再看一遍，我没有使用任何循环，但我的解决方案是 100% 基于集合的。要将它存储在第二个表中，我只需要 SELECT ... INTO new_table_name... (TSQL):

编辑:

Demo

CREATE TABLE tab(year INT, value NUMERIC(10,4));

INSERT INTO tab(year, value)
VALUES (18, 0.024), (19, 0.036), (20, 0.048), (21, 0.060);

CREATE TABLE new_table AS
SELECT (t.year * 12 + c.num) AS month
     ,t.value + ABS(t1.value-t.value)/12 * c.num AS value
FROM tab t
JOIN tab t1
  ON t.year = t1.year + 1
CROSS JOIN (SELECT 0 AS num      UNION ALL    SELECT 1     UNION ALL 
            SELECT 2             UNION ALL    SELECT 3     UNION ALL 
            SELECT 4             UNION ALL    SELECT 5     UNION ALL 
            SELECT 6             UNION ALL    SELECT 7     UNION ALL 
            SELECT 8             UNION ALL    SELECT 9    UNION ALL 
            SELECT 10            UNION ALL    SELECT 11) AS c

ORDER BY 1;

SELECT *
FROM new_table;