草庐IT

mysql - 在 SQL 查询中查找缺失的数字

coder 2023-10-06 原文

我需要找到 0 到 16 之间缺失的数字。

我的表是这样的:

CarId    FromCity_Id    ToCity_Id    Ran_Date    RunId
1001        0              2        01-08-2013     1
1001        5              9        02-08-2013     2
1001        11             16       03-08-2013     3
1002        0              11       02-08-2013     4
1002        11             16       08-08-2013     5

我需要找出:

从now()过去的三个月里，汽车没有跑过哪些城市。

例如在上面的记录中:

  • 1001 号车未在 02-05 和 09-11 之间运行
  • 汽车 1002 已完全运行(即在 0-11 和 11-16 之间)

总而言之，我需要生成一个查询，显示过去 3 个月汽车未运行的路段，并显示上次运行日期。

请问如何进行这样的查询。如果有任何存储过程请告知。

最佳答案

求大神帮忙。这使用了双相关子查询，您的系统中可能不存在的表，以及太多的咖啡因。但是，嘿，它有效。

好的，开始了。

SELECT CarId, GROUP_CONCAT(DISTINCT missing) missing
FROM MyTable r, 
   (SELECT @a := @a + 1 missing 
    FROM mysql.help_relation, (SELECT @a := -1) t 
    WHERE @a < 16 ) y
WHERE NOT EXISTS 
   (SELECT r.CarID FROM MyTable m 
    WHERE y.missing BETWEEN FromCity_Id AND ToCity_Id
       AND r.carid = m.carid)
GROUP BY CarID;

生成(将 CarID 1002 的第一行更改为 0-9 以打开 10 并为我们提供更好的测试数据):

+-------+---------+
| CarId | missing |
+-------+---------+
|  1001 | 3,4,10  |
|  1002 | 10      |
+-------+---------+
2 rows in set (0.00 sec)

这一切是如何运作的？

首先... 内部查询为我们提供了一个从 0 到 16 的数字列表:

   (SELECT @a := @a + 1 missing 
    FROM mysql.help_relation, (SELECT @a := -1) t 
    WHERE @a < 16 ) y

它通过从 -1 开始，然后显示在某个牺牲表中为每一行的该数字加 1 的结果来实现这一点。我正在使用 mysql.help_relation 因为它有超过一千行并且大多数基本系统都有它。 YMMV.

然后我们将它与 MyTable 交叉连接:

SELECT CarId, ...
FROM MyTable r, 
   (...) y

这为我们提供了所有可能的行组合，因此我们将每个 CarId 和 To/From ID 与 1-16 的每个数字混合在一起。

正在过滤... 这就是它变得有趣的地方。我们需要找到不匹配数字的行，我们需要根据 CarID 这样做。这种事情可以做到(只要 y.missing 存在，当我们关联子查询时它就会这样做):

    SELECT m.CarID FROM MyTable m 
    WHERE y.missing BETWEEN FromCity_Id AND ToCity_Id 
       AND m.CarID = 1001;

请记住:y.missing 设置为 1-16 之间的数字，与 MyTable 中的行交叉连接。这为我们提供了 1-16 中所有号码的列表，其中 CarID 1001 正忙。我们可以使用 NOT EXISTS 反转该集合，同时将(再次)与 CarId 相关联，这样我们就可以得到所有这样的 ID。

然后很容易过滤掉不适合的行:

SELECT CarId, ...
FROM MyTable r, 
   (...) y
WHERE NOT EXISTS 
   (SELECT r.CarID FROM MyTable m 
    WHERE y.missing BETWEEN FromCity_Id AND ToCity_Id 
       AND r.carid = m.carid)

输出 为了给出一个合理的结果(尝试 1)，我们可以得到不同的组合。这是那个版本:

SELECT DISTINCT CarId, missing
FROM MyTable r, 
   (SELECT @a := @a + 1 missing 
    FROM mysql.help_relation, (SELECT @a := -1) t 
    WHERE @a < 16 ) y
WHERE NOT EXISTS 
   (SELECT r.CarID FROM MyTable m 
    WHERE y.missing BETWEEN FromCity_Id AND ToCity_Id 
       AND r.carid = m.carid);

这给出:

+-------+---------+
| CarId | missing |
+-------+---------+
|  1001 |       3 |
|  1001 |       4 |
|  1001 |      10 |
|  1002 |      10 |
+-------+---------+
4 rows in set (0.01 sec)

GROUP BY 和 GROUP CONCAT 的简单相加可以让您在这个答案的顶部得到漂亮的结果。

对于给您带来的不便，我深表歉意。

关于mysql - 在 SQL 查询中查找缺失的数字，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18700671/

缺失mysqlcodemissingSELECT

有关mysql - 在 SQL 查询中查找缺失的数字的更多相关文章

  1. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  2. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

    我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

  3. ruby - 当使用::指定模块时，为什么 Ruby 不在更高范围内查找类？ - 2

    我刚刚被困在这个问题上一段时间了。以这个基地为例:moduleTopclassTestendmoduleFooendend稍后，我可以通过这样做在Foo中定义扩展Test的类:moduleTopmoduleFooclassSomeTest但是，如果我尝试通过使用::指定模块来最小化缩进:moduleTop::FooclassFailure这失败了:NameError:uninitializedconstantTop::Foo::Test这是一个错误，还是仅仅是Ruby解析变量名的方式的逻辑结果？ 最佳答案 Isthisabug,or

  4. ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2

    我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s

  5. 区块链之加解密算法&数字证书 - 2

    目录一.加解密算法数字签名对称加密DES(DataEncryptionStandard)3DES(TripleDES)AES(AdvancedEncryptionStandard)RSA加密法DSA(DigitalSignatureAlgorithm)ECC(EllipticCurvesCryptography)非对称加密签名与加密过程非对称加密的应用对称加密与非对称加密的结合二.数字证书图解一.加解密算法加密简单而言就是通过一种算法将明文信息转换成密文信息，信息的的接收方能够通过密钥对密文信息进行解密获得明文信息的过程。根据加解密的密钥是否相同，算法可以分为对称加密、非对称加密、对称加密和非

  6. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  7. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  8. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  9. ruby-on-rails - 缺失区域；使用 :region option or export region name to ENV ['AWS_REGION' ] - 2

    我知道还有其他相同的问题，但他们没有解决我的问题。我不断收到错误:Aws::Errors::MissingRegionErrorinBooksController#create,缺少区域；使用:region选项或将区域名称导出到ENV['AWS_REGION']。但是，这是我的配置开发.rb:config.paperclip_defaults={storage::s3,s3_host_name:"s3-us-west-2.amazonaws.com",s3_credentials:{bucket:ENV['AWS_BUCKET'],access_key_id:ENV['AWS_ACCE

  10. ruby-on-rails - 在 Rails 中更高效地查找或创建多条记录 - 2

    我有一个应用需要发送用户事件邀请。当用户邀请friend(用户)参加事件时，如果尚不存在将用户连接到该事件的新记录，则会创建该记录。我的模型由用户、事件和events_user组成。classEventdefinvite(user_id,*args)user_id.eachdo|u|e=EventsUser.find_or_create_by_event_id_and_user_id(self.id,u)e.save!endendend用法Event.first.invite([1,2,3])我不认为以上是完成我的任务的最有效方法。我设想了一种方法，例如Model.find_or_cr

随机推荐