SQL Microsoft Access显示立即下一行 [英] SQL Microsoft Access Show immediate next row

问题描述

如果WHERE语句中的rowa为true，我试图显示rowa和rowb(在其下方).我确实有ORDER BY Date，但这不会影响任何事情.如果显示了rowa，是否有任何快速的方法来显示rowb?

I'm trying to show rowa and rowb (right below it) if rowa is true in the WHERE statement. I do have it ORDER BY Date, but that wouldn't affect anything. Are there any quick ways of showing the rowb if rowa is shown?

我的代码是...

SELECT Roster.`Complete Name`, RS1018to1024.`Day of Week`, RS1018to1024.`Date`, RS1018to1024.`Time Type`, RS1018to1024.`Attribute`, RS1018to1024.`Value`, RS1018to1024.`Hourly value (in decimals)`

FROM Roster 

INNER JOIN RS1018to1024 ON Roster.GIN = RS1018to1024.GIN
WHERE (RS1018to1024.`Hourly value (in decimals)` >15 AND RS1018to1024.`Time Type`='Wellsite/Job/Vessel' OR RS1018to1024.Attribute='Job ID'  ) 
OR (RS1018to1024.`Time Type`='On Office-Base-Lab' AND RS1018to1024.Attribute='Regular Work Day' AND RS1018to1024.`Hourly value (in decimals)`>8)  

ORDER BY RS1018to1024.`Complete Name`, RS1018to1024.`Date`, RS1018to1024.`Attribute` DESC;

Day of Week|   Date    | Time Type |  Attribute    | Value     | Hourly value

Tues       |20-Oct-2015| Wellsite  |  Reg Work Day | RGWD      | 16.8

Tues       |20-Oct-2015| Wellsite  |  Job ID       | 2213      |

Friday     |23-Oct-2015| Wellsite  |  Job ID       | 2251      |

Wed        |21-Oct-2015| Wellsite  |  Reg Work Day | RGWD      | 24

Tues       |21-Oct-2015| Wellsite  |  Job ID       | 2317      |

Sunday     |18-Oct-2015| On Office |  Reg Work Day | RGWD      | 12.2

CREATE TABLE mytable(
   Day_of_Week                VARCHAR(8) NOT NULL PRIMARY KEY
  ,Date                       VARCHAR(9) NOT NULL
  ,Time_Type                  VARCHAR(19) NOT NULL
  ,Attribute                  VARCHAR(16) NOT NULL
  ,Value                      VARCHAR(28) NOT NULL
  ,Hourly_value_in_decimals   NUMERIC(5,2)
);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Tuesday','20-Oct-15','Wellsite/Job/Vessel','Regular Work Day','RGWD - Regular Work Day (BR)',16.75);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Tuesday','20-Oct-15','Wellsite/Job/Vessel','Job ID','2213840',NULL);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Friday','23-Oct-15','Wellsite/Job/Vessel','Job ID','2212599',NULL);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Friday','23-Oct-15','Wellsite/Job/Vessel','Regular Work Day','RGWD - Regular Work Day (BR)',23.87);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Saturday','24-Oct-15','Wellsite/Job/Vessel','Job ID','2212599',NULL);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Sunday','18-Oct-15','On Office-Base-Lab','Regular Work Day','RGWD - Regular Work Day (BR)',19.87);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Monday','19-Oct-15','On Office-Base-Lab','Regular Work Day','RGWD - Regular Work Day (BR)',11.17);

我想看的是...

Day of Week|   Date    | Time Type |  Attribute    | Value     | Hourly value

Tues       |20-Oct-2015| Wellsite  |  Reg Work Day | RGWD      | 16.8

Tues       |20-Oct-2015| Wellsite  |  Job ID       | 2213      |

Wed        |21-Oct-2015| Wellsite  |  Reg Work Day | RGWD      | 24

Tues       |21-Oct-2015| Wellsite  |  Job ID       | 2317      |

Sunday     |18-Oct-2015| On Office |  Reg Work Day | RGWD      | 12.2

CREATE TABLE mytable(
   Day_of_Week                VARCHAR(8) NOT NULL PRIMARY KEY
  ,Date                       VARCHAR(9) NOT NULL
  ,Time_Type                  VARCHAR(19) NOT NULL
  ,Attribute                  VARCHAR(16) NOT NULL
  ,Value                      VARCHAR(28) NOT NULL
  ,Hourly_value_in_decimals   NUMERIC(5,2)
);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Tuesday','20-Oct-15','Wellsite/Job/Vessel','Regular Work Day','RGWD - Regular Work Day (BR)',16.75);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Tuesday','20-Oct-15','Wellsite/Job/Vessel','Job ID','2213840',NULL);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Friday','23-Oct-15','Wellsite/Job/Vessel','Regular Work Day','RGWD - Regular Work Day (BR)',23.87);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Saturday','24-Oct-15','Wellsite/Job/Vessel','Job ID','2212599',NULL);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Sunday','18-Oct-15','On Office-Base-Lab','Regular Work Day','RGWD - Regular Work Day (BR)',19.87);
INSERT INTO mytable(Day_of_Week,Date,Time_Type,Attribute,Value,Hourly_value_in_decimals) VALUES ('Monday','19-Oct-15','On Office-Base-Lab','Regular Work Day','RGWD - Regular Work Day (BR)',11.17);

因此，每个显示Wellsite的时间类型都需要两行.一个用于注册工作日，下一行显示作业ID.我的代码显示了所有具有Job ID的行，除非他们的Reg Work Day在上面，否则我需要除去这些行.另外，我有一排上班时间类型"很好，并且没有任何问题.

So, every Time Type that says Wellsite needs two rows. One for the Reg Work Day and the next row showing the Job ID. My code is showing all rows that have Job ID which is what I need to get rid of unless they have the Reg Work Day above it. Also, I have a row of On Office Time Type that is fine and I'm not having any issues with.

推荐答案

需求的问题是您假设数据是有序的，并且此行和下一行.但是，在未对SQL数据进行排序的情况下，您需要在ORDER BY子句上使用一列来获取订单.

The problem with your requirements is you assume that the data is ordered and you this row and the next row. However in SQL data is not ordered you need a column to use on an ORDER BY clause to get an order.

查看您的示例数据，似乎您想要的两行都具有相同的日期.如果是这种情况，那么解决方案就不难了.您是这样做的:

Looking at your example data it seems that the two rows you want both have the same date. IF THIS IS THE CASE then the solution is not hard. You do it like this:

首先，您将获得符合条件的日期列表.

First you get a list of dates that meet the criteria.

SELECT `Date` 
FROM ROSTER 
WHERE `Hourly Value` > 15 AND Attribute = 'Reg Work Day'

然后，您将此列表作为选择表格其余部分的标准

Then you take this list as the criteria for selecting the rest of the table

SELECT *
FROM ROSTER
WHERE `Date` IN (
  SELECT `Date` 
  FROM ROSTER 
  WHERE `Hourly Value` > 15 AND Attribute = 'Reg Work Day'
) sub

这里的要点是，您不能认为SQL中的顺序和顺序-您必须考虑集合-SQL集合中的交易.

The takeaway here is that you can't think ordered and sequential in SQL -- you have to think in set -- SQL deals in sets.

这篇关于SQL Microsoft Access显示立即下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！