sql - SQL-为什么这行不通？

以下脚本允许您创建数据库并使用我的问题的必要数据填充它：

# HeidiSQL Dump
#
# --------------------------------------------------------
# Host:                 127.0.0.1
# Database:             blueskylearning
# Server version:       5.1.22-rc-community
# Server OS:            Win32
# Target-Compatibility: MySQL 5.0
# Extended INSERTs:     Y
# max_allowed_packet:   1048576
# HeidiSQL version:     3.0 Revision: 572
# --------------------------------------------------------

/*!40100 SET CHARACTER SET latin1*/;


#
# Database structure for database 'windowsLinuxProblem'
#

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `windowsLinuxProblem` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `windowsLinuxProblem`;


#
# Table structure for table 'organization'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ `organization` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;



#
# Dumping data for table 'organization'
#

/*!40000 ALTER TABLE `organization` DISABLE KEYS*/;
LOCK TABLES `organization` WRITE;
REPLACE INTO `organization` (`id`, `name`) VALUES
(1,'Org1');
UNLOCK TABLES;
/*!40000 ALTER TABLE `organization` ENABLE KEYS*/;


#
# Table structure for table 'resource'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ `resource` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `resourcePublic` tinyint(4) NOT NULL,
  `active` tinyint(4) NOT NULL,
  `published` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=534 DEFAULT CHARSET=utf8;



#
# Dumping data for table 'resource'
#

/*!40000 ALTER TABLE `resource` DISABLE KEYS*/;
LOCK TABLES `resource` WRITE;
REPLACE INTO `resource` (`id`, `title`, `resourcePublic`, `active`, `published`) VALUES
    (1,'Title number 1',1,1,1),
    (2,'Title number 2',1,1,1),
    (3,'Title number 3',1,1,1),
    (4,'Title number 4',1,1,1),
    (5,'Title number 5',1,1,1),
    (6,'Title number 6',1,1,1);
UNLOCK TABLES;
/*!40000 ALTER TABLE `resource` ENABLE KEYS*/;


#
# Table structure for table 'resourceorganization'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ `resourceorganization` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `resource_id` int(11) NOT NULL,
  `organization_id` int(11) NOT NULL,
  `forever` tinyint(4) NOT NULL,
  `startDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `endDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `resource_id` (`resource_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `resourceorganization_ibfk_1` FOREIGN KEY (`resource_id`) REFERENCES `resource` (`id`),
  CONSTRAINT `resourceorganization_ibfk_2` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8;

有人可以帮助我理解为什么以下查询未获取所有具有active = 1，published = 1，resourcePublic = 1的资源的原因

查询：

select *
from
    resource resource0_,
    resourceOrganization resourceor1_
where
    resource0_.active=1
    and resource0_.published=1
    and (
        resource0_.resourcePublic=1
        or resourceor1_.resource_id=resource0_.id
        and resourceor1_.organization_id=2
        and (
            resourceor1_.resource_id=resource0_.id
            and resourceor1_.forever=1
            or resourceor1_.resource_id=resource0_.id
            and (
                current_date between resourceor1_.startDate and resourceor1_.endDate
            )
        )
    )

最佳答案

就目前而言，此查询应简化为：

select *
from
    resource resource0_,
    resourceOrganization resourceor1_
where
    resource0_.active=1 and
    resource0_.published=1 and
    (resource0_.resourcePublic=1 or
        (resourceor1_.resource_id=resource0_.id and
         resourceor1_.organization_id=2 and
            (resourceor1_.forever=1 or
             current_date between resourceor1_.startDate and resourceor1_.endDate
            )
         )
     )

我希望这将对resourceOrganization表上的所有记录产生笛卡尔联接，其中resource表的active，published和resourcePublic值均为1。

资源表的活动值和发布值均为1，但resourcePublic不为1时，我希望内部联接到resourceOrganization表上的organization_id为2的记录，并且永远为1或当前日期为午夜，介于开始和结束日期时间值。我注意到结束日期时间默认为0000-00-00 00:00:00。

排除活动值或已发布值都不为1的记录，因此未报告资源的明显原因是resourcePublic值不为1，并且是：


没有organization_id 2的相应resourceOrganization记录，或者
相应的resourceOrganization记录的永久值不为1，并且
午夜的当前日期不在开始和结束日期时间值之间。


日期范围似乎是最有可能被错误排除的记录的来源-当日输入的记录在午夜之后将具有开始日期时间值，因此将被排除，而未指定结束日期的记录将默认为0000-00-00 00:00 ：00，因此也将排除在外。

因此，我建议将查询重写为：

select *
from resource r
left join resourceOrganization ro
on r.id = ro.resource_id and ro.organization_id = 2
where
    r.active=1 and
    r.published=1 and
    (r.resourcePublic=1 or
     ro.forever=1 or
     now() between ro.startDate and
           (case where ro.endDate = '0000-00-00 00:00:00'
                 then now()
                 else ro.endDate end
           )
     )

关于sql - SQL-为什么这行不通？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3917745/