修改MySQL表的显示

发布于 2025-02-06 16:47:01 字数 1310 浏览 2 评论 0 原文

Hi i have a mysql table like this:

TagName	DateTime	Value
'SGHAST_0001_Temp'	'2022-06-03 15:53:18.2400000'	'7.0'
'SGHAST_0001_HUMIDITY'	'2022-06-03 15:53:18.2400000'	'80.0'
'SGHAST_0002_Temp '	'2022-06-03 15：53：18.2400000''17	.0''sghast_0002_humisity''2022-06-03
如何将桌子分为下面的	15：53：53：18.2400000''50	.0 .0'

一行，

tagName	温度	湿度
sghast.0001'7.0''80	'17	.0'sghast.0002
.0	.0''50	'

我还能有代码行更改为此而不是建议吗？因为我是MySQL的新手。但是，任何帮助仍然值得赞赏！

到目前为止，我的代码：

SELECT DateTime, 
       SUM(CASE WHEN skynet_msa.testingintern.TagName LIKE 'Temp%' 
                THEN value 
                ELSE 0 END) as Temperature,
       SUM(CASE WHEN skynet_msa.testingintern.TagName LIKE 'HUMIDITY%' 
                THEN value 
                ELSE 0 END) as Humidity,
FROM skynet_msa.testingintern
GROUP BY DateTime 
LIMIT 0, 50000;

原文

Hi i have a mysql table like this:

TagName	DateTime	Value
'SGHAST_0001_Temp'	'2022-06-03 15:53:18.2400000'	'7.0'
'SGHAST_0001_HUMIDITY'	'2022-06-03 15:53:18.2400000'	'80.0'
'SGHAST_0002_Temp'	'2022-06-03 15:53:18.2400000'	'17.0'
'SGHAST_0002_HUMIDITY'	'2022-06-03 15:53:18.2400000'	'50.0'

How to make the table into one row(s) like below here?

TagName	Temperature	Humidity
SGHAST.0001	'7.0'	'80.0'
SGHAST.0002	'17.0'	'50.0'

Can I please also have the lines of code to change to this instead of just a suggestion? because I am new to MySQL. But still any help is appreciated!

My code so far:

SELECT DateTime, 
       SUM(CASE WHEN skynet_msa.testingintern.TagName LIKE 'Temp%' 
                THEN value 
                ELSE 0 END) as Temperature,
       SUM(CASE WHEN skynet_msa.testingintern.TagName LIKE 'HUMIDITY%' 
                THEN value 
                ELSE 0 END) as Humidity,
FROM skynet_msa.testingintern
GROUP BY DateTime 
LIMIT 0, 50000;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一念一轮回 2025-02-13 16:47:01

查询的主要问题是您要在“ dateTime ”字段上分组，该字段与每一行相等，并且无法识别所需的组。确定该组的内容实际上是“ tagName ”字段和“ datetime ”的第一部分的组合。

由于我们不能直接使用“ tagName ”，因此我们可以提取有趣的部分（例如'sghast_000＆lt; n＆gt;'来自'sghast_000＆lt; n＆gt; fieldName＆gt;'）使用mysql substring_index 函数。当您传递字符（在我们的情况下 _ ）和索引i（在我们的情况下 2 ）时，它将在第一个字符和ITH之间检索字符串中的所有内容字符（在我们的情况下，'sghast_000＆lt; n＆gt;'）。

一旦我们能够建立我们的组，我们就可以在子句中使用组中，并使用mysql max 聚集功能以提取温度和湿度值时，仅当这些功能才出现在“ > tagName “字段。

SELECT SUBSTRING_INDEX(TagName, '_', 2) AS TagName,
       MAX(CASE WHEN TagName LIKE '%Temp'     
                THEN Value END        ) AS Temperature,
       MAX(CASE WHEN TagName LIKE '%HUMIDITY' 
                THEN Value END        ) AS Humidity
FROM testingintern
GROUP BY SUBSTRING_INDEX(TagName, '_', 2), 
         Datetime

您可以在此 link 。

The main issue of your query is that you're grouping on the "DateTime" field, which is equal for every row and does not identify the group you want. What identifies the group is really the combination of the first part of the "TagName" field and the "Datetime" together.

Since we can't use "TagName" directly, we can extract the interesting part (e.g. 'SGHAST_000<n>' from 'SGHAST_000<n>_<fieldname>') using MySQL SUBSTRING_INDEX function. When you pass a character (in our case _) and an index i (in our case 2), it will retrieve everything in the string between the first character and the ith character (in our case 'SGHAST_000<n>').

Once we are able to build our group, we can use that inside a GROUP BY clause, and use MySQL MAX aggregation function to extract the values of temperature and humidity when and only when these occur inside the "TagName" field.

SELECT SUBSTRING_INDEX(TagName, '_', 2) AS TagName,
       MAX(CASE WHEN TagName LIKE '%Temp'     
                THEN Value END        ) AS Temperature,
       MAX(CASE WHEN TagName LIKE '%HUMIDITY' 
                THEN Value END        ) AS Humidity
FROM testingintern
GROUP BY SUBSTRING_INDEX(TagName, '_', 2), 
         Datetime

You can play with it (and select what specific function returns) at this link.

回复收藏 0 原文

~没有更多了~