使用sql按族搜索表

x8goxv8g 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(298)

我把这张table命名为 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 我想要这张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 |
|  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 |
+-----+--------------------+----------+--------------+------+

你看到身份证号码了吗 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;

还有家人的调查结果。

sql mysql duplicates Date php

来源：https://stackoverflow.com/questions/53581426/searching-the-table-by-family-using-sql

2条答案

按热度按时间

6tdlim6h1#

选择的问题是，您还需要使用相同的用户名筛选子查询，例如：

select e.* 
from table e 
where e.user ='u123' and e.family in 
(
    select distinct e2.family 
    from table e2 
    where 
        e2.user ='u123' and 
        e2.date >= '2018-01-06'
) 
order by e.family

select e.*
from table e 
where e.user ='u123' and e.family in 
(
    select distinct e2.family 
    from table e2 
    where 
        e2.user ='u123' and 
        e2.date >= '2018-01-06' and
        e2.money like '%transf%'
)
order by e.family

赞(0）回复(0）举报 2021-06-18

xggvc2p62#

查询的问题是用于 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;

在测试仪上测试

赞(0）回复(0）举报 2021-06-18

我来回答

使用sql按族搜索表

2条答案

相关问题

热门标签

最新问答