选择部分匹配另一个表中的行的行

I have a table for the actions.

我有一个行动表。

The table has several slots for the same time on the same day. Same action can't be booked for the same time twice. I'm trying to come up with the way to list all the IDs for an action 'A', such as every available time is listed only once, even if there are both slots available, but if 'A' is book for some time already and another slot for this time is empty, that slot wouldn't be showing. And it comes to me that I don't know T-SQL that good. I overcame this by selecting all the rows where 'A' is booked, selecting all distinct (date, time start and time end) which are not booked and doing check whether 'A' is already booked for this time. But all this checking is done on the software level, and those multiple requests to the server and looping in the program to perform the same job as one LIKELY SIMPLE sql request don't look very efficient to me. If there a way to do something like:

该表在同一天同时有几个插槽。同一行动不能同时预订两次。我试图找出列出动作'A'的所有ID的方法,例如每个可用时间仅列出一次,即使两个插槽都可用,但如果'A'是预订了一段时间已经和这个时间的另一个插槽是空的,该插槽不会显示。在我看来,我不知道T-SQL那么好。我通过选择预订'A'的所有行来克服这个问题,选择未预订的所有不同(日期,时间开始和时间结束)并检查此时是否已预订'A'。但所有这些检查都是在软件级别完成的,那些对服务器的多次请求以及在程序中循环执行与一个LIKELY SIMPLE sql请求相同的工作对我来说效率不高。如果有办法做这样的事情:

    SELECT ID FROM mytable
    WHERE Action IS NULL AND (date, time_start, time_end **'ALL TOGETHER IN ONE ROW'**)
    NOT IN (SELECT date, time_start, time_end FROM mytable 
            WHERE Action = 'A')
HAVING 'THOSE THREE BEING DISTINCT'

By other words can I select rows which partially match other rows? It would be simple if I had only one column to compare, but there are three.

换句话说,我可以选择与其他行部分匹配的行吗?如果我只有一列可以比较,那将很简单,但有三列。

2 个解决方案

#1

In SQL Server we generally use WHILE instead of FOR. I believe what you're trying to do could be fulfilled as follows if you want to loop through the table (ideally your ID field would be the PRIMARY KEY as well). This is just inserting it into a temp table for now, but potentially it should give you the results you want:

在SQL Server中,我们通常使用WHILE而不是FOR。如果你想循环遍历表格,我相信你想要做的事情可以完成如下(理想情况下你的ID字段也是PRIMARY KEY)。这只是暂时将它插入到临时表中,但它可能会给你想要的结果:

-- DECLARE and set counters
DECLARE @curr INT, @prev INT, @max INT
SELECT @curr = 0, @prev = 0, @max = MAX(ID) FROM myTable

-- Make a simple temp table
CREATE TABLE #temp (ID INT)

-- Start looping
WHILE (@curr < @max)
BEGIN
    -- Set our counter for the next row
    SELECT @curr = MIN(ID) FROM myTable WHERE ID > @prev

    -- Populate temp table with a self-join to compare slots
    -- Slot must match on date + time but NOT have equal SLOT value 
    -- Will only INSERT if we meet our criteria i.e. neither slot booked
    INSERT  INTO #temp
    SELECT  DISTINCT A.ID
    FROM    myTable A
    JOIN    myTable B ON B.[Date] = A.[date] AND B.time_start = A.time_start AND B.time_end = A.time_end
    WHERE   A.[Action] IS NULL  -- Indicates NO booking
    AND     B.[Action] IS NULL  -- Indicates NO booking
    AND     A.SLOT <> B.SLOT
    AND     A.ID = @curr

    -- Update our counter         
    SET @prev = @curr
END

-- Get all our records
SELECT * FROM #temp

-- Remove the sleeping dog ;)
DROP TABLE #temp

There is a little bit of redundancy here because it checks ALL rows, even if a condition has been found in the first row of that time slot, but you can tweak it from here if you need to.

这里有一点冗余,因为它会检查所有行,即使在该时隙的第一行中找到了条件,但如果需要,可以从此处调整它。

You should really avoid using field names like "Date" and "Action" because these are reserved words in SQL.

您应该避免使用“Date”和“Action”等字段名称,因为这些是SQL中的保留字。

#2

You question is a bit unclear, but I think this will point you in a productive direction. SQL is designed to perform operation on sets of rows, not to loop through processing one row at a time. The following code will correlate your data into one row for each pair of slots at each date/time. You can use a CASE expression, as shown, to add a column that indicates the status of the row, and you can then add a WHERE clause, not shown, to perform any additional filtering.

你的问题有点不清楚,但我认为这会使你指向一个富有成效的方向。 SQL旨在对行集执行操作,而不是循环处理一行一行。以下代码会在每个日期/时间为每对插槽将您的数据关联到一行。您可以使用CASE表达式(如图所示)添加指示行状态的列,然后可以添加WHERE子句(未显示)以执行任何其他过滤。

-- Sample data.
declare @Samples as Table ( SampleId Int, Slot Int, EventDate Date, StartTime Time(0), EndTime Time(0), Action VarChar(10) );
insert into @Samples ( SampleId, Slot, EventDate, StartTime, EndTime, Action ) values
  ( 200, 1, '20150501', '00:00:00', '00:30:00', NULL ),
  ( 201, 2, '20150501', '00:00:00', '00:30:00', NULL ),
  ( 202, 1, '20150501', '00:30:00', '01:00:00', 'A' ),
  ( 203, 2, '20150501', '00:30:00', '01:00:00', NULL ),
  ( 204, 1, '20150501', '01:00:00', '01:30:00', NULL ),
  ( 205, 2, '20150501', '01:00:00', '01:30:00', 'A' ),
  ( 206, 1, '20150501', '01:30:00', '02:00:00', 'B' ),
  ( 207, 2, '20150501', '01:30:00', '02:00:00', 'B' );
select * from @Samples;

-- Data correleated for each date/time.
select Slot1.EventDate, Slot1.StartTime, Slot1.EndTime,
  Slot1.Action as Action1, Slot2.Action as Action2,
  Coalesce( Slot1.Action, Slot2.Action ) as SummaryAction,
  case when Slot1.Action = Slot2.Action then 'ERROR!' else 'Okay.' end as Status
  from @Samples as Slot1 inner join
    @Samples as Slot2 on Slot2.EventDate = Slot1.EventDate and Slot2.StartTime = Slot1.StartTime and
    Slot1.Slot = 1 and Slot2.Slot = 2;