我有一个SQL表，我想从Number的值更新Location / Description的值。
例如：表中的第二行应该具有Loaction / Descritption作为“Freezer Twnety”。 同样明智的是，最后两行的位置/描述应为“Freezer Thirty”“。
NUMBER列是一个ID，不知何故该列由位置/描述值填充。 我想将它们更新回正确的位置，并将包含“Freezer％”的所有行删除为NUMBER。
╔══════════╦════════════════╦════════╦═════════════╗ ║ Location ║ Number ║ Tissue ║ Description ║ ╠══════════╬════════════════╬════════╬═════════════╣ ║ NULL ║ FREEZER TWENTY ║ NULL ║ NULL ║ ║ NULL ║ BRC13091917 ║ 13828 ║ NULL ║ ║ NULL ║ Freezer Three ║ NULL ║ NULL ║ ║ NULL ║ BRC13091922 ║ 13867 ║ NULL ║ ║ NULL ║ FREEZER THIRTY ║ NULL ║ NULL ║ ║ NULL ║ BRC13112118 ║ 14815 ║ NULL ║ ║ NULL ║ BRC13112118 ║ 14816 ║ NULL ║ ╚══════════╩════════════════╩════════╩═════════════╝I have a SQL table where I want to update the value of Location/Description from the value of Number.
For eg: The 2nd row in the table should have a Loaction/Descritption as "Freezer Twnety". Like wise the last 2 rows should have Location/Description as "Freezer Thirty"".
The NUMBER column is an ID and somehow that column got populated by Location/Description values. I want to update back them to the right place and delete all the rows that just contain the "Freezer%" as NUMBER.
╔══════════╦════════════════╦════════╦═════════════╗ ║ Location ║ Number ║ Tissue ║ Description ║ ╠══════════╬════════════════╬════════╬═════════════╣ ║ NULL ║ FREEZER TWENTY ║ NULL ║ NULL ║ ║ NULL ║ BRC13091917 ║ 13828 ║ NULL ║ ║ NULL ║ Freezer Three ║ NULL ║ NULL ║ ║ NULL ║ BRC13091922 ║ 13867 ║ NULL ║ ║ NULL ║ FREEZER THIRTY ║ NULL ║ NULL ║ ║ NULL ║ BRC13112118 ║ 14815 ║ NULL ║ ║ NULL ║ BRC13112118 ║ 14816 ║ NULL ║ ╚══════════╩════════════════╩════════╩═════════════╝最满意答案
我想出了一些更好的东西。 我用JavaScript构建它并将其放在jsfiddle中 。 只需将您的电子表格另存为CSV文件即可; 作为分隔符，复制顶部<div>并运行它。 您可以将输出复制回文本文件，另存为CSV并在Excel中打开。
使用编程语言解决这个问题要比在SQL中解决它容易得多。 如果您有电子表格，则可以将其转换为CSV以便于阅读。 然后，您可以填写空白并删除错误的行。 这是Perl的解决方案。
#!/usr/bin/perl use strictures; use Data::Dump; use 5.012; my $location; while (my $row = <DATA>) { chomp $row; my @fields = split /;/, $row; if ($fields[1] =~ m/^FREEZER/i) { $location = $fields[1]; next; } say join ';', $location, map { $_ // '' } @fields[1, 2, 3]; } __DATA__ ;FREEZER TWENTY;; ;BRC13091917;13828; ;Freezer Three;; ;BRC13091922;13867; ;FREEZER THIRTY;; ;BRC13112118;14815; ;BRC13112118;14816;输出如下：
FREEZER TWENTY;BRC13091917;13828; Freezer Three;BRC13091922;13867; FREEZER THIRTY;BRC13112118;14815; FREEZER THIRTY;BRC13112118;14816;由于你最有可能在Windows上，你自己没有得出这个结论，你可能不知道如何处理这个问题。 但是，该算法在任何编程语言中都是相同的。 您可以在Excel中的VBA或PHP或其他任何内容中执行此操作。
逐行读取文件 将该行拆分为一个数组 检查第二列中是否有位置 如是： 将其保存在移动到下一行时保持不变的变量中 继续下一行 如果不： 用保存的位置替换空的第1列 将该行写出到新文件I've come up with something a lot better. I built it in JavaScript and put it in a jsfiddle. Just save your spreadsheet as a CSV file with ; as delimiter, copy everything in the top <div> and run it. You can copy the output back into a text file, save as CSV and open in Excel.
It is a lot easier to solve this with a programming language than to do it in SQL. If you have a spreadsheet, you can turn it into CSV to make it easier to read. You can then fill in the blanks and remove the wrong lines. Here's a solution in Perl.
#!/usr/bin/perl use strictures; use Data::Dump; use 5.012; my $location; while (my $row = <DATA>) { chomp $row; my @fields = split /;/, $row; if ($fields[1] =~ m/^FREEZER/i) { $location = $fields[1]; next; } say join ';', $location, map { $_ // '' } @fields[1, 2, 3]; } __DATA__ ;FREEZER TWENTY;; ;BRC13091917;13828; ;Freezer Three;; ;BRC13091922;13867; ;FREEZER THIRTY;; ;BRC13112118;14815; ;BRC13112118;14816;The output looks like this:
FREEZER TWENTY;BRC13091917;13828; Freezer Three;BRC13091922;13867; FREEZER THIRTY;BRC13112118;14815; FREEZER THIRTY;BRC13112118;14816;Since you are most likely on Windows you did not come to this conclusion yourself you will probably not know what to do with this. The algorithm is the same in any programming language, though. You could do it in VBA in Excel, or in PHP, or anything else.
read the file line by line split the line into an array check if there is a location in the 2nd column if yes: save that in a variable that stays intact when moving to the next line proceed to next line if no: replace the empty 1st column with the saved location write out the line to a new file更多推荐
发布评论