SQL |如何在SQL表中将单个日期列拼接成两列
SQL |如何在SQL表中将单个日期列拼接成两列
提问于 2018-06-10 01:30:50
感谢你关注我的问题，我需要一些建议如下：
- 如何在TSQL
- 中将单个日期列拼接成两列在TSQL
- 中我有SQL表，它是一家酒店的CheckInDate问题此记录中包含记录在同一列中但具有记录id的CheckOutDate，因此每个id在一列中将有两行记录。
- 我想基于两列(CheckInDate和CheckOutDate)拼接此日期，CheckOutDate将是第二列将是别名。
- 我希望根据其id查看所有记录。
F29
任何建议都将不胜感激。
原始表
这是我希望看到的结果，最后一列是date diff，DATEDIFF(d，Date，Date) AS DaysBooked
原始表字段
SELECT
[ID]
,([Date]) AS CheckInDate
FROM [DateOneRow]我尝试了下面的代码，但dd不起作用
SELECT
--I would like to have below to columns
([ID])
,MIN([Date]) AS CheckInDate
,MAX([DATE]) AS CheckOutDate
,DATEDIFF(d,[Date],[Date]) AS DaysBooked
FROM [DateOneRow]
GROUP BY [ID], ([Date])原始表1
由@shawnt00添加的DDL
create table DateOneRow (Id int not null, "Date" date not null);
insert into DateOneRow ("Date", Id) values
('20181201', 1), ('20181204', 1),
('20180201', 2), ('20180301', 2),
('20180301', 1), ('20180303', 1),
('20180402', 2), ('20180503', 2),
('20180120', 2), ('20180202', 2);回答 3
Stack Overflow用户
发布于 2018-06-10 02:38:13
尽管我不能容忍这种糟糕的数据库设计，但这里有一个查询，它将向您显示您所需要的内容。
用于SQL Server的：
create table [DateOneRow] (
[Id] int,
[Date] date
);
insert into [DateOneRow] ([Id], [Date]) values (1, '2018-01-01');
insert into [DateOneRow] ([Id], [Date]) values (1, '2018-01-03');
insert into [DateOneRow] ([Id], [Date]) values (2, '2018-02-15');
insert into [DateOneRow] ([Id], [Date]) values (2, '2018-02-16');
insert into [DateOneRow] ([Id], [Date]) values (3, '2018-03-20');
insert into [DateOneRow] ([Id], [Date]) values (4, '2018-04-25');
select
ci.id,
check_in,
check_out,
datediff(day, check_in, check_out) as days_booked
from (
select id, min(date) as check_in from dateonerow group by id
) ci
full join (
select id, max(date) as check_out from dateonerow group by id
) co on ci.id = co.id结果：
id check_in check_out days_booked
-- ---------- ---------- -----------
1 2018-01-01 2018-01-03 2
2 2018-02-15 2018-02-16 1
3 2018-03-20 2018-03-20 0
4 2018-04-25 2018-04-25 0MySQL的：
create table DateOneRow (
Id int,
Date date
);
insert into DateOneRow (Id, Date) values (1, '2018-01-01');
insert into DateOneRow (Id, Date) values (1, '2018-01-03');
insert into DateOneRow (Id, Date) values (2, '2018-02-15');
insert into DateOneRow (Id, Date) values (2, '2018-02-16');
insert into DateOneRow (Id, Date) values (3, '2018-03-20');
insert into DateOneRow (Id, Date) values (4, '2018-04-25');
select
ci.id,
ci.check_in,
co.check_out,
datediff(check_out, check_in) as days_booked
from (
select id, min(date) as check_in from DateOneRow group by id
) ci
join (
select id, max(date) as check_out from DateOneRow group by id
) co on ci.id = co.id;结果：
id check_in check_out days_booked
-- ---------- ---------- -----------
1 2018-01-01 2018-01-03 2
2 2018-02-15 2018-02-16 1
3 2018-03-20 2018-03-20 0
4 2018-04-25 2018-04-25 0Stack Overflow用户
发布于 2018-06-10 03:14:37
如果我们只有一个预订ID，那就相当容易了。但我们可以推导出[Id]的预订编号(我猜这就是预订的客户？)使用
ceiling(row_number() OVER (PARTITION BY [DateOneRow].[Id] ORDER BY [DateOneRow].[CheckInDate]) / convert(decimal, 2))row_number() OVER (PARTITION BY [DateOneRow].[Id] ORDER BY [DateOneRow].[CheckInDate])将对按[CheckInDate]排序的[Id]的行进行编号。每两行(按照[CheckInDate]定义的顺序)属于一个预订。因此，我们可以将数字除以2并对其执行ceiling()操作，以获得该行所属的预订编号。
有了这个，我们可以很容易地GROUP BY [Id]和预订号码，取最小日期为入住时间，最大日期为退房时间。在上面应用datediff()，你就完成了。
SELECT [X].[Id],
min([X].[CheckInDate]) [CheckInDate],
max([X].[CheckInDate]) [CheckOutDate],
datediff(d, min([X].[CheckInDate]), max([X].[CheckInDate])) [DaysBooked]
FROM (SELECT [DateOneRow].[Id],
[DateOneRow].[CheckInDate],
ceiling(row_number() OVER (PARTITION BY [DateOneRow].[Id]
ORDER BY [DateOneRow].[CheckInDate]) / convert(decimal, 2)) [Booking#]
FROM [DateOneRow]) [X]
GROUP BY [X].[Id],
[X].[Booking#];(如果日期包含在内，则可以考虑使用datediff(d, min([X].[CheckInDate]), max([X].[CheckInDate])) + 1 [DaysBooked] (请注意+ 1)。)
但是我强烈建议修改这个可怕的设计！
编辑：
如果[Id]实际上是一个预订ID，并且只对属于一个预订的两行是相同的，那么它将是一个使用min()和max()的简单GROUP BY。
SELECT [DateOneRow].[Id],
min([DateOneRow].[CheckInDate]) [CheckInDate],
max([DateOneRow].[CheckInDate]) [CheckOutDate],
datediff(d, min([DateOneRow].[CheckInDate]), max([DateOneRow].[CheckInDate])) [DaysBooked]
FROM [DateOneRow]
GROUP BY [DateOneRow].[Id];Stack Overflow用户
发布于 2018-06-10 03:44:43
如果您的签入与签出总是正确配对，那么下面这样的代码应该是有效的：
-- option 1
with data as (
select *, row_number() over (partition by Id order by "Date") - 1 as rn
from DateOneRow
)
select
d1."Date" as CheckInDate, d2."Date" as CheckOutDate,
datediff(day, d1."Date", d2."Date") as DaysBooked
from data d1 inner join data d2 on d2.Id = d1.Id and d2.rn = d1.rn + 1
where d1.rn % 2 = 0;
-- option 2
with data as (
select *, row_number() over (partition by Id order by "Date") - 1 as rn
from DateOneRow
)
select
d1."Date" as CheckInDate, d2."Date" as CheckOutDate,
datediff(day, d1."Date", d2."Date") as DaysBooked
from data d1 cross apply
(
select min("Date") as "Date" from data
where Id = d1.Id and "Date" > d1."Date"
) d2
where d1.rn % 2 = 0;我给你第二个选择，以防它更快。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50776858
复制相关文章
相似问题