如何通过 SQL Server 上的查询获取唯一行、所有列和最小最大日期时间 [英] How to get unique row, all column and min max datetime by query on SQL Server
问题描述
在 Microsoft SQL Server 中查询
Query in Microsoft SQL Server
select *
from TIMEDATA1
order by [Production order] asc
我得到这样的数据:
╔══════════╤═════════════╤══════════╤═══════╤═════════════╤════════════╤══════════════════╤══════════════════════╤══════════╤═════════════╤═════╤════════════╤════════════╤════════════╤══════════╗
║ RecordID │ ID Employee │ Resource │ Shift │ ProjectID │ Drawing No │ Production order │ PN │ Quantity │ ProductName │ BNo │ Start Date │ Start Time │ End Date │ End Time ║
╠══════════╪═════════════╪══════════╪═══════╪═════════════╪════════════╪══════════════════╪══════════════════════╪══════════╪═════════════╪═════╪════════════╪════════════╪════════════╪══════════╣
║ 60431 │ 2088 │ M-JO │ HC │ E195256-A01 │ 1A │ MA-000000001 │ SHAFT-DBT-999M │ 1 │ NULL │ B01 │ 2020-10-05 │ 13:23:27 │ NULL │ NULL ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 8354 │ 2029 │ M-JO │ HC │ E183127-A01 │ 2A │ MA-000001FAB │ VY1200-DISE-700F1 │ 7 │ NULL │ B01 │ 2019-09-23 │ 09:41:48 │ 2019-09-23 │ 14:38:18 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 8408 │ 2058 │ M-MD2.5 │ 1 │ E183127-A02 │ 3A │ MA-000001FAB │ VY1200-DISE-700F2 │ 7 │ NULL │ B01 │ 2019-09-23 │ 15:32:53 │ 2019-09-23 │ 16:51:19 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 50130 │ 2175 │ M-ML1.5 │ HC │ L190004-A01 │ 4A │ MA-000001PHA │ L190004-A01-051-023C │ 2 │ NULL │ B01 │ 2020-05-19 │ 15:59:23 │ 2020-05-19 │ 18:06:14 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 50231 │ 2175 │ M-ML1.5 │ HC │ L190004-A02 │ 4B │ MA-000001PHA │ L190004-A01-051-023C │ 2 │ NULL │ B01 │ 2020-05-20 │ 08:04:39 │ 2020-05-20 │ 16:53:53 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 50874 │ 2134 │ M-ML2 │ HC │ L190004-A07 │ 5C │ MA-000002PHA │ L190004-A01-005-023C │ 2 │ NULL │ B01 │ 2020-05-22 │ 10:11:08 │ 2020-05-22 │ 16:39:43 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 51030 │ 2134 │ M-ML2 │ HC │ L190004-A08 │ 5C │ MA-000002PHA │ L190004-A01-005-023C │ 2 │ NULL │ B01 │ 2020-05-23 │ 08:06:43 │ 2020-05-23 │ 11:38:03 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 52063 │ 2134 │ M-ML2 │ HC │ E203089-A05 │ 2AW │ MA-000003PHA │ E203089-A01-005-023C │ 1 │ NULL │ B01 │ 2020-05-28 │ 13:23:48 │ 2020-05-28 │ 18:29:19 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 52204 │ 2134 │ M-ML2 │ HC │ E203089-A01 │ 1A │ MA-000003PHA │ E203089-A01-005-023C │ 1 │ NULL │ B01 │ 2020-05-29 │ 08:05:22 │ 2020-05-29 │ 12:51:25 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 8051 │ 2163 │ M-MLV1.6 │ 3 │ E183116-A03 │ Q │ MA-000005198 │ VY0750031-SUCB-819M1 │ 1 │ NULL │ B01 │ 2019-09-21 │ 02:30:14 │ 2019-09-21 │ 06:00:00 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 8052 │ 2028 │ M-MLV1.6 │ 1 │ E183116-A02 │ P │ MA-000005198 │ VY0750031-SUCB-819M │ 1 │ NULL │ B01 │ 2019-09-21 │ 08:10:59 │ 2019-09-21 │ 10:00:00 ║
╟──────────┼─────────────┼──────────┼───────┼─────────────┼────────────┼──────────────────┼──────────────────────┼──────────┼─────────────┼─────┼────────────┼────────────┼────────────┼──────────╢
║ 8100 │ 2029 │ M-JO │ 2 │ E183116-A03 │ X │ MA-000005198 │ VY0750031-SUCB-819M │ 1 │ NULL │ B01 │ 2019-09-21 │ 14:05:12 │ 2019-09-21 │ 15:36:38 ║
╚══════════╧═════════════╧══════════╧═══════╧═════════════╧════════════╧══════════════════╧══════════════════════╧══════════╧═════════════╧═════╧════════════╧════════════╧════════════╧══════════╝
我试过查询
SELECT [Production order], PN, ProjectID,[ Drawing No],
MIN( CAST([Start Date] AS DATETIME) + CAST([Start Time] AS DATETIME)) AS MIN_DATE_TIME,
MAX(CAST([End Date] AS DATETIME) + CAST([End Time] AS DATETIME)) AS MAX_DATE_TIME
FROM TIMEDATA1
WHERE RESOURCE not like 'I-%'
GROUP BY [Production order], PN, ProjectID,[ Drawing No]
ORDER BY [Production order] ASC;
但是它会为每个生产订单返回多行.我希望查询表为
However It return multiple rows per production order. I expect to query to have table as
╔══════════════════╤══════════════════════╤═════════════╤════════════╤═════════════════════════╤═════════════════════════╗
║ Production order │ PN │ ProjectID │ Drawing No │ MIN_DATE_TIME │ MAX_DATE_TIME ║
╠══════════════════╪══════════════════════╪═════════════╪════════════╪═════════════════════════╪═════════════════════════╣
║ MA-000000001 │ SHAFT-DBT-999M │ E195256-A01 │ 1A │ 2020-10-05 13:23:27.000 │ NULL ║
╟──────────────────┼──────────────────────┼─────────────┼────────────┼─────────────────────────┼─────────────────────────╢
║ MA-000001FAB │ VY1200-DISE-700F1 │ E183127-A01 │ 2A │ 2019-09-23 09:41:48.000 │ 2019-09-23 16:51:19.000 ║
╟──────────────────┼──────────────────────┼─────────────┼────────────┼─────────────────────────┼─────────────────────────╢
║ MA-000001PHA │ L190004-A01-051-023C │ L190004-A01 │ 4A │ 2020-05-19 15:59:23.000 │ 2020-05-20 16:53:53.000 ║
╟──────────────────┼──────────────────────┼─────────────┼────────────┼─────────────────────────┼─────────────────────────╢
║ MA-000002PHA │ L190004-A01-005-023C │ L190004-A07 │ 5C │ 2020-05-22 10:11:08.000 │ 2020-05-23 11:38:03.000 ║
╟──────────────────┼──────────────────────┼─────────────┼────────────┼─────────────────────────┼─────────────────────────╢
║ MA-000003PHA │ E203089-A01-005-023C │ E203089-A01 │ 2AW │ 2020-05-28 13:23:48.000 │ 2020-05-29 12:51:25.000 ║
╟──────────────────┼──────────────────────┼─────────────┼────────────┼─────────────────────────┼─────────────────────────╢
║ MA-000005198 │ VY0750031-SUCB-819M1 │ E183116-A03 │ Q │ 2019-09-21 02:30:14.000 │ 2019-10-21 15:36:38.000 ║
╚══════════════════╧══════════════════════╧═════════════╧════════════╧═════════════════════════╧═════════════════════════╝
注意:Production order 应该是唯一值.PN, ProjectID, Drawing No 也应该是唯一值.可以从第一行或任何一行获取.
Note: Production order should be unique value. PN, ProjectID, Drawing No should be unique value also .That can get from first row or any.
MIN_DATE_TIME 是 MIN( CAST([Start Date] AS DATETIME) + CAST([Start Time] AS DATETIME))
MAX_DATE_TIME 是 MAX(CAST([End Date] AS DATETIME) + CAST([End Time] AS DATETIME))
我从 通过MSSQL查询SQL以获取所有列和最小最大日期时间
我尝试使用答案的结果，但是@Gordon Linoff 提到这会为每个生产订单返回多行，这是因为值不同请帮我.谢谢！
I try with result of answer however @Gordon Linoff mentioned this returns multiple rows per production order, that is because the values are different Please help me. Thank you !
编辑
数据类型
开始日期:日期
结束日期:日期
开始时间:time(0)
Start Time: time(0)
结束时间:time(0)
End Time: time(0)
我知道这份报告的报告设计很差.但我是为了其他目的而制作的
I know this report is poor report design. But I make it for other purpose
推荐答案
我需要知道日期和时间列的数据类型，才能知道完成最后两列的最佳方式.既然这不是问题，这里是其余的，足以让你开始:
I need to know the data type for your date and time columns to know the best way to finish the last two columns. Since that's not in the question, here's the rest of it, which is enough to get you started:
select [Production order]
, MIN(PN) as PN
, MIN(ProjectID) as ProjectID
, MIN([Drawing No]) as [Drawing No]
from TIMEDATA1
group by [Production order]
order by [Production order] asc
我也注意到了这一点:
I also noticed this:
PN、ProjectID、Drawing No 也应该是唯一值.可以从第一行或任何一行获取.
PN, ProjectID, Drawing No should be unique value also .That can get from first row or any.
这也是一个常见的要求，但它非常糟糕的报告设计，因为很容易最终显示出没有意义的数据.
This is also a common requirement, but it's really poor report design, because it's very easy to end up showing data that doesn't make sense.
例如，查看示例结果数据，我们看到生产订单、图纸和结束时间的这些值:
For example, looking at the sample result data we see these values for production order, drawing, and end time:
MA-000001FAB, 2A, 2019-09-23 16:51:19.000但是，查看绘制 2A 的原始数据，我们可以看到此结束时间与该绘图无关.换句话说，这些数据并排显示没有任何意义.你的工作是了解这一点，要么反对这种要求，要么了解如何扩展它以显示正确的行.
However, looking at the original data for drawing 2A we can see this end time is not associated with that drawing. In other words, this is data that doesn't make any sense to show side-by-side. It's your job to know this and either push back against that kind of requirement or understand how to expand it to show the correct row.
如果你必须满足这个糟糕的要求，最好这样做:
If you must settle for this poor requirement, it's better to do something like this:
select *
from (
select [Production order], PN, ProjectID, [Drawing No]
, row_number() over (partition by [production order] order by [production order], end_date, end_time) as rn
from TIMEDATA1
) t1
where rn = 1
order by [production order]
这样，至少所有的随机值都来自同一行，并且是与最小结束日期和时间匹配的行.
This way, at least all of the random values will come from the same row, and it will be the row that matches the min end date and time.
这篇关于如何通过 SQL Server 上的查询获取唯一行、所有列和最小最大日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
