英文:
SELECT TOP (1) from DISTINCT values
问题
我有来自机器PLC的数据，并将其收集到SQL数据库中。
我有几台机器，"machineID" 分别为 19、21、24、25，等等。
我有数千条来自这些机器的日志，包含实时操作数据。
我需要编写查询以获取每台机器的最后一条记录。
我想执行类似于以下的操作：
SELECT DISTINCT machineID
FROM [moh01dtd].[dbo].[MasterPLC_Data]
ORDER BY machineID;
这会给我返回表中所有的机器ID。
现在我想找到每台机器写入表中的最后一行记录。类似于以下的操作：
SELECT TOP(1) *
FROM [moh01dtd].[dbo].[MasterPLC_Data]
WHERE machineID IN (SELECT DISTINCT machineID
FROM [moh01dtd].[dbo].[MasterPLC_Data]);
但如预期的那样，它并不起作用。基本上我需要执行：
SELECT TOP(1) *
FROM [moh01dtd].[dbo].[MasterPLC_Data]
WHERE machineID = <parameter>
和 <parameter>
是来自以下查询的行：
SELECT DISTINCT machineID
FROM [moh01dtd].[dbo].[MasterPLC_Data]
ORDER BY machineID;
英文:
I have data from machine PLCs and I am gathering that into SQL database.
I have several machines with "machineID" = 19, 21, 24, 25, .. .etc.
I have thousands of logs from these machines with actual realtime operating data.
I need to write the query to get the last query from each machine.
What I want to perform is something like this:
SELECT DISTINCT machineID
FROM [moh01dtd].[dbo].[MasterPLC_Data]
ORDER BY machineID;
This gives me the results of all machine IDs that are in the table.
Now I want to find the last row for each machine that was written to the table. So something like this:
SELECT TOP(1) *
FROM [moh01dtd].[dbo].[MasterPLC_Data]
WHERE machineID IN (SELECT DISTINCT machineID
FROM [moh01dtd].[dbo].[MasterPLC_Data]);
But as expected it is not working. Basically I would need to perform
SELECT TOP(1) *
FROM [moh01dtd].[dbo].[MasterPLC_Data]
WHERE machineID = <parameter>
and <parameter>
is the rows from the
SELECT DISTINCT machineID
FROM [moh01dtd].[dbo].[MasterPLC_Data]
ORDER BY machineID;
答案1
得分: 1
首先，你需要定义最后一个查询的含义。表中是否有一个 IDENTITY 列，或者是一个时间戳或其他什么东西。
假设你有一个 IDENTITY 列，可以这样做：
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY machineID ORDER BY id DESC) AS rn
FROM moh01dtd.dbo.MasterPLC_Data
) AS q
WHERE rn=1
英文:
First you have to define what the last query means. Do you have an IDENTITY column in the table or a timestamp or something else.
Assuming you have an IDENTITY column, you can do:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY machineID ORDER BY id DESC) AS rn
FROM moh01dtd.dbo.MasterPLC_Data
) AS q
WHERE rn=1
答案2
得分: 0
你可以从按machineID分组后获取最后一个值
示例:
with MasterPLC_Data as(
select id=1, machineID = 1, field1='1', field2='1'
union all select id=2, machineID = 1, field1='2', field2='2'
union all select id=3, machineID = 1, field1='3', field2='3'
union all select id=4, machineID = 2, field1='4', field2='4'
union all select id=5, machineID = 2, field1='5', field2='5'
union all select id=6, machineID = 3, field1='4', field2='4'
union all select id=7, machineID = 3, field1='5', field2='5'
union all select id=8, machineID = 3, field1='6', field2='6'
)
SELECT m.*
FROM MasterPLC_Data m
join (select machineID, max_id = max(id)
from MasterPLC_Data
group by machineID
) t on m.id = t.max_id
结果
| id | machineID | field1 | field2 |
|---|---|---|---|
| 3 | 1 | 3 | 3 |
| 5 | 2 | 5 | 5 |
| 8 | 3 | 6 | 6 |
英文:
You can get last value from group by machineID
Example:
with MasterPLC_Data as(
select id=1,machineID = 1,field1='1',field2='1'
union all select id=2,machineID = 1,field1='2',field2='2'
union all select id=3,machineID = 1,field1='3',field2='3'
union all select id=4,machineID = 2,field1='4',field2='4'
union all select id=5,machineID = 2,field1='5',field2='5'
union all select id=6,machineID = 3,field1='4',field2='4'
union all select id=7,machineID = 3,field1='5',field2='5'
union all select id=8,machineID = 3,field1='6',field2='6'
)
SELECT m.*
FROM MasterPLC_Data m
join (select machineID,max_id = max(id)
from MasterPLC_Data
group by machineID
) t on m.id = t.max_id
result
| id | machineID | field1 | field2 |
|---|---|---|---|
| 3 | 1 | 3 | 3 |
| 5 | 2 | 5 | 5 |
| 8 | 3 | 6 | 6 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论