一旦数据进入系统中，就修补不良的BAD数据库设计
我知道那不是问题……反正这就是问题。
我已经继承了一个数据库，该数据库具有1(one)表，看起来像这样。其目的是记录在各个(200个奇数)国家中发现了哪些物种。
ID Species Afghanistan Albania Algeria American Samoa Andorra Angola .... Western Sahara Yemen Zambia Zimbabwe
数据样本将是这样的
id Species Afghanistan Albania American Samoa 1 SP1 null null null 2 SP2 1 1 null 3 SP3 null null 1
在我看来，这是一种典型的多对多情况，我想要3张桌子。
物种，国家和地区
链接表(SpeciesFoundInCountry)在种类表和国家表中都将具有外键。
(很难绘制图表！)
Species SpeciesID SpeciesName Country CountryID CountryName SpeciesFoundInCountry CountryID SpeciesID
有没有一种神奇的方法可以生成一个插入语句，该语句将基于原始兆表中的列名和SpeciesID从新的Country表中获取CountryID？
我可以为一个国家/地区做这件事(可以选择显示我想要的东西)
SELECT Species.ID, Country.CountryID FROM Country, Species WHERE (((Species.Afghanistan)=1)) AND (((Country.Country)="Afghanistan"));
(巨型表称为物种)
但是使用这种策略，我需要对原始表中的每一列进行查询。
有没有办法在sql中做到这一点？
我想我可以将where子句或者一起加载，然后编写脚本来制作sql，虽然看起来不太优雅！
有什么想法(或者需要澄清)吗？
解决方案
回答
为什么要在SQL中执行此操作？只需编写一个执行转换的小脚本即可。
回答
我们可能要在适当的位置创建替换表。脚本的种类取决于我们可用的脚本语言，但是我们只需列出当前表中的列就可以创建国家/地区ID表。完成此操作后，我们可以进行一些字符串替换以遍历所有唯一的国家/地区名称，并将其插入到物种国家/地区表中，其中给定国家/地区列不为null。
回答
我将使用脚本来生成所有单个查询，因为这是一次性导入过程。
一些程序(例如Excel)擅长混合不同维度的数据(将列名与行内的数据进行比较)，而关系数据库很少。
但是，我们可能会发现某些系统(令人惊讶的是，例如Microsoft Access)具有方便的工具，可用于标准化数据。就个人而言，我会发现编写脚本的速度更快，但是我们对Access和脚本编写的相对技能可能与我有所不同。
回答
当我遇到这些时，我写了一个脚本来进行转换，而不是尝试在SQL中进行转换。对于我来说，它通常更快，更轻松。选择我们喜欢的任何语言。
回答
我们可能会变得很聪明，并在系统表中查询列名，然后构建一个动态查询字符串来执行，但是老实说，这可能比为我们生成SQL语句的快速脚本更难看。
希望我们没有太多动态SQL代码来访问代码库中埋藏的旧表。那可能是真正困难的部分。
回答
在SQL Server中，这将生成我们演示的自定义选择。我们可以外推到插入
select 'SELECT Species.ID, Country.CountryID FROM Country, Species WHERE (((Species.' + c.name + ')=1)) AND (((Country.Country)="' + c.name + '"))' from syscolumns c inner join sysobjects o on o.id = c.id where o.name = 'old_table_name'
回答
与其他人一样，我很可能会以一种适合任何方式将其作为一次性的快速解决方案。
对于这些类型的转换，它们是一次性的东西，可以快速解决，并且代码不必太优雅，而必须工作。对于这些类型的事情，我已经做了很多方法。
回答
如果这是SQL Server，则可以使用sys.columns表查找原始表的所有列。然后，我们可以使用动态SQL和ivot命令执行所需的操作。在线查找那些语法。
回答
我绝对同意建议，即编写一个小脚本以通过查询每一列来生成SQL。
实际上，脚本可能已经在我们考虑此神奇查询的时间中完成了(我们只需使用一次然后扔掉它，那么这一切就变得神奇而完美了有什么用)
回答
如果这是SQL Server，则可以使用Unpivot命令，但是查看分配给它的标签是否可以访问，对吗？
尽管访问中存在枢转命令，但没有反向语句。
看起来可以通过复杂的连接来完成。查看这篇有趣的文章，了解有关如何取消选择命令的信息。
回答
我将对SpeciesFoundInCountry表进行一些临时修改，使其成为一个三步过程。我会在该表中添加一列以存储国家/地区名称。然后，步骤如下。
1)创建/运行一个脚本，该脚本遍历源表中的列，并在SpeciesFoundInCountry中为每个具有真实值的列创建一条记录。该记录将包含国家名称。
2)运行一条SQL语句，通过加入到"国家/地区名称"上的"国家/地区"表来更新SpeciesFoundInCountry.CountryID字段。
3)通过删除CountryName列来清理SpeciesFoundInCountry表。
这是一些MS Access VB / VBA伪代码，为我们提供要点
Public Sub CreateRelationshipRecords()
Dim rstSource as DAO.Recordset
Dim rstDestination as DAO.Recordset
Dim fld as DAO.Field
dim strSQL as String
Dim lngSpeciesID as Long
strSQL = "SELECT * FROM [ORIGINALTABLE]"
Set rstSource = CurrentDB.OpenRecordset(strSQL)
set rstDestination = CurrentDB.OpenRecordset("SpeciesFoundInCountry")
rstSource.MoveFirst
' Step through each record in the original table
Do Until rstSource.EOF
lngSpeciesID = rstSource.ID
' Now step through the fields(columns). If the field
' value is one (1), then create a relationship record
' using the field name as the Country Name
For Each fld in rstSource.Fields
If fld.Value = 1 then
with rstDestination
.AddNew
.Fields("CountryID").Value = Null
.Fields("CountryName").Value = fld.Name
.Fields("SpeciesID").Value = lngSpeciesID
.Update
End With
End IF
Next fld
rstSource.MoveNext
Loop
' Clean up
rstSource.Close
Set rstSource = nothing
....
End Sub
之后，我们可以运行一个简单的SQL语句来更新SpeciesFoundInCountry表中的CountryID值。
更新SpeciesFoundInCountry内联接Country on SpeciesFoundInCountry.CountryName = Country.CountryName SET SpeciesFoundInCountry.CountryID = Country.CountryID;
最后，我们要做的就是通过删除CountryName列来清理SpeciesFoundInCountry表。
****注意：我发现拥有包含ISO缩写(国家代码)的国家/地区表很有用。有时，它们在其他表中用作外键，因此查询中不必包括与Country表的联接。
有关更多信息：http://en.wikipedia.org/wiki/Iso_country_codes
回答
抱歉，但是流血的发布解析器删除了我发布的空白和格式。它使日志更难阅读。
回答
@stomp：
在我们输入答案的框上方，有几个按钮。 101010是一个代码示例。我们选择所有的代码代码，然后单击该按钮。这样就不会造成太多混乱。
cout>>"I don't know C" cout>>"Hello World"
回答
我将大致使用Union查询：
Dim db As Database
Dim tdf As TableDef
Set db = CurrentDb
Set tdf = db.TableDefs("SO")
strSQL = "SELECT ID, Species, """ & tdf.Fields(2).Name _
& """ AS Country, [" & tdf.Fields(2).Name & "] AS CountryValue FROM SO "
For i = 3 To tdf.Fields.Count - 1
strSQL = strSQL & vbCrLf & "UNION SELECT ID, Species, """ & tdf.Fields(i).Name _
& """ AS Country, [" & tdf.Fields(i).Name & "] AS CountryValue FROM SO "
Next
db.CreateQueryDef "UnionSO", strSQL
然后，我们将拥有一个可以添加到新设计中的视图。
回答
当我读到标题" BAD数据库设计不良"时，我很想知道它有多糟糕。你没有让我失望:)
正如其他人提到的那样，脚本将是最简单的方法。这可以通过用PHP编写大约15行代码来完成。
SELECT * FROM ugly_table; while(row) foreach(row as field => value) if(value == 1) SELECT country_id from country_table WHERE country_name = field; if(field == 'Species') SELECT species_id from species_table WHERE species_name = value; INSERT INTO better_table (...)
显然，这是伪代码，无法按原样工作。我们还可以通过在此处添加插入语句来快速填充国家和物种表。
回答
抱歉，我几乎没有完成Access编程，但是我可以提供一些指南，应该会有所帮助。
首先让我们解决问题。
假定我们通常需要在SpeciesFoundInCountry中为原始表中的每一行生成多行。换句话说，物种往往在一个以上的国家中。使用笛卡尔积，即没有联接条件的联接，实际上很容易做到。
要执行笛卡尔积，我们将需要创建"国家/地区"表。该表应具有从1到N的country_id(N是唯一国家的数量，大约200个)和国家/地区名称。为了使生活更轻松，只需按列顺序使用数字1到N。那将使阿富汗1和阿尔巴尼亚2 ...津巴布韦N。我们应该能够使用系统表来执行此操作。
接下来，从原始表创建一个表或者视图，其中包含物种和每个国家0或者1的字符串。我们将需要将null而不是null转换为文本0或者1，并将所有值连接到单个字符串中。对表的描述以及带有正则表达式的文本编辑器应该使此操作变得容易。首先对单个列进行实验，然后进行工作，然后编辑所有列的创建视图/插入。
接下来，将两个表连接在一起，没有连接条件。这将为我们提供每个国家/地区几乎所有物种的记录。
现在，我们要做的就是过滤掉无效的记录，它们在字符串的相应位置将为零。由于国家表的country_code列具有子字符串位置，因此我们只需过滤掉0处的记录即可。
where substring(new_column,country_code) = '1'
我们仍然需要创建种类表并加入该表
where a.species_name = b.species_name
a和b是表别名。
希望这个帮助
回答
OBTW，
如果查询已经针对旧表运行，则需要创建一个视图，该视图使用新表复制旧表。我们将需要进行分组以对表格进行非规范化。
告诉用户，将来将不再支持旧表/视图，并且所有新查询或者对旧查询的更新都必须使用新表。
回答
如果我不得不创建大量类似的SQL语句并执行所有这些语句，我通常会发现Excel非常方便。进行原始查询。如果我们在A列中有一个国家/地区列表，而在B列中有SQL语句，则格式设置为文本(用引号引起来)，并在该国家/地区在sql中插入了单元格引用
例如=" INSERT INTO new_table SELECT ...(species。"＆A1＆")= ...));""
然后只需将公式复制下来以创建200个不同的SQL语句，将该列复制/粘贴到编辑器中，然后按F5. 当然，我们可以根据需要使用多个变量来执行此操作。
回答
(希望如此)这是一项一次性的练习，因此，一个优雅的解决方案可能不会听起来那么糟糕。
问题(例如，我确定我们只是太了解了！)是在查询中的某个时刻，我们必须列出所有这些列。 :(问题是，最优雅的方法是什么？下面是我的尝试。它看起来很笨拙，因为有很多列，但这可能是我们想要的，或者至少它会指向我们正确的方向。
可能的SQL解决方案：
/* if you have N countries */
CREATE TABLE Country
(id int,
name varchar(50))
INSERT Country
SELECT 1, 'Afghanistan'
UNION SELECT 2, 'Albania',
UNION SELECT 3, 'Algeria' ,
UNION SELECT 4, 'American Samoa' ,
UNION SELECT 5, 'Andorra' ,
UNION SELECT 6, 'Angola' ,
...
UNION SELECT N-3, 'Western Sahara',
UNION SELECT N-2, 'Yemen',
UNION SELECT N-1, 'Zambia',
UNION SELECT N, 'Zimbabwe',
CREATE TABLE #tmp
(key varchar(N),
country_id int)
/* "key" field needs to be as long as N */
INSERT #tmp
SELECT '1________ ... _', 'Afghanistan'
/* '1' followed by underscores to make the length = N */
UNION SELECT '_1_______ ... ___', 'Albania'
UNION SELECT '__1______ ... ___', 'Algeria'
...
UNION SELECT '________ ... _1_', 'Zambia'
UNION SELECT '________ ... __1', 'Zimbabwe'
CREATE TABLE new_table
(country_id int,
species_id int)
INSERT new_table
SELECT species.id, country_id
FROM species s ,
#tmp t
WHERE isnull( s.Afghanistan, ' ' ) +
isnull( s.Albania, ' ' ) +
... +
isnull( s.Zambia, ' ' ) +
isnull( s.Zimbabwe, ' ' ) like t.key
我的建议
就个人而言，我不会这样做。除了要对国家/地区ID进行硬编码之外，我会像我们提到的那样做一个快速而又肮脏的解决方案(因为我们只需要这样做一次，对吧？创建完国家/地区表，因此我们知道所有ID是什么)：
INSERT new_table SELECT Species.ID, 1 FROM Species WHERE Species.Afghanistan = 1 INSERT new_table SELECT Species.ID, 2 FROM Species WHERE Species.Albania= 1 ... INSERT new_table SELECT Species.ID, 999 FROM Species WHERE Species.Zambia= 1 INSERT new_table SELECT Species.ID, 1000 FROM Species WHERE Species.Zimbabwe= 1
回答
当我遇到类似的问题时，我发现生成一个生成SQL脚本的脚本很方便。这是我们提供的示例，被抽象为使用％PAR1％代替阿富汗。
SELECT Species.ID, Country.CountryID FROM Country, Species WHERE (((Species.%PAR1%)=1)) AND (((Country.Country)="%PAR1%")) UNION
还添加了关键字联合作为组合所有选择的一种方式。
接下来，我们需要一个根据现有数据生成的国家/地区列表：
阿富汗
阿尔巴尼亚
。
，
。
接下来，我们需要一个可以遍历国家/地区列表的脚本，对于每次迭代，
产生的输出在第一次迭代中用阿富汗代替％PAR1％，在第二次迭代中用阿尔巴尼亚代替。该算法就像文字处理器中的邮件合并一样。编写此脚本需要一些工作。但是，一旦有了它，就可以在许多像这样的一次性项目中使用它。
最后，我们需要手动将最后一个" UNION"改回分号。
如果我们可以使Access执行此巨型联合，则可以所需的形式获取所需的数据，并将其插入到新表中。
