如何将列值转换为 Sqlite 中的行? [英] How to convert column values into rows in Sqlite?

问题描述

我在 sqlite 中有一个表值，例如..

I have a table value in sqlite like..

我在这样的 sqlite 查询中获得了价值..

i'm getting value in sqlite query like this..

select * from tablename where id='101' and id='102' and id='1' and id='18'

101 Local Local Local   Local   Local   Local
102 9               12      9       12      9
  1 3:55    4:20    4:40    5:00    5:20    5:40
 18 4:50    5:15    5:35    5:55    6:15    6:35

上面的值我需要在 sqlite 查询中转换，如下所示..

Above value I need to convert in sqlite query like below..

Local       9       3:55    4:50
Local       12      4:20    5:15
Local       9       4:40    5:35
Local       12      5:00    5:55
Local       9       5:20    6:15
Local       9       5:40    6:35

推荐答案

哦，所以你被一个糟糕的数据库设计困住了，你不能改变它......这很糟糕.它应该被分成4个表(至少).无论如何，在这种特殊情况下，这是一种方法.

Oh, so you're stuck with a terrible DB design which you aren't allowed to change... that sucks. It ought to be divided into 4 tables (at least). Anyway, here is one way to do it in this particular case.

我假设第一个查询等价于:

I assume the first query is equivalent to this:

SELECT id, F1, F2, F3, F4, F5, F6
FROM tablename
WHERE id IN (101,102,1,18)

因此，您可以像这样进行移调/旋转/实际调用的任何内容:

So, you can do transposing/pivoting/whatever it's actually called like this:

SELECT 
  MAX(CASE WHEN id=101 THEN F1 END) R1,
  MAX(CASE WHEN id=102 THEN F1 END) R2,
  MAX(CASE WHEN id=1 THEN F1 END) R3,
  MAX(CASE WHEN id=18 THEN F1 END) R4
FROM tablename
UNION
SELECT 
  MAX(CASE WHEN id=101 THEN F2 END) R1,
  MAX(CASE WHEN id=102 THEN F2 END) R2,
  MAX(CASE WHEN id=1 THEN F2 END) R3,
  MAX(CASE WHEN id=18 THEN F2 END) R4
FROM tablename
UNION
SELECT 
  MAX(CASE WHEN id=101 THEN F3 END) R1,
  MAX(CASE WHEN id=102 THEN F3 END) R2,
  MAX(CASE WHEN id=1 THEN F3 END) R3,
  MAX(CASE WHEN id=18 THEN F3 END) R4
FROM tablename
UNION
SELECT 
  MAX(CASE WHEN id=101 THEN F4 END) R1,
  MAX(CASE WHEN id=102 THEN F4 END) R2,
  MAX(CASE WHEN id=1 THEN F4 END) R3,
  MAX(CASE WHEN id=18 THEN F4 END) R4
FROM tablename
UNION
SELECT 
  MAX(CASE WHEN id=101 THEN F5 END) R1,
  MAX(CASE WHEN id=102 THEN F5 END) R2,
  MAX(CASE WHEN id=1 THEN F5 END) R3,
  MAX(CASE WHEN id=18 THEN F5 END) R4
FROM tablename
UNION
SELECT 
  MAX(CASE WHEN id=101 THEN F6 END) R1,
  MAX(CASE WHEN id=102 THEN F6 END) R2,
  MAX(CASE WHEN id=1 THEN F6 END) R3,
  MAX(CASE WHEN id=18 THEN F6 END) R4
FROM tablename

不幸的是，它的排序方式很奇怪.

Unfortunately, it's sorted in a strange way.

我真的建议你说服负责人重新设计数据库.一些设计者认为拥有这种结构的表是灵活的"和提高性能"，但事实并非如此:使用此类表进行实际工作需要非常重要的查询，而大多数 DBMS无法优化 因为他们从来没有想到会处理这样的暴行.

And I really advise you to persuade someone in charge to re-design the DB. Some designers think that having tables with such structure is "flexible" and "improves performance", but it's not so: doing actual work with such tables requires highly non-trivial queries, which most of DBMSs cannot optimize because they were never expected to deal with such atrocities.

这样的表也打破了关系模型的想法.每个表必须有一个语句模板"，例如，CREATE TABLE S (id INTEGER, name TEXT, address TEXT) 可以有模板具有 ID ID 的供应商称为 NAME，位于地址".现在，当您将其内容放入模板时，该表中的每一行都会为您一个关于世界的真实陈述:如果 S 将包含一行 (1,"Bob & Co.", "Lime St., 125")，然后它会告诉您ID 为 1 的供应商名为 Bob & Co.，位于 Lime St., 125".

Such tables also break the very idea of the relational model. Each table must have one-statement "template", for example, CREATE TABLE S (id INTEGER, name TEXT, address TEXT) can have template "A supplier with id ID is called NAME and is located at ADDRESS". Now, each row in this table yields you a true statement about the world when you put its contents into the template: if S would contain a row (1, "Bob & Co.", "Lime St., 125"), then it would tell you that "A supplier with id 1 is called Bob & Co. and is located at Lime St., 125".

所有关系操作:选择、投影、连接、过滤等，不只是以特定方式组合表，不！它们还结合了模板"，因此您可以说出查询给您的确切信息.反之亦然，如果你能通过组合这些模板"来表达你想要的信息，它几乎会自动给你一个相应的查询.

All relational operations: selection, projection, joins, filtering, etc. don't just combine tables in a particular way, no! They also combine "templates", so you can say what exactly information a query gives you. And vice versa, if you can express what information you want through combining those "templates", it would almost automatically give you a corresponding query.

并且您必须使用的表格可以与没有理智的模板"相关联.这就是为什么您必须编写无意义的查询才能获得有用的东西.请注意，我基本上可以猜测结果集的模板"，

And the table you have to work with can be associated with no sane "template". That's why you have to write nonsensical queries in order to get something useful. Note that I can basically guess the "template" of the resulting set,

R1      R2   R3     R4
============================
Local   9   3:55   4:50
...

类似于R2 类型的事件发生在 R1 地点，从时间 R3 开始，到时间 R4 结束".我说的对吗?

It's something like "An event of type R2 was happening at place R1, starting at time R3 and ending at time R4". Am I correct?

这篇关于如何将列值转换为 Sqlite 中的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！