按座位选择连续座位

Question

SELECT TOP N seats.*, eventtickets.eticketprice from seats 
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype 
WHERE ((Seats.seatType ='BOXSEAT') 
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
ORDER BY convert (int, seats.seatSection), seats.seatrow ASC

我上面的查询生成了N个座位的最佳可用#，但它将填满一个部分，然后将该顺序的其余部分放入下一节。我需要找到一个有N个座位的新区。如果座位在TicketSales表中，则不可用。如果salecategory是空的，那么这意味着票证还没有售出/阻止/持有，所以它是可用的。

很明显，这条条例草案共限制在6个议席之内，但我有一个组别，每一节有30多个议席。

Section 1: 
Row 1: 1-2-3
Row 2: 4-5-6

Section 2:
Row 1: 1-2-3
Row 2: 4-5-6

等。

我快疯了。一直盯着这个看了两天，我脑子里都是糊里糊涂的。我在SQL方面相当薄弱，令人惊讶的是，我在上面得到了如此多的成绩。我想我已经接近了，但没有。

见所附两个主要表的图片。我只需要seatprice从eventtickets表，所以它没有那么重要。

我感谢你在这件事上给我的任何帮助。

Answer 1

如果您对已经拥有的内容感到满意，请尝试从一组不同的部分开始，交叉连接，然后将您的子查询过滤到该部分。这样的话，就像把每个部分当作是自己的活动一样来考虑。

但是..。为什么不考虑看看所有的座位，找出(N-1)下一个是实际正确数字的块：

SELECT * FROM
(SELECT seats.*, eventtickets.eticketprice,
    LEAD(seats.SeatID,@N-1) OVER (PARTITION BY seats.seatSection ORDER BY seats.SeatID) AS SeatNAway
from seats 
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype 
WHERE ((Seats.seatType ='BOXSEAT') 
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
) s
WHERE s.SeatId = s.SeatNAway - (@N-1)
ORDER BY convert (int, s.seatSection), s.seatrow ASC

看看你怎么处理这个也许..。当然，请检查您是否理解内部子查询。

Answer 2

我想我是以一种迂回的方式弄到的。谢谢你的帮助和建议。我搞不懂Seatnaway，所以我不得不做点别的.

select TOP 3 seats.*, eventtickets.eticketprice from seats
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype 
WHERE seats.seatsection IN (SELECT TOP 1 SEATSECTION FROM seats 
WHERE seattype = 'rbox' AND seatHandicap <> 'HANDICAP'
AND (seatID not in (select seatID from ticketsales WHERE (eventID=6 AND (salecategory = 'SOLD' OR salecategory = 'BLOCK')) OR (eventID=6 AND salecategory = 'HOLD' AND seattimestamp > dateADD(mi, -30, getdate()) ))) 
group by seatsection having count(*) >= 3
ORDER BY convert (int, seatSection)) 
AND (Seats.seatType = 'rbox')
AND (eticketcategory = 'STOCKYARDSRODEO')
AND (seatID not in (select seatID from ticketsales WHERE (eventID=6 AND (salecategory = 'SOLD' OR salecategory = 'BLOCK')) OR (eventID=6 AND salecategory = 'HOLD' AND seattimestamp > dateADD(mi, -30, getdate()) )))