SQL根据另一个表的行计数隐藏/显示行

I have a SQL question. I have two tables, tableA has 4 records, tableB has 0 records right now, but will go over 200 total records. I was wondering if there is away to hide the last two records of tableA if tableB is under 200 records?

我有一个SQL问题。我有两个表，tableA有4条记录，tableB现在有0条记录，但总记录超过200条。如果tableB低于200条记录，我想知道是否有隐藏tableA的最后两条记录？

What I got so far is very simple

到目前为止我所得到的非常简单

SELECT 
    id, dateSlot, timeSlot
FROM 
    tableA a 
INNER JOIN
    tableB b ON a.id = b.dateTimeSlotId;

I just don't know how to hide records based on another tables total records.

我只是不知道如何根据另一个表总记录隐藏记录。

Can anyone help?

有人可以帮忙吗？

1 个解决方案

#1

It is only an idea. if the tables are not symmetric you need to improve the logic.

这只是一个想法。如果表不对称，则需要改进逻辑。

declare @tableA table (id int)
declare @tableB table (dateTimeSlotId int , dateSlot date, timeSlot time) 

insert @tableA values (1),(2),(3),(4),(5),(6),(7)
insert @tableB values 
(1,'20170801', '00:00'),
(2,'20170802', '00:01'),
(3,'20170803', '00:02'),
(4,'20170804', '00:03'),
(5,'20170805', '00:04'),
(6,'20170806', '00:05'),
(7,'20170807', '00:06')

;with cte as(
SELECT ROW_NUMBER() over (order by id) rNumber, id, dateSlot, timeSlot
FROM @tableA a INNER JOIN
     @tableB b
      ON a.id = b.dateTimeSlotId)
SELECT  id, dateSlot, timeSlot
FROM cte where rNumber <= (SELECT case when Count(1) >= 200 then Count(1) -2 else Count(1) end  from @tableB)