如何将id的最后一个目的地作为最终目的地，其余目标是中间目的地？ [英] How do I take last destination of a id to be the final destination and the rest to be intermediate ？

问题描述

如何计算某个位置（邮政编码）是中间目的地的次数，并计算它是最终目的地？因为某些位置的目的地数量不同。

因此，当序列为1或2或3且序列为4时，如我的代码所示，计数是错误的吗？顺序1,2,3,4表示dest1,2,3,4



例如预订ID 1001

取件地点44456

目的地1 43367

目的地2 43876

Dest1将为中间

Dest 2将是最终的

例如预订编号2005

目的地1 56721

目的地2 53242

目的地3 45234

Dest1,2中级

目标3决赛



我的代码

选择l.zip_code，sum（dum.tab）来自location l，

（选择计数（Pickup_location）作为预订b的标签，位置l其中b.pickup_location = l.Zip_Code group by l.zip_Code

union select count（目的地）作为标签来自booking_destinations bd内部连接位置l on bd.destination = l.Zip_Code其中bd.sequence in（1,2,3）group by l.zip_Code

union select count（destination）作为来自booking_destinations bd的标签，位置l其中bd.destination = l.Zip_Code和sequence = 4 group by l.zip_Code

）作为dum group by l.zip_Code;



我尝试了什么：



在线搜索并删除序列= 4时的select语句

解决方案

您不想计算目的地，只需想知道哪个是第一个，哪个是最后一个。您可以通过在预订代码上使用GROUP BY并获取序列号的MIN和MAX来完成此操作。例如

 选择预订，max（ sequence ） as  final，min（ sequence ） as  [first] 
 来自 booking_destinations bd 
  group  按预订

然后，您可以使用子查询OR（我的偏好）将该子查询合并到整体结果中使用公用表表达式...



子查询示例：

  select  fl.booking，b.Pickup_location，bd。 sequence ，bd.destination，
  case  何时 bd。 sequence  = fl.final  then  ' 最终' 
   bd。序列 = fl。[first] 然后 ' 首先' 
  else  ' 临时' 
 结束 
 来自 
（ 选择预订，max（序列）  final，min（ sequence ） as  [first] 
 来自 booking_destinations bd 
  group   预订
）fl 
 加入预订b  ON  fl.booking = b.id 
  join  booking_destinations bd  on  b.id = bd.booking 
 加入位置l   bd.destination = l.zip_code 

CTE示例：

;   CTE  as  
（
 选择预订，max（序列） as  final，min（ sequence ） as  [first] 
 来自 booking_destinations bd 
  group   by 预订
）
 选择 CTE.booking，b.Pickup_location，bd。 sequence ，bd.destination，
  case  何时 bd。 sequence  = CTE.final 然后  最终' 
   bd。 sequence  = CTE.first 然后 '  First' 
  else  '  Interim' 
  end  
 来自 CTE 
  join 预订b  ON  CTE.booking = b.id 
 < span class =code-keyword> join  booking_destinations bd  on  b.id = bd.booking 
 加入位置l   bd.destination = l.zip_code 

我对你的表结构做了一些假设...这是我用来创建一些测试数据的代码（只包括我需要的列）

  create   table  booking 
（
 id  int ，
 Pickup_location  bigint  
 
）
  insert  进入预订值（ 1001  ， 44456 ），（ 2005 ， 44457  ）
 
 创建 表 booking_destinations 
（
预订 int ，
 [ sequence ]  int ，
 destination  bigint  
）
 插入 进入 booking_destinations  values  
（ 1001 ， 1 ，  43367 ），
（ 1001 ， 2 ， 43876 ），（ 2005 ， 1 ， 56721 ），（ 2005 ， 2 ， 53242 ），（ 2005 ， 3 ， 45234 ）
 
  create  表位置
（
 id  int  身份（  1 ， 1 ），
 zip_code  bigint  
）
  insert  进入位置值（ 44456 ），（ 44457 ），（ 43367 ），（ 43876 ），（ 56721 ），（ 53242 ），（ 45234 ）

How do I count the number of times a location(zip code) is an intermediate destination and also count that it is a final destination? Because some locations have different number of destination.
So is wrong to count when sequence is 1 or 2 or 3 and when sequence is 4 as shown in my code? Sequence 1,2,3,4 means dest1,2,3,4

Eg booking id 1001
Pickup location 44456
Destination 1 43367
Destination 2 43876
Dest1 will be intermediate
Dest 2 will be final
Eg booking id 2005
Destination 1 56721
Destination 2 53242
Destination 3 45234
Dest1,2 intermediate
Dest 3 final

My code
select l.zip_code,sum(dum.tab) from location l,
(select count( Pickup_location) as tab from booking b,location l where b.pickup_location = l.Zip_Code group by l.zip_Code
union select count( destination) as tab from booking_destinations bd inner join location l on bd.destination = l.Zip_Code where bd.sequence in(1,2,3) group by l.zip_Code
union select count( destination) as tab from booking_destinations bd,location l where bd.destination = l.Zip_Code and sequence = 4 group by l.zip_Code
) as dum group by l.zip_Code ;

What I have tried:

Searching online and removing the select statement when sequence = 4

解决方案

You don't want to count the destinations, you just want to know which was the first and which was the last. You can do this by using GROUP BY on the booking code and getting the MIN and MAX of the sequence number. E.g.

select booking, max(sequence) as final, min(sequence) as [first]
from booking_destinations bd
group by booking

You can then incorporate that sub-query into an overall result by either using a sub-query OR (my preference) using a Common Table Expression...

Sub-query example:

select fl.booking, b.Pickup_location, bd.sequence, bd.destination,
	case when bd.sequence = fl.final then 'Final' 
		 when bd.sequence = fl.[first] then 'First'
		 else 'Interim' 
	end
from 
(	select booking, max(sequence) as final, min(sequence) as [first]
	from booking_destinations bd 
	group by booking
) fl
join booking b ON fl.booking = b.id
join booking_destinations bd on b.id = bd.booking
join location l on bd.destination = l.zip_code

CTE example:

;with CTE as
(
	select booking, max(sequence) as final, min(sequence) as [first]
	from booking_destinations bd 
	group by booking
)
select CTE.booking, b.Pickup_location, bd.sequence, bd.destination,
	case when bd.sequence = CTE.final then 'Final' 
		 when bd.sequence = CTE.first then 'First'
		 else 'Interim' 
	end
from CTE
join booking b ON CTE.booking = b.id
join booking_destinations bd on b.id = bd.booking
join location l on bd.destination = l.zip_code

I made some assumptions about your table structure ... this is the code I used to create some test data (only the columns I needed have been included)

create table booking
 (
	id int,
	Pickup_location bigint
	
 )
 insert into booking values (1001, 44456),(2005, 44457)
 
  create table booking_destinations
 (
	booking int,
	[sequence] int,
	destination bigint
 )
 insert into booking_destinations values 
 (1001,1,43367),
 (1001,2,43876),(2005,1,56721),(2005,2,53242),(2005,3,45234)
 
 create table location
 (
	id int identity(1,1),
	zip_code bigint
 )
insert into location values(44456),(44457),(43367),(43876),(56721),(53242),(45234)

这篇关于如何将id的最后一个目的地作为最终目的地，其余目标是中间目的地？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！