在Mysql中，根据另一列向表中批量添加多个列
P粉336536706
2023-08-28 11:03:44
<p>我有一个有三列的表格。
对于每个id，我们有多达400个<code>index</code>值。我想根据index的数量添加列。在我提供的示例中，我有4个index，然后我在表格中添加了四列。这是我拥有的表格：</p>
<pre class="brush:php;toolbar:false;">Create table buy_sell (id int, idx varchar(255), sell float(2, 1));
insert into buy_sell (id, idx, sell) values ('1', 'a', '4');
insert into buy_sell (id, idx, sell) values ('1', 'b', '6');
insert into buy_sell (id, idx, sell) values ('1', 'c', '8');
insert into buy_sell (id, idx, sell) values ('1', 'd', '9');
insert into buy_sell (id, idx, sell) values ('3', 'b ', '1');
insert into buy_sell (id, idx, sell) values ('3', 'c ', '2');
insert into buy_sell (id, idx, sell) values ('2', 'a', '5');
insert into buy_sell (id, idx, sell) values ('2', 'b', '7');
insert into buy_sell (id, idx, sell) values ('2', 'd', '5');
SELECT * FROM buy_sell;</pre>
<p>这是结果：</p>
<table class="s-table">
<thead>
<tr>
<th>id</th>
<th>idx</th>
<th>出售</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>一个</td>
<td>4.0</td>
</tr>
<tr>
<td>1</td>
<td>b</td>
<td>6.0</td>
</tr>
<tr>
<td>1</td>
<td>c</td>
<td>8.0</td>
</tr>
<tr>
<td>1</td>
<td>d</td>
<td>9.0</td>
</tr>
<tr>
<td>3</td>
<td>b</td>
<td>1.0</td>
</tr>
<tr>
<td>3</td>
<td>c</td>
<td>2.0</td>
</tr>
<tr>
<td>2</td>
<td>一个</td>
<td>5.0</td>
</tr>
<tr>
<td>2</td>
<td>b</td>
<td>7.0</td>
</tr>
<tr>
<td>2</td>
<td>d</td>
<td>5.0</td>
</tr>
</tbody>
</table>
<p>例如，对于id=1，我们在这里有四个index（a，b，c，d），然后我们有四个非零列。对于id = 3，我们有两个index（b，c），然后我们有两个非零列，所以对于第一列，我们放置零，对于第二列，我们放置1，对于第三列，我们放置2。依此类推。这是我想要的表格：</p>
<table class="s-table">
<thead>
<tr>
<th>id</th>
<th>卖出1</th>
<th>卖2</th>
<th>卖3</th>
<th>卖4</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>4</td>
<td>6</td>
<td>8</td>
<td>9</td>
</tr>
<tr>
<td>3</td>
<td>0</td>
<td>1</td>
<td>2</td>
<td>0</td>
</tr>
<tr>
<td>2</td>
<td>5</td>
<td>7</td>
<td>0</td>
<td>5</td>
</tr>
</tbody>
</table>
<p>我搜索了很多，并尝试了<code>Group_concat</code>，<code>JSON_ARRAYAGG</code>等，但我找不到解决方法。我需要做什么？</p>
SQL语言对于在查询编译时知道结果中的列数有非常严格的要求，在查看任何数据之前。如果你必须查看数据来确定你想要的列数，那么你只能使用（潜在危险的）动态SQL，需要三个步骤：
在这种情况下，你不知道需要多少列，只知道它是“最多400列”。考虑到这一点，你可能会看到像这样的代码：
SELECT ID, MAX(CASE WHEN IDX = 'a' THEN sell ELSE 0 END) as sell1, MAX(CASE WHEN IDX = 'b' THEN sell ELSE 0 END) as sell2, MAX(CASE WHEN IDX = 'c' THEN sell ELSE 0 END) as sell3, -- ... MAX(CASE WHEN IDX = '??' THEN sell ELSE 0 END) as sell400 FROM `buy_sell` GROUP BY ID是的，你确实需要在查询中为每个可能的列指定一些内容。这也假设你的
sell值都大于0。如果你的值可能是正数和负数的混合，你可以尝试使用SUM()而不是MAX()。这种做法也与关系数据库背后的集合论原则直接相悖，因此在实践中，你最好让客户端代码或报表工具来对数据进行透视。