使用值收集多个列作为新行数(Gather several column using value as number of new rows)

网站建设800 更新时间：2025-06-19 10:04:43

我想将一个有点脏的表格转换成标准化表格。表格的结构如下：

CREATE TABLE dirty_table( date DATE NOT NULL ,name VARCHAR(24) NOT NULL ,co BIT NOT NULL ,en BIT NOT NULL ,re BIT NOT NULL ,po BIT NOT NULL ,ga BIT NOT NULL ,pr BIT NOT NULL ,bi INTEGER NOT NULL );

有点类似于这个问题，但有一个警告，我有一个位/整数而不是真/假方式的值，位列可以包含值0和1，和双列任何正数和0.我想创建一个新行保留名称和日期列以及非零列的名称。像这样的东西：

date |name |proc | -----------|----------|-----| 2017-07-04 |Jonny doe |bi | 2017-07-04 |Jonny doe |bi | 2017-07-07 |Jonny doe |ga | 2017-07-04 |Jonny doe |po | 2017-07-04 |Jonda doe |en | 2017-07-04 |Jonda doe |co | 2017-07-07 |Jonda doe |re | 2017-07-07 |Jonda doe |re | 2017-08-03 |Jonda doe |re | 2017-08-08 |Josep doe |en | 2017-08-09 |Josep doe |bi | 2017-08-11 |Josep doe |ga |

可以看出，如果值> 1，则bi列可以出现多次。其他人，除非另有一行，否则可能只有date，name和proc列的组合，如在dirty_table的摘录中所见：

date name co en re po ga pr bi 2017-07-03 DPSUW 1 1 0 0 0 0 2 2017-07-03 XDUPT 1 0 0 0 0 0 0 2017-07-03 XIYUD 0 1 0 0 0 0 1 2017-07-03 HBJRL 1 1 0 0 0 0 2 2017-07-03 DIHMP 1 1 0 0 0 0 1 2017-07-04 MTHDT 1 1 0 0 0 0 2 2017-07-04 MFPLI 0 1 0 0 0 0 1 2017-07-04 GKHFG 1 0 0 0 0 0 1 2017-07-04 QKDNE 1 1 0 0 0 0 2 2017-07-04 GSXLN 1 1 0 0 0 0 2 2017-07-05 ICKUT 0 1 0 0 0 0 1 2017-07-05 NHVLT 0 1 0 0 0 0 1 2017-07-05 KTSFX 1 1 0 0 0 0 1 2017-07-05 AINSA 1 1 0 0 0 0 2 2017-07-07 YUCAU 0 1 0 0 0 0 1 2017-07-07 YLLVX 1 0 0 0 0 0 1 2017-07-10 CSIMK 1 1 0 0 0 0 2 2017-07-10 PWNCV 0 1 0 0 0 0 1 2017-07-10 AMMVX 0 1 0 0 0 0 1 2017-07-11 BLELT 0 1 0 0 0 0 1 2017-07-11 ONAKD 0 1 0 0 0 0 1 2017-07-11 IGJDK 1 0 0 0 0 0 1 2017-07-11 TOQLH 1 1 0 0 0 0 2 2017-07-11 DUQWM 1 0 0 0 0 0 0 2017-07-11 SFWVP 1 1 0 0 0 0 2 2017-07-12 MQVHW 0 1 0 0 0 0 1 2017-07-12 OFHWQ 0 1 0 0 0 0 1 2017-07-12 MPOAK 1 1 0 0 0 0 1 2017-07-12 YPFEH 1 1 0 0 0 0 1 2017-07-12 XUENE 1 0 0 0 0 0 1

我试图使用case语句，但只创建一行。如何使用值作为要创建的新行数创建一个记录中的多个行？我更喜欢使用泛型SQL，但我使用MariaDB。

I want to convert a somewhat dirty table into a normalized one. The structure of the table is as follow:

CREATE TABLE dirty_table( date DATE NOT NULL ,name VARCHAR(24) NOT NULL ,co BIT NOT NULL ,en BIT NOT NULL ,re BIT NOT NULL ,po BIT NOT NULL ,ga BIT NOT NULL ,pr BIT NOT NULL ,bi INTEGER NOT NULL );

Somewhat similar to this question but with a caveat, I have a bit/integer instead for values in a true/false fashion, bit columns can contain values 0 and 1, and the bi column any positive number and 0. I want to create a new row keeping name and date column and the name of the non zero column. Something like this:

date |name |proc | -----------|----------|-----| 2017-07-04 |Jonny doe |bi | 2017-07-04 |Jonny doe |bi | 2017-07-07 |Jonny doe |ga | 2017-07-04 |Jonny doe |po | 2017-07-04 |Jonda doe |en | 2017-07-04 |Jonda doe |co | 2017-07-07 |Jonda doe |re | 2017-07-07 |Jonda doe |re | 2017-08-03 |Jonda doe |re | 2017-08-08 |Josep doe |en | 2017-08-09 |Josep doe |bi | 2017-08-11 |Josep doe |ga |

As can be seen, the bi column can appear several times if the value is >1. Others, unless there's another row, are likely to have only one combination of date, name and proc column, as seen in this excerpt of dirty_table:

date name co en re po ga pr bi 2017-07-03 DPSUW 1 1 0 0 0 0 2 2017-07-03 XDUPT 1 0 0 0 0 0 0 2017-07-03 XIYUD 0 1 0 0 0 0 1 2017-07-03 HBJRL 1 1 0 0 0 0 2 2017-07-03 DIHMP 1 1 0 0 0 0 1 2017-07-04 MTHDT 1 1 0 0 0 0 2 2017-07-04 MFPLI 0 1 0 0 0 0 1 2017-07-04 GKHFG 1 0 0 0 0 0 1 2017-07-04 QKDNE 1 1 0 0 0 0 2 2017-07-04 GSXLN 1 1 0 0 0 0 2 2017-07-05 ICKUT 0 1 0 0 0 0 1 2017-07-05 NHVLT 0 1 0 0 0 0 1 2017-07-05 KTSFX 1 1 0 0 0 0 1 2017-07-05 AINSA 1 1 0 0 0 0 2 2017-07-07 YUCAU 0 1 0 0 0 0 1 2017-07-07 YLLVX 1 0 0 0 0 0 1 2017-07-10 CSIMK 1 1 0 0 0 0 2 2017-07-10 PWNCV 0 1 0 0 0 0 1 2017-07-10 AMMVX 0 1 0 0 0 0 1 2017-07-11 BLELT 0 1 0 0 0 0 1 2017-07-11 ONAKD 0 1 0 0 0 0 1 2017-07-11 IGJDK 1 0 0 0 0 0 1 2017-07-11 TOQLH 1 1 0 0 0 0 2 2017-07-11 DUQWM 1 0 0 0 0 0 0 2017-07-11 SFWVP 1 1 0 0 0 0 2 2017-07-12 MQVHW 0 1 0 0 0 0 1 2017-07-12 OFHWQ 0 1 0 0 0 0 1 2017-07-12 MPOAK 1 1 0 0 0 0 1 2017-07-12 YPFEH 1 1 0 0 0 0 1 2017-07-12 XUENE 1 0 0 0 0 0 1

I was trying to use case statements but that only creates a single row. How can I create multiple rows from one record using the value as number of new rows to create? I prefer using generic SQL, but I'm using MariaDB.

最满意答案

最简单的方法可能是union all ：

select date, name, 'co' as proc from t where co >= 1 union all select date, name, 'en' as proc from t where en >= 1 union all . . . select date, name, 'bi' as proc from t where bi >= 1 union all select date, name, 'bi' as proc from t where bi >= 2;

bi的多行。

The simplest method is probably union all:

select date, name, 'co' as proc from t where co >= 1 union all select date, name, 'en' as proc from t where en >= 1 union all . . . select date, name, 'bi' as proc from t where bi >= 1 union all select date, name, 'bi' as proc from t where bi >= 2;

That the multiple rows for bi.