我有一个复杂的SQL查询,它返回正确的值,但格式错误。下面的代码查看来自同一个表的三个单独的列,检查它们是否为空,然后将它们链接在一起。问题是它们都落在我检查的第一列之后的一个标题列中,即列'A‘。我想知道如何解决这个格式化问题。这基本上就是SQL将一列转置为一行的过程。
我做了一些实验,以实际获得正确的值,并确认它是正常工作的。我唯一的问题是正确地格式化我的SQL。
SELECT `A` FROM sensor, login_users, relation
WHERE (login_users.id = relation.owner_id
AND relation.dev_id = sensor.Device_ID)
AND `A` IS NOT NULL
UNION
SELECT `B` FROM sensor, login_users, relation
WHERE (login_users.id = relation.owner_id
AND relation.dev_id = sensor.Device_ID)
AND `B` IS NOT NULL
UNION
SELECT `C` FROM sensor, login_users, relation
WHERE (login_users.id = relation.owner_id
AND relation.dev_id = sensor.Device_ID)
AND `C` IS NOT NULL
这就是SQL查询返回的内容
A <-- column name
--------------
2.54 <-- A value
15.00 <-- B value
3.45 <-- C value
This is what I need it to return.
A B C
---- ----- -----
2.54 15.00 3.45
有没有很棒的SQL用户可以在这个格式化问题上提供任何帮助?我通常对SQL很在行,但这一点把我难住了。
发布于 2019-05-21 03:41:36
看起来你只需要旋转一下。我在这里做了很多假设,因为这里没有列出dbms或数据集。
with dat
as(
SELECT `A` as col, 'a' as spot FROM sensor, login_users, relation
WHERE (login_users.id = relation.owner_id
AND relation.dev_id = sensor.Device_ID)
AND `A` IS NOT NULL
UNION
SELECT `B` as col, 'b' as spot FROM sensor, login_users, relation
WHERE (login_users.id = relation.owner_id
AND relation.dev_id = sensor.Device_ID)
AND `B` IS NOT NULL
UNION
SELECT `C` as col,'c' as spot FROM sensor, login_users, relation
WHERE (login_users.id = relation.owner_id
AND relation.dev_id = sensor.Device_ID)
AND `C` IS NOT NULL
)
select
case when col = 'a' then `A` end as A
case when col = 'b' then `B` end as B
case when col = 'c' then `C` end as C
from
dat
https://stackoverflow.com/questions/56227078
复制相似问题