msyql子查询
我使用MySQL的比较两个字段的值,并有3个表,像这样:msyql子查询
Create Table users (
firstName VARCHAR,
lastName VARCHAR,
userName VARCHAR,
email VARCHAR,
created DATETIME, etc.
Create Table data_2013 (
uid VARCHAR,
d1 INT,
d2 INT,
d3 INT, etc
Create Table data_2016 (
uid VARCHAR,
d1 INT,
d2 INT,
d3 INT, etc
的
uid
在两个数据表的userName
场比赛中users
表每个用户在
users
表中存在两次(或更多),但始终匹配firstName
和lastName
。这些用户的子集(约100个)在“data_xxxx”表中都有数据。
对于2013年的数据,
userName
是一个8个字符的字符串。对于2016年的数据,userName
是他们当前的电子邮件地址(不一定与2013年相同)。
我可以得到所有谁拥有2016年的数据瓦特/这样的查询用户:
SELECT firstName,lastName,userName
FROM users
WHERE created > '2016-01-01'
AND userName IN(SELECT uid FROM data_2016)`
但我现在想的是,会给我的用户列表查询,通过userName
,有2013年的数据。但是,如我所说,userName
(或uid
)不匹配,但firstName
和lastName
值应该。
我需要这样的事情,在伪代码:
SELECT userName
FROM users
WHERE created < '2014-01-01'
and firstName,lastName IN (
SELECT firstName,lastName
FROM users
WHERE created > '2016-01-01'
AND userName IN(SELECT uid FROM data_2016))
我敢肯定,联合或连接是答案,但我不能弄明白。
任何提示?
由于
EDIT
下面是从users
表中的一些示例性数据:
+--------+---------------------+----+----+----+----+----+ | uid | created | d1 | d2 | d3 | d4 | d5 | +--------+---------------------+----+----+----+----+----+ | rwhite | 2013-08-05 13:24:24 | 38 | 31 | 7 | 22 | 46 | +--------+---------------------+----+----+----+----+----+
以上用户的的实施例:上述用户的2013数据的
+------------------------+-----------+----------+------------------------+---------------------+ | userName | firstName | lastName | email | created | +------------------------+-----------+----------+------------------------+---------------------+ | rwhite | ROBERT | WHITE | [email protected] | 2013-08-05 13:13:23 | | [email protected] | Robert | White | [email protected] | 2016-10-23 20:26:52 | +------------------------+-----------+----------+------------------------+---------------------+
实施例2016年d ATA:
+--------------------+---------------------+----+----+----+----+----+ | uid | created | d1 | d2 | d3 | d4 | d5 | +--------------------+---------------------+----+----+----+----+----+ | [email protected] | 2016-10-24 12:37:29 | 38 | 48 | 59 | 71 | 17 | +--------------------+---------------------+----+----+----+----+----+
EDIT2
我忘了,我有对某些客户的额外数据的第4个表:
Create Table users_custA (
userName VARCHAR,
id_num VARCHAR,
etc.
)
和示例该表中的同一用户的:
+--------------------+-----------+
| userName | id_num |
+--------------------+-----------+
| rwhite | N0|
| [email protected] | N0|
+--------------------+-----------+
This id_num
is guarant对一个给定的人来说是唯一的(即,R White是一个单人,在users_custA
表中有两个条目)。
问题依然如此:我如何构建一个查询来生成在两个data_xxxx表中都有数据的用户名列表?
一般来说,期望名称在时间上是独一无二且一致的,但如果您确信数据中存在这种情况,那么您可以像这样调整您的查询(假设您有案例不敏感的排序):
SELECT userName
FROM users As u2013
WHERE created >= '2013-01-01'
AND created < '2014-01-01'
AND EXISTS (
SELECT 1
FROM users As u2016
WHERE created >= '2016-01-01'
AND created < '2017-01-01'
AND u2016.FirstName = u2013.FirstName
AND u2016.LastName = u2013.LastName
AND EXISTS (SELECT 1 FROM data_2016 WHERE data_2016.uid = u2016.userName));
你会使用WHERE EXISTS
而不是WHERE ... IN
因为mysql不支持WHERE (col1, col2) IN ...
,它只支持它单列or so I understand.
编辑
您可以整合您users_custA
表以这种方式获得更一定的匹配:
Select *
From users_custA
Where id_num In (
SELECT id_num
FROM (
SELECT DISTINCT id_num
FROM users As u
JOIN users_custA As a On u.userName = a.userName
WHERE created >= '2013-01-01'
AND created < '2014-01-01'
UNION ALL
SELECT DISTINCT id_num
FROM users As u
JOIN users_custA As a On u.userName = a.userName
WHERE created >= '2016-01-01'
AND created < '2017-01-01') As union_subquery
GROUP BY id_num
HAVING COUNT(*) = 2);
做你data_中*表有名字和姓氏字段? – Nerdwood
向我们展示一些样品会更有帮助。 – Blank
data_ *表只有以下字段:uid,d1..dN,创建 – atreyu