连接多个行中的某些列 [英] concatenate certain columns across multiple rows

问题描述

我有一个看起来像这样的数据集

I have a dataset that looks like this

RID          SID          MID          QID          QText
------------------------------------------------------------------
NULL         NULL         NULL         10,20,30     't1','t2','t3'
10           14           13           4            'text2'
100          141          131          5,6          't5','t6'

我想运行一些sql命令，该命令基本上将带有空值的行并连接QID和QText列到具有有效RID，SID，MID的每一行

I'd like to run some sql command that would basically take the row with the nulls and concatenate the QID and QText columns to each row that has a valid RID, SID, MID

因此最终结果将是与此类似的数据集(在这种情况下，第一行不需要在那儿，因为我已将在该行中获得的信息串联到其他行中).

so the end result would be a dataset similar to this (in this case the first row doesn't need to be there because I've concatenated the info I've got in that row to the other rows).

RID          SID          MID          QID          QText
------------------------------------------------------------------
NULL         NULL         NULL         10,20,30     't1','t2','t3'
10           14           13           4,10,20,30   'text2','t1','t2','t3'
100          141          131          5,6,10,20,30 't5','t6','t1','t2','t3'

我已经尝试了几种具有不同分组方式的group_concats，但是不能完全按照我需要的方式工作.使用原始SQL(mysql)可以进行这种转换吗?

I've tried several group_concats with different grouping but can't quite get it to work the way I need it to. Is this transform possible with raw SQL (mysql) ?

到目前为止，我已经尝试过一些(确实很糟糕的尝试，因为我只是不知道该怎么做才能做我想做的事情)

Some of what I've tried so far (really bad attempts because I just don't know what will do what I'm trying to do) are

从myTable组中选择group_concat(QText)，方法是? < ---我不知道可以分组的任何内容都会给我我想要的东西.这就是我真正的错误尝试的意思.我知道他们错了(按ID，QID等分组).还考虑过并尝试对要连接的列求和.

select group_concat(QText) from myTable group by ? <--- I don't know of anything that I can group by that will give me what i'm looking for. That's what I mean by really bad attempts. I know they are wrong (group by id, qid, etc, etc). Also thought about and tried a sum on the columns that I want to concatenate.

推荐答案

如果希望将NULL行与所有非NULL行组合在一起，请为每个组创建一个副本.例如，您可以导出不同的RID, SID, MID组合列表

If you want the NULL row(s) to be grouped with all non-NULL rows, make a copy for every group. You could, for instance, derive the list of distinct RID, SID, MID combinations

SELECT DISTINCT RID, SID, MID, QID, QText
FROM myTable
WHERE RID IS NOT NULL
   OR SID IS NOT NULL
   OR MID IS NOT NULL

并将其与NULL行交叉连接:

and cross join it with the NULL row(s):

SELECT
  groups.RID, groups.SID, groups.MID,
  empty.QID, empty.QText
FROM
(
  SELECT DISTINCT RID, SID, MID
  FROM myTable
  WHERE RID IS NOT NULL
     OR SID IS NOT NULL
     OR MID IS NOT NULL
) AS groups
CROSS JOIN
(
  SELECT QID, QText
  FROM myTable
  WHERE RID IS NULL
    AND SID IS NULL
    AND MID IS NULL
) AS empty

然后将结果集与原始集结合起来

then combine the resulting set with the original set:

SELECT RID, SID, MID, QID, QText
FROM myTable

UNION ALL

SELECT
  groups.RID, groups.SID, groups.MID,
  empty.QID, empty.QText
FROM
(
  SELECT DISTINCT RID, SID, MID
  FROM myTable
  WHERE RID IS NOT NULL
     OR SID IS NOT NULL
     OR MID IS NOT NULL
) AS groups
CROSS JOIN
(
  SELECT QID, QText
  FROM myTable
  WHERE RID IS NULL
    AND SID IS NULL
    AND MID IS NULL
) AS empty

现在只需将合并的结果集用作派生表并从中获取GROUP_CONCAT:

Now just use the combined result set as a derived table and get your GROUP_CONCATs from it:

SELECT
  RID, SID, MID,
  GROUP_CONCAT(QID) AS QID,
  GROUP_CONCAT(QText) AS QText
FROM
(
  SELECT …  /* the above UNION ALL query here */
) AS s
GROUP BY
  RID, SID, MID
;

这篇关于连接多个行中的某些列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！