php - 使用sql按家庭搜索表

Question

我有这个名为 table 的表:

+-----+--------------------+----------+--------------+------+
| id  |       money        |  family  |     date     | user |
+-----+--------------------+----------+--------------+------+
|  1  |  credit card       |       1  | 2018-01-04   | U123 |
|  2  |  direct transfert  |       1  | 2018-01-04   | U123 |
|  3  |  Wire transfert    |       1  | 2018-01-06   | U123 |
|  4  |  Exchange          |       2  | 2018-01-03   | U123 |
|  5  |  free              |       2  | 2018-01-03   | U123 |
|  6  |  other             |       3  | 2018-01-08   | U123 |
+-----+--------------------+----------+--------------+------+

我想要的是永远靠家人来得到结果。例如，如果我搜索 date >= 2018-01-06 我想得到这个表:

+-----+--------------------+----------+--------------+------+
| id  |       money        |  family  |     date     | user |
+-----+--------------------+----------+--------------+------+
|  1  |  credit card       |       1  | 2018-01-04   | U123 |
|  2  |  direct transfert  |       1  | 2018-01-04   | U123 |
|  3  |  Wire transfert    |       1  | 2018-01-06   | U123 |
|  6  |  other             |       3  | 2018-01-08   | U123 |
+-----+--------------------+----------+--------------+------+

我试过这个查询:

SELECT e.* 
  FROM table e 
 WHERE e.user ='U123' AND e.family IN 
(
    SELECT e2.family 
      FROM table e2 
     WHERE (e2.date >= "2018-01-06") 
) 
 ORDER BY family;

但我收到了这个结果:

+-----+--------------------+----------+--------------+------+
| id  |       money        |  family  |     date     | user |
+-----+--------------------+----------+--------------+------+
|  1  |  credit card       |       1  | 2018-01-04   | U123 |
|  2  |  direct transfert  |       1  | 2018-01-04   | U123 |
|  3  |  Wire transfert    |       1  | 2018-01-06   | U123 |
|  4  |  Exchange          |       2  | 2018-01-03   | U123 |
|  5  |  free              |       2  | 2018-01-03   | U123 |
|  6  |  other             |       3  | 2018-01-08   | U123 |
+-----+--------------------+----------+--------------+------+

您看到 ID 号 4 和 5 不应该在那里。

此外，我希望能够添加其他类似的条件:

SELECT e.*
  FROM table e 
 WHERE e.user ='U123' AND e.family IN 
(
    SELECT e2.family 
      FROM table e2 
     WHERE (e2.date >= "2018-01-06") 
       AND (e2.money like "%transf%")
)
 ORDER BY family;

还有家庭的结果。

Answer 1

您的查询的问题是用于 IN 的子查询也从其他用户中选择了“家庭”。

您可以通过包含用户来解决这个问题。

SELECT *
FROM yourtable e 
WHERE e.user = 'U123'
  AND (e.user, e.family) IN (
    SELECT DISTINCT e2.user, e2.family 
    FROM yourtable e2 
    WHERE e2.date >= '2018-01-06'
  )
ORDER BY family;

但在这种情况下，最好改用 EXISTS。
一个链接到用户和家庭的。

然后将这些额外条件放入 EXISTS 查询中。

SELECT *
FROM yourtable e 
WHERE e.user = 'U123'
  AND EXISTS (
    SELECT 1
    FROM yourtable e2
    WHERE e2.user = e.user
      AND e2.family = e.family
      AND e2.date >= '2018-01-06'
      -- AND e2.money LIKE '%transf%'
  )
ORDER BY family;

在 RexTester 上测试 here