- android - RelativeLayout 背景可绘制重叠内容
- android - 如何链接 cpufeatures lib 以获取 native android 库?
- java - OnItemClickListener 不起作用,但 OnLongItemClickListener 在自定义 ListView 中起作用
- java - Android 文件转字符串
我正在运行以下查询。有时它返回大约 100 条记录,正如预期的那样,其他时候它只返回一条记录,其中所有连接字段的值都为 NULL。此外,explain 几乎每次都会返回不同的结果。
这里是查询:
EXPLAIN
SELECT
`fieldreps`.`user_id` AS `fieldreps.user_id`,
`fieldreps`.`availability_id` AS `fieldreps.availability_id`,
`fieldreps`.`applicant_type` AS `fieldreps.applicant_type`,
`fieldreps`.`license_number` AS `fieldreps.license_number`,
`fieldreps`.`license_exp` AS `fieldreps.license_exp`,
`fieldreps`.`license_state` AS `fieldreps.license_state`,
`fieldreps`.`car` AS `fieldreps.car`,
`fieldreps`.`authorized_worker` AS `fieldreps.authorized_worker`,
`fieldreps`.`restrictions` AS `fieldreps.restrictions`,
`fieldreps`.`night` AS `fieldreps.night`,
`fieldreps`.`day` AS `fieldreps.day`,
`fieldreps`.`longer` AS `fieldreps.longer`,
`fieldreps`.`commitment` AS `fieldreps.commitment`,
`fieldreps`.`travel_metro` AS `fieldreps.travel_metro`,
`fieldreps`.`travel_states` AS `fieldreps.travel_states`,
`fieldreps`.`big_employee` AS `fieldreps.big_employee`,
`fieldreps`.`employed` AS `fieldreps.employed`,
`fieldreps`.`retail` AS `fieldreps.retail`,
`fieldreps`.`status` AS `fieldreps.status`,
`fieldreps`.`start` AS `fieldreps.start`,
`fieldreps`.`sales` AS `fieldreps.sales`,
`fieldreps`.`study` AS `fieldreps.study`,
`fieldreps`.`relevant` AS `fieldreps.relevant`,
`fieldreps`.`experience` AS `fieldreps.experience`,
`fieldreps`.`claims` AS `fieldreps.claims`,
`fieldreps`.`education` AS `fieldreps.education`,
`fieldreps`.`degree_details` AS `fieldreps.degree_details`,
`fieldreps`.`degree_institution` AS `fieldreps.degree_institution`,
`fieldreps`.`other_training` AS `fieldreps.other_training`,
`fieldreps`.`jobs` AS `fieldreps.jobs`,
`fieldreps`.`current_training` AS `fieldreps.current_training`,
`fieldreps`.`interested` AS `fieldreps.interested`,
`fieldreps`.`achievements` AS `fieldreps.achievements`,
`fieldreps`.`passions` AS `fieldreps.passions`,
`fieldreps`.`ambitions` AS `fieldreps.ambitions`,
`fieldreps`.`max_travel_time` AS `fieldreps.max_travel_time`,
`fieldreps`.`creation_time` AS `fieldreps.creation_time`,
`fieldreps`.`resume` AS `fieldreps.resume`,
`users`.`user_id` AS `users.user_id`,
`users`.`email` AS `users.email`,
`users`.`name` AS `users.name`,
`users`.`password` AS `users.password`,
`users`.`given_name` AS `users.given_name`,
`users`.`title` AS `users.title`,
`users`.`nationality` AS `users.nationality`,
`tasks`.`task_id` AS `tasks.task_id`,
`tasks`.`recurrence_id` AS `tasks.recurrence_id`,
`tasks`.`parent_id` AS `tasks.parent_id`,
`tasks`.`user_id` AS `tasks.user_id`,
`tasks`.`campaign_id` AS `tasks.campaign_id`,
`tasks`.`location_id` AS `tasks.location_id`,
`tasks`.`title` AS `tasks.title`,
`tasks`.`description` AS `tasks.description`,
`tasks`.`earliest_start` AS `tasks.earliest_start`,
`tasks`.`deadline` AS `tasks.deadline`,
`tasks`.`km_estimate` AS `tasks.km_estimate`,
`tasks`.`estimate` AS `tasks.estimate`,
`datapoints`.`datapoint_id` AS `datapoints.datapoint_id`,
`datapoints`.`task_id` AS `datapoints.task_id`,
`datapoints`.`datatype_id` AS `datapoints.datatype_id`,
`datapoints`.`value` AS `datapoints.value`,
`datapoints`.`product_id` AS `datapoints.product_id`,
`datapoints`.`recorded` AS `datapoints.recorded`,
`datatypes`.`datatype_id` AS `datatypes.datatype_id`,
`datatypes`.`datatype_template_id` AS `datatypes.datatype_template_id`,
`datatypes`.`client_id` AS `datatypes.client_id`,
`datatypes`.`name` AS `datatypes.name`,
`datatypes`.`description` AS `datatypes.description`,
`datatypes`.`options` AS `datatypes.options`,
`locations`.`location_id` AS `locations.location_id`,
`locations`.`retailer_group_id` AS `locations.retailer_group_id`,
`locations`.`latitude` AS `locations.latitude`,
`locations`.`longitude` AS `locations.longitude`,
`locations`.`name` AS `locations.name`,
`locations`.`address` AS `locations.address`,
`locations`.`city` AS `locations.city`,
`locations`.`state` AS `locations.state`,
`locations`.`postal` AS `locations.postal`,
`locations`.`phone` AS `locations.phone`,
`locations`.`suburb` AS `locations.suburb`,
`locations`.`country` AS `locations.country`,
`locations`.`region` AS `locations.region`,
`locations`.`coverage` AS `locations.coverage`,
`locations`.`store_number` AS `locations.store_number`,
`locations`.`store_type` AS `locations.store_type`
FROM `fieldreps` AS `fieldreps`
LEFT JOIN `users` ON (`fieldreps`.`user_id` = `users`.`user_id`)
LEFT JOIN `tasks` ON (`fieldreps`.`user_id` = `tasks`.`user_id`)
LEFT JOIN `datapoints` ON (`tasks`.`task_id` = `datapoints`.`task_id`)
LEFT JOIN `datatypes` ON (`datapoints`.`datatype_id` = `datatypes`.`datatype_id`)
LEFT JOIN `locations` ON (`tasks`.`location_id` = `locations`.`location_id`)
WHERE `fieldreps`.`user_id` LIKE '5f5dc29c-832e-5646-9dc2-46d774bfb1ec'
我在 phpMyAdmin 中运行查询。数据库托管在 xeround.com 上(这可能是他们配置中的错误吗?)
我被难住了。我不确定从这里到哪里去。
提前致谢!
更新:
这是来自 phpMyAdmin 的转储:
-- phpMyAdmin SQL Dump
-- version 3.4.3.2
-- http://www.phpmyadmin.net
--
-- Generation Time: Jan 07, 2012 at 08:20 PM
-- Server version: 5.1.42
-- PHP Version: 5.3.1
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `big_development`
--
-- --------------------------------------------------------
--
-- Table structure for table `datapoints`
--
CREATE TABLE IF NOT EXISTS `datapoints` (
`datapoint_id` varchar(36) COLLATE utf8_bin NOT NULL,
`task_id` varchar(36) COLLATE utf8_bin NOT NULL,
`datatype_id` varchar(36) COLLATE utf8_bin NOT NULL,
`value` text COLLATE utf8_bin,
`product_id` varchar(36) COLLATE utf8_bin DEFAULT NULL,
`recorded` text COLLATE utf8_bin,
PRIMARY KEY (`datapoint_id`),
KEY `task_id` (`task_id`),
KEY `datatype_id` (`datatype_id`),
KEY `product_id` (`product_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `datatypes`
--
CREATE TABLE IF NOT EXISTS `datatypes` (
`datatype_id` varchar(36) COLLATE utf8_bin NOT NULL,
`datatype_template_id` varchar(36) COLLATE utf8_bin NOT NULL,
`client_id` varchar(36) COLLATE utf8_bin NOT NULL,
`name` text COLLATE utf8_bin,
`description` text COLLATE utf8_bin,
`options` text COLLATE utf8_bin,
PRIMARY KEY (`datatype_id`),
KEY `datatype_template_id` (`datatype_template_id`),
KEY `client_id` (`client_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `fieldreps`
--
CREATE TABLE IF NOT EXISTS `fieldreps` (
`user_id` varchar(36) COLLATE utf8_bin NOT NULL,
`availability_id` varchar(36) COLLATE utf8_bin NOT NULL,
`applicant_type` text COLLATE utf8_bin NOT NULL,
`license_number` text COLLATE utf8_bin NOT NULL,
`license_exp` text COLLATE utf8_bin,
`license_state` text COLLATE utf8_bin,
`car` text COLLATE utf8_bin NOT NULL,
`authorized_worker` text COLLATE utf8_bin NOT NULL,
`restrictions` text COLLATE utf8_bin NOT NULL,
`night` text COLLATE utf8_bin NOT NULL,
`day` text COLLATE utf8_bin NOT NULL,
`longer` text COLLATE utf8_bin NOT NULL,
`commitment` text COLLATE utf8_bin NOT NULL,
`travel_metro` text COLLATE utf8_bin NOT NULL,
`travel_states` text COLLATE utf8_bin NOT NULL,
`big_employee` text COLLATE utf8_bin NOT NULL,
`employed` text COLLATE utf8_bin NOT NULL,
`retail` text COLLATE utf8_bin NOT NULL,
`status` text COLLATE utf8_bin NOT NULL,
`start` text COLLATE utf8_bin,
`sales` text COLLATE utf8_bin NOT NULL,
`study` text COLLATE utf8_bin NOT NULL,
`relevant` text COLLATE utf8_bin NOT NULL,
`experience` text COLLATE utf8_bin NOT NULL,
`claims` text COLLATE utf8_bin NOT NULL,
`education` text COLLATE utf8_bin NOT NULL,
`degree_details` text COLLATE utf8_bin NOT NULL,
`degree_institution` text COLLATE utf8_bin NOT NULL,
`other_training` text COLLATE utf8_bin NOT NULL,
`jobs` text COLLATE utf8_bin NOT NULL,
`current_training` text COLLATE utf8_bin NOT NULL,
`interested` text COLLATE utf8_bin NOT NULL,
`achievements` text COLLATE utf8_bin NOT NULL,
`passions` text COLLATE utf8_bin NOT NULL,
`ambitions` text COLLATE utf8_bin NOT NULL,
`max_travel_time` text COLLATE utf8_bin NOT NULL,
`creation_time` text COLLATE utf8_bin,
`resume` text COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`user_id`),
KEY `availability_id` (`availability_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `locations`
--
CREATE TABLE IF NOT EXISTS `locations` (
`location_id` varchar(36) COLLATE utf8_bin NOT NULL,
`retailer_group_id` varchar(36) COLLATE utf8_bin NOT NULL,
`latitude` text COLLATE utf8_bin,
`longitude` text COLLATE utf8_bin,
`name` text COLLATE utf8_bin,
`address` text COLLATE utf8_bin,
`city` text COLLATE utf8_bin,
`state` text COLLATE utf8_bin,
`postal` text COLLATE utf8_bin,
`phone` text COLLATE utf8_bin,
`suburb` text COLLATE utf8_bin,
`country` text COLLATE utf8_bin,
`region` text COLLATE utf8_bin,
`coverage` tinyint(1) DEFAULT NULL,
`store_number` text COLLATE utf8_bin,
`store_type` text COLLATE utf8_bin,
PRIMARY KEY (`location_id`),
KEY `retailer_group_id` (`retailer_group_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `tasks`
--
CREATE TABLE IF NOT EXISTS `tasks` (
`task_id` varchar(36) COLLATE utf8_bin NOT NULL,
`recurrence_id` varchar(36) COLLATE utf8_bin NOT NULL,
`parent_id` varchar(36) COLLATE utf8_bin DEFAULT NULL,
`user_id` varchar(36) COLLATE utf8_bin NOT NULL,
`campaign_id` varchar(36) COLLATE utf8_bin NOT NULL,
`location_id` varchar(36) COLLATE utf8_bin NOT NULL,
`title` text COLLATE utf8_bin,
`description` text COLLATE utf8_bin,
`earliest_start` text COLLATE utf8_bin,
`deadline` text COLLATE utf8_bin,
`km_estimate` text COLLATE utf8_bin,
`estimate` int(11) DEFAULT NULL,
PRIMARY KEY (`task_id`),
KEY `campaign_id` (`campaign_id`),
KEY `location_id` (`location_id`),
KEY `user_id` (`user_id`),
KEY `parent_id` (`parent_id`),
KEY `recurrence_id` (`recurrence_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`user_id` varchar(36) COLLATE utf8_bin NOT NULL,
`email` varchar(255) COLLATE utf8_bin NOT NULL,
`name` text COLLATE utf8_bin,
`password` varchar(255) COLLATE utf8_bin NOT NULL,
`given_name` text COLLATE utf8_bin,
`title` text COLLATE utf8_bin NOT NULL,
`nationality` text COLLATE utf8_bin,
PRIMARY KEY (`user_id`),
KEY `user_id` (`user_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
编辑:
如果我删除除用户以外的所有表的连接,查询每次都有效。但是,一旦我添加任务连接,它就会中断。例如,此查询以与我发布的第一个查询相同的方式中断它:
SELECT
`fieldreps`.`user_id` AS `fieldreps.user_id`,
`fieldreps`.`availability_id` AS `fieldreps.availability_id`,
`fieldreps`.`applicant_type` AS `fieldreps.applicant_type`,
`fieldreps`.`license_number` AS `fieldreps.license_number`,
`fieldreps`.`license_exp` AS `fieldreps.license_exp`,
`fieldreps`.`license_state` AS `fieldreps.license_state`,
`fieldreps`.`car` AS `fieldreps.car`,
`fieldreps`.`authorized_worker` AS `fieldreps.authorized_worker`,
`fieldreps`.`restrictions` AS `fieldreps.restrictions`,
`fieldreps`.`night` AS `fieldreps.night`,
`fieldreps`.`day` AS `fieldreps.day`,
`fieldreps`.`longer` AS `fieldreps.longer`,
`fieldreps`.`commitment` AS `fieldreps.commitment`,
`fieldreps`.`travel_metro` AS `fieldreps.travel_metro`,
`fieldreps`.`travel_states` AS `fieldreps.travel_states`,
`fieldreps`.`big_employee` AS `fieldreps.big_employee`,
`fieldreps`.`employed` AS `fieldreps.employed`,
`fieldreps`.`retail` AS `fieldreps.retail`,
`fieldreps`.`status` AS `fieldreps.status`,
`fieldreps`.`start` AS `fieldreps.start`,
`fieldreps`.`sales` AS `fieldreps.sales`,
`fieldreps`.`study` AS `fieldreps.study`,
`fieldreps`.`relevant` AS `fieldreps.relevant`,
`fieldreps`.`experience` AS `fieldreps.experience`,
`fieldreps`.`claims` AS `fieldreps.claims`,
`fieldreps`.`education` AS `fieldreps.education`,
`fieldreps`.`degree_details` AS `fieldreps.degree_details`,
`fieldreps`.`degree_institution` AS `fieldreps.degree_institution`,
`fieldreps`.`other_training` AS `fieldreps.other_training`,
`fieldreps`.`jobs` AS `fieldreps.jobs`,
`fieldreps`.`current_training` AS `fieldreps.current_training`,
`fieldreps`.`interested` AS `fieldreps.interested`,
`fieldreps`.`achievements` AS `fieldreps.achievements`,
`fieldreps`.`passions` AS `fieldreps.passions`,
`fieldreps`.`ambitions` AS `fieldreps.ambitions`,
`fieldreps`.`max_travel_time` AS `fieldreps.max_travel_time`,
`fieldreps`.`creation_time` AS `fieldreps.creation_time`,
`fieldreps`.`resume` AS `fieldreps.resume`,
`users`.`user_id` AS `users.user_id`,
`users`.`email` AS `users.email`,
`users`.`name` AS `users.name`,
`users`.`password` AS `users.password`,
`users`.`given_name` AS `users.given_name`,
`users`.`title` AS `users.title`,
`users`.`nationality` AS `users.nationality`,
`tasks`.`task_id` AS `tasks.task_id`,
`tasks`.`recurrence_id` AS `tasks.recurrence_id`,
`tasks`.`parent_id` AS `tasks.parent_id`,
`tasks`.`user_id` AS `tasks.user_id`,
`tasks`.`campaign_id` AS `tasks.campaign_id`,
`tasks`.`location_id` AS `tasks.location_id`,
`tasks`.`title` AS `tasks.title`,
`tasks`.`description` AS `tasks.description`,
`tasks`.`earliest_start` AS `tasks.earliest_start`,
`tasks`.`deadline` AS `tasks.deadline`,
`tasks`.`km_estimate` AS `tasks.km_estimate`,
`tasks`.`estimate` AS `tasks.estimate`
FROM `fieldreps` AS `fieldreps`
LEFT JOIN `users` ON (`fieldreps`.`user_id` = `users`.`user_id`)
LEFT JOIN `tasks` ON (`fieldreps`.`user_id` = `tasks`.`user_id`)
WHERE `fieldreps`.`user_id` LIKE '5f5dc29c-832e-5646-9dc2-46d774bfb1ec'
最佳答案
连接和数据库整理不同。
关于mysql - 相同的 MySQL 查询在同一服务器上返回不同的结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8772632/
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。 我的查询需要显示值和选项的名称,而
在mysql中,如何计算一行中的非空单元格?我只想计算某些列之间的单元格,比如第 3-10 列之间的单元格。不是所有的列...同样,仅在该行中。 最佳答案 如果你想这样做,只能在 sql 中使用名称而
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗?更新问题,使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
我正在为版本7.6进行Elasticsearch查询 我的查询是这样的: { "query": { "bool": { "should": [ {
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗?更新问题,使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确? SELECT * FROM employees e WHERE NOT EXISTS (
我找到了很多关于我的问题的答案,但问题没有解决 我有表格,有数据,例如: Data 1 Data 2 Data 3
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
我有一个或多或少有效的查询(关于结果),但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。 所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
这是我第一次使用 Stack Overflow,所以我希望我以正确的方式提出这个问题。 我有 2 个 SQL 查询,我正在尝试比较和识别缺失值,尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
什么是动态 SQL 查询?何时需要使用动态 SQL 查询?我使用的是 SQL Server 2005。 最佳答案 这里有几篇文章: Introduction to Dynamic SQL Dynami
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
通过我的研究,我发现至少从 EF 4.1 开始,EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上,这对我来说非常有用,使用 Entity Framework 5 和 6。 但
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人,仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人 表格结构如下: 一个联系人可
如何让我输入数据库的信息在输入数据 5 分钟后自行更新? 假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
我正在尝试搜索正好是 4 位数字的 ID,我知道我需要使用 LENGTH() 字符串函数,但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (
我是一名优秀的程序员,十分优秀!