blocks|key|2127290|text|假设您使用的是MySQL，那么您要查找的函数是GROUP_CONCAT()。使用Eloquent，它看起来像这样：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2127291|$transactions+=+DB::table('tableName')
++++++++++++++++++->select('TRANSACTION_ID',+DB::raw('GROUP_CONCAT(BEACON_TYPE+SEPARATOR+',+')+as+BEACON_TYPE'))
++++++++++++++++++->groupBy('TRANSACTION_ID')
++++++++++++++++++->get();|code-block|syntax|javascript|2127292|请注意，如果您想要更改分隔符，您只需将',+'编辑为其他内容。默认分隔符是','。|2127293|entityMap^0|N|E|0|0|J|4|11|3|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

Assuming you're using MySQL, the function you're looking for is <code>GROUP_CONCAT()</code>. Using Eloquent, it would look something like this:

<pre><code>$transactions = DB::table('tableName')
 -&gt;select('TRANSACTION_ID', DB::raw('GROUP_CONCAT(BEACON_TYPE SEPARATOR ', ') as BEACON_TYPE'))
 -&gt;groupBy('TRANSACTION_ID')
 -&gt;get();
</code></pre>

Note, if you want to change the separator, you just need to edit the <code>', '</code> to something else. The default separator is <code>','</code>.

blocks|key|87409|text|使用GROUP_CONCAT()串联组的值。将其SEPARATOR设置为',+'，以获取带有逗号分隔值的组。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|87410|您的预期结果将丢失一些信息，因为分组行的ID将同时为2和3(在您的示例中)。因此，要么不选择这些ID，要么使用单独的GROUP_CONCAT()添加它们。|87411|原始的SQL查询将使用GROUP_CONCAT()。以下查询使用表foo，请将其替换为您的实际表名。|87412|SELECT+TRANSACTION_ID,+
+++++++GROUP_CONCAT(BEACON_TYPE+SEPARATOR+',+')+AS+BEACON_TYPE
FROM+foo
GROUP+BY+TRANSACTION_ID|code-block|syntax|javascript|87413|使用Eloquent，您需要使用DB::raw()来选择GROUP_CONCAT()部件，因为没有用于GROUP_CONCAT()的Eloquent方法，所以它变成|87414|$result+=+DB::table('foo')
++++++++++++->select('TRANSACTION_ID',
++++++++++++++++++++++DB::raw("GROUP_CONCAT(BEACON_TYPE+SEPARATOR+',+'")+as+BEACON_TYPE)
++++++++++++->groupBy('TRANSACTION_ID');|87415|或者，如果要将分组的if包含到中，则..|87416|$result+=+DB::table('foo')
++++++++++++->select('TRANSACTION_ID',
++++++++++++++++++++++DB::raw("GROUP_CONCAT(BEACON_TYPE+SEPARATOR+',+'")+as+BEACON_TYPE),
++++++++++++++++++++++DB::raw("GROUP_CONCAT(ID+SEPARATOR+',+'")+as+ID)
++++++++++++->groupBy('TRANSACTION_ID');|87417|entityMap^0|2|E|O|9|10|4|0|K|2|1M|E|0|B|E|X|3|0|0|G|9|S|E|1F|E|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|15|8|@$9|16|A|17|B|C]|$9|18|A|19|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1A|8|@$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|1F|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|1G|8|@$9|1H|A|1I|B|C]|$9|1J|A|1K|B|C]|$9|1L|A|1M|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|L|7|1N|8|@]|D|@]|E|$M|N]]|$1|S|3|T|5|6|7|1O|8|@]|D|@]|E|$]]|$1|U|3|V|5|L|7|1P|8|@]|D|@]|E|$M|N]]|$1|W|3|-4|5|6|7|1Q|8|@]|D|@]|E|$]]]|X|$]]

Use <code>GROUP_CONCAT()</code> to concatenate values of the group. Give it a <code>SEPARATOR</code> of <code>', '</code> to get the groups with comma separated values. 

Your expected result is going to lose some information, since the <code>ID</code>s of the grouped row will be both 2 and 3 (in your example). So either don't select those IDs, or add them with a separate <code>GROUP_CONCAT()</code>.

The raw SQL query would be using <code>GROUP_CONCAT()</code>. The following queries use the table <code>foo</code>, replace that with your actual table name. 

<pre class="lang-sql prettyprint-override"><code>SELECT TRANSACTION_ID, 
 GROUP_CONCAT(BEACON_TYPE SEPARATOR ', ') AS BEACON_TYPE
FROM foo
GROUP BY TRANSACTION_ID
</code></pre>

Using Eloquent, you need to use <code>DB::raw()</code> to select the <code>GROUP_CONCAT()</code> part, as there's no Eloquent method for <code>GROUP_CONCAT()</code>, so then it becomes

<pre><code>$result = DB::table('foo')
 -&gt;select('TRANSACTION_ID',
 DB::raw("GROUP_CONCAT(BEACON_TYPE SEPARATOR ', '") as BEACON_TYPE)
 -&gt;groupBy('TRANSACTION_ID');
</code></pre>

or if you want to include the grouped IDs to, then..

<pre><code>$result = DB::table('foo')
 -&gt;select('TRANSACTION_ID',
 DB::raw("GROUP_CONCAT(BEACON_TYPE SEPARATOR ', '") as BEACON_TYPE),
 DB::raw("GROUP_CONCAT(ID SEPARATOR ', '") as ID)
 -&gt;groupBy('TRANSACTION_ID');
</code></pre>

blocks|key|2127295|text|如果您已经拥有数据库中的数据，并且只想使用collection+functions，则可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2127296|++++$things+=+$things->groupBy('TRANSACTION_ID')
++++++++++++->map(function($x)+{
++++++++++++++++$beaconTypes+=+$x->implode('BEACON_TYPE',+',+');
++++++++++++++++$firstThing+=+$x->first();
++++++++++++++++$firstThing['BEACON_TYPE']+=+$beaconTypes;

++++++++++++++++return+$firstThing;
++++++++++++});|code-block|syntax|javascript|2127297|entityMap|0|LINK|mutability|MUTABLE|url|https://laravel.com/docs/5.8/collections^0|L|K|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

If you already have the data from the database and just want to use <a href="https://laravel.com/docs/5.8/collections" rel="nofollow noreferrer">collection functions</a> you can do the following:

<pre><code> $things = $things-&gt;groupBy('TRANSACTION_ID')
 -&gt;map(function($x) {
 $beaconTypes = $x-&gt;implode('BEACON_TYPE', ', ');
 $firstThing = $x-&gt;first();
 $firstThing['BEACON_TYPE'] = $beaconTypes;

 return $firstThing;
 });
</code></pre>

I'm facing a relatively easy problem but I can't manage to wrap my head around a solution. Keep in mind I'm using PHP with the Laravel framework if it can makes things any easier.

I have a table filled with data like so :

<pre><code>ID | TRANSACTION_ID | BEACON_TYPE
---+----------------+---------------
 1 | 1 | "abc"
 2 | 2 | "def"
 3 | 2 | "xyz"
</code></pre>

and I would like to group them by transaction ID and to keep the beacon type inside the data like so:

<pre><code>ID | TRANSACTION_ID | BEACON_TYPE
---+----------------+---------------
 1 | 1 | "abc"
 2 | 2 | "def", "xyz"
</code></pre>

I've tried using group by with no avail. Any ideas or hints on how to accomplish that? As I said earlier there might be some way to do it with Laravel Eloquent.

SQL : Merge two rows by some ID and keep values

数据库

MySQL

我正面临着一个相对简单的问题，但我不能想出一个解决方案。请记住，我正在使用PHP和Laravel框架，如果它能让事情变得更容易的话。我有一个数据填充表，如下所示：ID | TRANSACTION_ID | BEACON_TYPE---+----------------+--------------- 1 |      ...

问SQL :按ID合并两行并保留值
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL :按ID合并两行并保留值EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL :按ID合并两行并保留值
EN