如果条件不适用IN条款

Question

我有一个查询。在这个查询中，我使用子查询从具有不同条件的同一个表中获取数据，在主查询中我提到了用于在子查询中获取数据的ID，并提出了一个条件，即在具有ID的主查询中考虑的值不应为空。如果我在主查询中使用带有IN子句的单个ID，我的查询可以正常工作，但如果我在主要查询中使用多个ID并添加值不应为空的子句，则查询不会给我所需的结果。这是我的查询

SELECT e.id AS `Personal Number`,
e.date AS `Date`,
CONCAT(ep.firstname,' ',ep.lastname) AS `Employee Name`,
IF(ep.sex='M','Male','Female') AS sex,
DATE_FORMAT(p.birthdate,'%m/%d/%Y')AS birthdate,
(SELECT `value` FROM employee_data WHERE history=87 AND DATE=e.date) AS `A`,
(SELECT `value` FROM employee_data WHERE history=603 AND DATE=e.date) AS `B`,
(SELECT `value` FROM employee_data WHERE history=82 AND DATE=e.date) AS `C`,
(SELECT `value` FROM employee_data WHERE history=86 AND DATE=e.date) AS `D`
FROM tbl e  
INNER JOIN employee ep ON e.id = ep.id
INNER JOIN tbl2 ap ON ap.date=e.date
INNER JOIN employee_data AS phd ON e.date = phd.date   
WHERE (phd.history IN(82,87,603,86) AND phd.value!='')  AND ap.date BETWEEN '2013-01-01' AND '2013-09-01'AND e.status!='cancelled'. 

我不知道该怎么处理这个问题。有人能帮忙吗。提前感谢

Answer 1

由于您在select子句中使用filter获取值，因此在select子句中将执行分组并获取max（）值。

max (SELECT `value` FROM employee_data WHERE history=87 AND DATE=e.date) AS `A`,
max (SELECT `value` FROM employee_data WHERE history=603 AND encounter_nr=e.encounter_nr)     AS `B`,
max (SELECT `value` FROM employee_data WHERE history=82 AND encounter_nr=e.encounter_nr) AS `C`,
max  (SELECT `value` FROM employee_data WHERE history=86 AND encounter_nr=e.encounter_nr) AS `D`
FROM

Answer 2

这是您的问题。当您有where子句时，您的查询将生成四行。但是，我怀疑你只是期望一行，或者至少，每个id只有一行。

我想这就是你想要的查询：

SELECT e.id AS `Personal Number`, e.date AS `Date`,
       CONCAT(ep.firstname,' ',ep.lastname) AS `Employee Name`,
       IF(ep.sex='M','Male','Female') AS sex,
       DATE_FORMAT(p.birthdate,'%m/%d/%Y') AS birthdate,
       max(case when history = 87 then value end) as A,
       max(case when history = 603 then value end) as B,
       max(case when history = 82 then value end) as C,
       max(case when history = 86 then value end) as D
FROM tbl e INNER JOIN
     employee ep
     ON e.id = ep.id INNER JOIN
     tbl2 ap ON ap.date = e.date INNER JOIN
     employee_data phd
     ON e.date = phd.date   
WHERE phd.history IN (82, 87, 603, 86) AND phd.value <> '' AND
      ap.date BETWEEN '2013-01-01' AND '2013-09-01' AND
      e.status <> 'cancelled'
group by e.id;

这应该为每个员工返回一行。

编辑：

我突然想到，您可能不想要聚合。您可以按照原来的方法，在外部查询中删除employee_data的联接

SELECT e.id AS `Personal Number`, e.date AS `Date`,
       CONCAT(ep.firstname,' ',ep.lastname) AS `Employee Name`,
       IF(ep.sex='M','Male','Female') AS sex,
       DATE_FORMAT(p.birthdate,'%m/%d/%Y') AS birthdate,
       (SELECT `value` FROM employee_data WHERE history=87 AND DATE=e.date) AS `A`,
       (SELECT `value` FROM employee_data WHERE history=603 AND DATE=e.date) AS `B`,
       (SELECT `value` FROM employee_data WHERE history=82 AND DATE=e.date) AS `C`,
       (SELECT `value` FROM employee_data WHERE history=86 AND DATE=e.date) AS `D`
FROM tbl e INNER JOIN
     employee ep
     ON e.id = ep.id INNER JOIN
     tbl2 ap ON ap.date = e.date
WHERE ap.date BETWEEN '2013-01-01' AND '2013-09-01'AND e.status <> 'cancelled'
HAVING A <> '' and B <> '' and C <> '' and D <> '';

having子句是MySQL的一个技巧，它允许您在from子句中引用列别名。在这种情况下，它并不意味着聚合。

如果你有一个索引employee_data(history, date)，你会这样做。