条件SQL查询协助 [英] Conditional SQL Query Assistance

查看：60
本文介绍了条件SQL查询协助的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

使用下面提供的所有内容以及所有示例和数据， 还有另一种方法可以做到 ，以得到不同表的UNION预期结果吗?

With everything and all the examples and data provided below, is there another way to do this to get the expected results doing the UNION of the different tables?

我的问题

本质上，我需要具有条件逻辑:

Essentially I need to have conditional logic to:

  1. 如果在 定义的时间段 中找到了记录，则从表

  1. if there are records found within the defined time period then get all the records (multiple) from the table

如果在 定义的时间段 内未找到任何记录，则从表中获取最新的1条记录，因为 定义的时间段

if there are no records found within the defined time period then get the newest 1 record from the table since before the defined time period

步骤

只需创建表并使用下面的逻辑插入我在下面列出的数据，然后运行每个示例中提供的每个查询，您就可以轻松地准确模拟我所指的内容.

Just create the tables and insert the data as I listed below with that logic, and then run each query I provided in each example and you can easily emulate exactly what I'm referring to.

上下文和澄清

  • 也许使用临时表/内存表来代替@Now~变量，并在相关临时表中在UNION处具有TRUE条件，或者也许是一些精心设计(或可能对某些情况而言简单)的数据表JOIN方法?

  • Perhaps using a temp / memory table in place of the @Now~ variables and have the TRUE condition there UNION in the correlated temp table, or maybe some elaborate (or may simple for some) data table JOIN methods?

我在下面指的是"定义的时间段"或具有相同含义的词，这些词是指特定的开始时间和结束时间，以及所有在这两点之间的时间-这就是我在使用该术语时所要谈论的.

I refer to a "Defined Time Period" below or words closely meaning the same, and by those words I am talking about a specific start time and a specific end time and all the time between those two points—that's what I'm talking about when I use the term.

  • 有一个生成这些开始和结束时间范围的报告系统，我已经知道了存储过程中的逻辑，该过程被称为日期时间".

  • There is a reporting system that generates these start and end time frames and I've gotten the logic in a stored procedure which is called and the date times are passed in.

  • 我只提供了存储过程的一部分，并从中进行了一些选择查询，以举例说明问题以及预期或所需的结果.

通过下面的代码，您可以在MySQL数据库中创建三个表，这还将填充我在运行以显示结果的查询中使用的数据，并在每个查询的下面填充数据，以便您可以跟踪并拥有甚至可以使用的数据帮我解决一个或两个或两个以上的指针.

With the below you can create three tables in a MySQL DB and this will also populate the data I used in the queries I run to show results and such beneath each so you can follow along and have data to play with to maybe even help me out with a solution or a pointer or two.

注意: 在确保将<DBName>更改为MySQL实例上的实际数据库名称或架构之前.

Note: Before sure to change <DBName> to an actual DB name or schema on your MySQL instance.

USE <DBName>; 
CREATE TABLE `ponumber` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

CREATE TABLE `batch_number` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

CREATE TABLE `batchweight` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 10:47:55',0);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',1217911);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 05:24:18',1217906);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:18',5522);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:25:33',5521);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 11:44:45',5520);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:05',5519);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:22:58',5518);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:19',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 05:23:03',31002);

查询一个

USE <DBName>; 

SET @bStartTime   = '2017-09-29 11:10:00';
SET @bEndTime     = '2017-09-29 12:48:00';

SELECT TimeStr, CONCAT('Set Load Number: ',Value) AS Value
FROM ponumber 
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

UNION 

SELECT TimeStr, CONCAT('Set Batch Number: ',Value) AS Value
FROM batch_number 
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

UNION 

SELECT TimeStr, 
    CONCAT('Batch Weight: ',Value) AS Value
FROM batchweight
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

ORDER BY TimeStr;

查询一个结果

如您所见，没有Set Load Number，因为PONumber表中没有@bStartTime和@bEndTime变量定义的时间(定义的时间段)中的记录.

As you can see there is no Set Load Number because there are no records in the PONumber table from the times defined by the @bStartTime and @bEndTime variables—the defined time period.

如果在定义的时间段内PONumber表中没有数据记录，则结果应列出该表中的最新记录，即使这些记录不在定义的时间段内，因此我建立了 Query两个.

If there are no data records in the PONumber table during the defined time period then the result should list the most recent records from that table, even those fall outside of the defined time period, so I built Query Two.

USE <DBName>; 
SET @bStartTime   = '2017-09-29 11:10:00';
SET @bEndTime     = '2017-09-29 12:48:00';

SET @LastPONumber = (SELECT Value FROM PONumber ORDER BY TimeStr DESC LIMIT 1); 
SET @NowPONumber  = (SELECT Value FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);
SET @LastPONumTimeStr  = (SELECT TimeStr FROM PONumber ORDER BY TimeStr DESC LIMIT 1); 
SET @NowPONumTimeStr   = (SELECT TimeStr FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);

SELECT DISTINCT TimeStr, Value FROM  
    (SELECT 
        CASE 
        WHEN TimeStr = 1 THEN 
            (SELECT @NowPONumTimeStr AS TimeStr) 
                ELSE 
            (SELECT @LastPONumTimeStr AS TimeStr)
                END AS TimeStr,
        CASE 
        WHEN Value = 1 THEN 
            (SELECT (CONCAT('Set Load Number: ',@NowPONumber)) AS Value) 
                ELSE 
            (SELECT (CONCAT('Set Load Number: ',@LastPONumber)) AS Value)
                END AS Value
    FROM PONumber) AS X

UNION 

SELECT TimeStr, CONCAT('Set Batch Number: ',Value) AS Value
FROM batch_number 
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

UNION 

SELECT TimeStr, 
    CONCAT('Batch Weight: ',Value) AS Value
FROM batchweight
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

ORDER BY TimeStr;

查询两个结果

因此 Query Two 是创建@LastPONumber，@NowPONumber，@LastPONumTimeStr和@NowPONumTimeStr变量并将这些值设置为使用SELECT查询结果的地方>和LIMIT 1取决于使用CASE WHEN表达式的条件.

So Query Two is where I created the @LastPONumber, @NowPONumber, @LastPONumTimeStr, and @NowPONumTimeStr variables and set those values to be a SELECT query result using ORDER BY DESC and LIMIT 1 depending on the conditions using CASE WHEN expressions.

现在的结果显示了最新的PONumber表记录，因为在定义的时间段内未找到任何记录，因此这使它可以在定义的时间段外返回.

The results now show the most recent PONumber table records since none were found between the defined time period, so this allowed it to go back further outside the defined time period.

现在有一个要求，就是在定义的时间段内Batch_Number表中没有数据记录来列出最新记录，就像查询二中的PONumber表一样，因此我使用与PONumber表所用的查询二相同的条件CASE WHEN逻辑，构建了查询三.

Now there's a requirement if there are no data records in the Batch_Number table during a defined time period to list the most recent record just as done with the PONumber table in Query Two, so I built Query Three using the same condition CASE WHEN logic, etc. as Query Two used for the PONumber table.

USE <DBName>; 
SET @bStartTime   = '2017-09-29 11:10:00';
SET @bEndTime     = '2017-09-29 12:48:00';

## - PONumber Table Variables
SET @LastPONumber = (SELECT Value FROM PONumber ORDER BY TimeStr DESC LIMIT 1); 
SET @NowPONumber  = (SELECT Value FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);
SET @LastPONumTimeStr  = (SELECT TimeStr FROM PONumber ORDER BY TimeStr DESC LIMIT 1); 
SET @NowPONumTimeStr   = (SELECT TimeStr FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);

## - Batch_Number Table Variables
SET @LastBatNumber = (SELECT Value FROM Batch_Number ORDER BY TimeStr DESC LIMIT 1); 
SET @NowBatNumber  = (SELECT Value FROM Batch_Number WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);
SET @LastBatNumTimeStr  = (SELECT TimeStr FROM Batch_Number ORDER BY TimeStr DESC LIMIT 1); 
SET @NowBatNumTimeStr   = (SELECT TimeStr FROM Batch_Number WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);

SELECT DISTINCT TimeStr, Value FROM  
    (SELECT 
        CASE 
        WHEN TimeStr = 1 THEN 
            (SELECT @NowPONumTimeStr AS TimeStr) 
                ELSE 
            (SELECT @LastPONumTimeStr AS TimeStr)
                END AS TimeStr,
        CASE 
        WHEN Value = 1 THEN 
            (SELECT (CONCAT('Set Load Number: ',@NowPONumber)) AS Value) 
                ELSE 
            (SELECT (CONCAT('Set Load Number: ',@LastPONumber)) AS Value)
                END AS Value
    FROM PONumber) AS X

UNION 

SELECT DISTINCT TimeStr, Value FROM  
    (SELECT 
        CASE 
        WHEN TimeStr = 1 THEN 
            (SELECT @NowBatNumTimeStr AS TimeStr) 
                ELSE 
            (SELECT @LastBatNumTimeStr AS TimeStr)
                END AS TimeStr,
        CASE 
        WHEN Value = 1 THEN 
            (SELECT (CONCAT('Set Batch Number: ',@NowBatNumber)) AS Value) 
                ELSE 
            (SELECT (CONCAT('Set Batch Number: ',@LastBatNumber)) AS Value)
                END AS Value
    FROM Batch_Number) AS X

UNION 

SELECT TimeStr, 
    CONCAT('Batch Weight: ',Value) AS Value
FROM batchweight
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

ORDER BY TimeStr;

查询三个结果

当查询三的结果检测到Batch_Number表有记录在定义的时间段之间时，它仅列出最近的一条记录，而没有列出列出其他两个批号值(5521, and 5520)，如查询两个结果中所示.显然，这是由于@NowBatNumber查询变量仅包含最近的1条记录.

When the result of Query Three detects the Batch_Number table does have records between the defined time period, it only lists the most recent one record and doesn't list the other two batch number values (5521, and 5520) as shown in Query Two Result. This is obviously due to the @NowBatNumber query variable containing only the most recent 1 record.

问题

  • 我需要能够检查之间的Batch_Number记录 确定时间段并执行以下两项操作之一:

  • I need to be able to check for Batch_Number records between a defined time period and do one of two things:

  1. 如果存在该时间段之间的记录，则列出所有记录，而不仅仅是最近的一条记录

  1. If the records between that time period exist then list all the records and not just the most recent one record

如果在定义的时间段内未找到任何记录，则在该时间段外进一步查找并列出最近的一条记录

If no records are found between the defined time period then look back further outside that time period and list the most recent one record

基本上对于Batch_Number表以及我如何在查询三中进行尝试，我希望结果看起来与查询两个结果完全一样，并且仍然使用条件逻辑来显示否则需要什么.

Essentially for the Batch_Number table and how I tried that in Query Three I would expect the results to look exactly like the Query Two Result and still use the conditional logic to show what's needed otherwise.

请返回并再次阅读问题，因为现在可能更有意义.

Go back and up read the Question again please as it may make better sense now.

推荐答案

简短和简单的问题比长/复杂的问题更引人注意.这不是因为我们不能回答，而是有很多问题，而且志愿者付出的时间很少，很难证明有时间阅读大问题.

Short and simple questions tend to get more attention than long/complex ones. This isn't because we can't answer but with so many questions, and so little volunteer time to give, it is hard to justify the time to read big questions.

但是我认为您的基本要求并不那么复杂.您需要一种方法来检索属于某个时间范围的行，或者如果不在该范围内，则提供与该范围最接近的行.

However I think your basic requirement isn't that complex. You want a way to retrieve rows that fall within a time range OR if not in that range provide the closest rows to that range.

在支持ROW_NUMBER()OVER()的数据库中，这非常容易(并且MySQL 8.x计划支持该功能)，但是在那之前，要模拟row_number()，您可以使用变量和有序子查询.

In databases that support ROW_NUMBER() OVER() this is quite easy (and MySQL 8.x is planned to support this), but until that time to emulate row_number() you can use variables and an ordered subquery.

您可以在 SQL小提琴

MySQL 5.6模式设置:

CREATE TABLE `ponumber` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 10:47:55',0);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',1217911);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 05:24:18',1217906);

CREATE TABLE `batch_number` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:18',5522);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:25:33',5521);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 11:44:45',5520);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:05',5519);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:22:58',5518);

CREATE TABLE `batchweight` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:19',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 05:23:03',31002);

查询:

SET @bStartTime  := '2017-09-29 11:10:00'   
SET @bEndTime    := '2017-09-29 12:48:00'

SELECT 
      SrcTable, TimeStr, Value
FROM (
      SELECT
            @row_num :=IF( @prev_value=u.SrcTable, @row_num + 1 ,1) AS RowNumber
          , u.*
          , @prev_value := u.SrcTable
      FROM (

          select 'ponumber' SrcTable , TimeStr, `Value`
          from ponumber
          union all
          select 'batch_number' SrcTable , TimeStr, `Value`
          from batch_number
          union all
          select 'batchweight' SrcTable , TimeStr, `Value`
          from batchweight
          ) u
      CROSS JOIN (SELECT @row_num := 1,  @prev_value :='') vars
      ORDER BY SrcTable, TimeStr DESC
      ) d
WHERE (d.TimeStr between @bStartTime and @bEndTime)
   OR (TimeStr < @bStartTime AND RowNumber = 1)

因此，这是为每个源表的最新行计算一个从1开始的行号".然后，可以根据时间范围或行号(如果不在时间范围内)过滤此派生表.

So, what this does is compute a "RowNumber" which starts at 1 for the most recent row for each source table. Then this derived table is filtered either by the time range, or by the row number if not within the time range.

还请注意，我未使用 UNION，但是使用了UNION ALL.性能上有很大差异，应根据需要学习使用每种方法.如果使用UNION，请不要同时使用select distinct，因为这只是浪费时间.

Also note I have NOT used UNION but instead have used UNION ALL. There is a big difference in performance and should learn to use each according to need. If using UNION don't also use select distinct because you are just wasting effort.

结果 :

Results:

|     SrcTable |              TimeStr | Value |
|--------------|----------------------|-------|
|  batchweight | 2017-09-29T12:46:19Z | 38985 |
| batch_number | 2017-09-29T12:46:18Z |  5522 |
| batch_number | 2017-09-29T12:25:33Z |  5521 |
| batch_number | 2017-09-29T11:44:45Z |  5520 |
|     ponumber | 2017-09-28T10:47:55Z |     0 |

这篇关于条件SQL查询协助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆