I have 3 tables and the result table should have columns from all three tables based on the column user_id of first table.
我有3个表,结果表应该包含来自所有三个表的列,基于第一个表的user_id列。
1 个解决方案
#1
1
Assuming 10,john should be 15,john this looks like a very simple join. If the below model is incorrect please copy it, edit it and add to your question.
假设10,约翰应该是15,约翰这看起来像一个非常简单的连接。如果以下型号不正确,请复制,编辑并添加到您的问题中。
drop table if exists t1,t2,t3;
create table t1(u_id int, name varchar(10), prd int);
create table t2(u_id int, temp int);
create table t3(u_id int, Office int);
insert into t1 values (10,'ram',1), (15,'john',1),(20,'sat',1),(12,'peter',1);
insert into t2 values (10,32),(20,42),(15,25),(12,32);
insert into t3 values (20,56),(10,57),(15,56),(12,57);
This query
select t1.*,t2.*,t3.*
from t1
join t2 on t2.u_id = t1.u_id
join t3 on t3.U_id = t1.u_id
order by t1.u_id;
Produces this result
产生这个结果
+------+-------+------+------+------+------+--------+
| u_id | name | prd | u_id | temp | u_id | Office |
+------+-------+------+------+------+------+--------+
| 10 | ram | 1 | 10 | 32 | 10 | 57 |
| 12 | peter | 1 | 12 | 32 | 12 | 57 |
| 15 | john | 1 | 15 | 25 | 15 | 56 |
| 20 | sat | 1 | 20 | 42 | 20 | 56 |
+------+-------+------+------+------+------+--------+
4 rows in set (0.00 sec)
If the model looks like this
如果模型看起来像这样
drop table if exists t1,t2,t3;
create table t1(u_id int, name varchar(10), prd int);
create table t2(u_id int, temp int);
create table t3(u_id int, Office int);
insert into t1 values (10,'ram',1), (15,'john',1),(20,'sat',1),(12,'peter',1);
insert into t2 values (10,32),(20,42),(10,25),(12,32);
insert into t3 values (20,56),(10,57),(10,60),(10,56),(12,57);
You may need to simulate a full join
您可能需要模拟完整连接
select t1.u_id,t1.name,t2temp,t3office
from t1
join
(
select t2.u_id as t2id, t2.temp as t2temp, t3.u_id as t3uid,t3.office as t3office
from
(
select t2.*,if(t2.u_id <> @p ,@rn:=1,@rn:=@rn+1) rn,@p:=t2.u_id from t2 ,(select @rn:=0,@p:=0) rn order by t2.u_id
) t2
left outer join
(
select t3.*,if(t3.u_id <> @p1 ,@rn1:=1,@rn1:=@rn1+1) rn,@p1:=t3.u_id from t3 ,(select @rn1:=0,@p1:=0) rn1 order by t3.u_id
) t3 on t3.u_id = t2.u_id and t2.rn = t3.rn
union all
select t2.u_id as t2id, t2.temp as t2temp, t3.u_id as t3uid,t3.office as t3office
from
(
select t2.*,if(t2.u_id <> @p ,@rn:=1,@rn:=@rn+1) rn,@p:=t2.u_id from t2 ,(select @rn:=0,@p:=0) rn order by t2.u_id
) t2
right outer join
(
select t3.*,if(t3.u_id <> @p1 ,@rn1:=1,@rn1:=@rn1+1) rn,@p1:=t3.u_id from t3 ,(select @rn1:=0,@p1:=0) rn1 order by t3.u_id
) t3 on t3.u_id = t2.u_id and t2.rn = t3.rn
where t2.rn is null
) z on t1.u_id = t2id or t1.u_id = t3uid
order by t1.u_id
+------+-------+--------+----------+
| u_id | name | t2temp | t3office |
+------+-------+--------+----------+
| 10 | ram | NULL | 56 |
| 10 | ram | 32 | 57 |
| 10 | ram | 25 | 60 |
| 12 | peter | 32 | 57 |
| 20 | sat | 42 | 56 |
+------+-------+--------+----------+
5 rows in set (0.00 sec)
#1
1
Assuming 10,john should be 15,john this looks like a very simple join. If the below model is incorrect please copy it, edit it and add to your question.
假设10,约翰应该是15,约翰这看起来像一个非常简单的连接。如果以下型号不正确,请复制,编辑并添加到您的问题中。
drop table if exists t1,t2,t3;
create table t1(u_id int, name varchar(10), prd int);
create table t2(u_id int, temp int);
create table t3(u_id int, Office int);
insert into t1 values (10,'ram',1), (15,'john',1),(20,'sat',1),(12,'peter',1);
insert into t2 values (10,32),(20,42),(15,25),(12,32);
insert into t3 values (20,56),(10,57),(15,56),(12,57);
This query
select t1.*,t2.*,t3.*
from t1
join t2 on t2.u_id = t1.u_id
join t3 on t3.U_id = t1.u_id
order by t1.u_id;
Produces this result
产生这个结果
+------+-------+------+------+------+------+--------+
| u_id | name | prd | u_id | temp | u_id | Office |
+------+-------+------+------+------+------+--------+
| 10 | ram | 1 | 10 | 32 | 10 | 57 |
| 12 | peter | 1 | 12 | 32 | 12 | 57 |
| 15 | john | 1 | 15 | 25 | 15 | 56 |
| 20 | sat | 1 | 20 | 42 | 20 | 56 |
+------+-------+------+------+------+------+--------+
4 rows in set (0.00 sec)
If the model looks like this
如果模型看起来像这样
drop table if exists t1,t2,t3;
create table t1(u_id int, name varchar(10), prd int);
create table t2(u_id int, temp int);
create table t3(u_id int, Office int);
insert into t1 values (10,'ram',1), (15,'john',1),(20,'sat',1),(12,'peter',1);
insert into t2 values (10,32),(20,42),(10,25),(12,32);
insert into t3 values (20,56),(10,57),(10,60),(10,56),(12,57);
You may need to simulate a full join
您可能需要模拟完整连接
select t1.u_id,t1.name,t2temp,t3office
from t1
join
(
select t2.u_id as t2id, t2.temp as t2temp, t3.u_id as t3uid,t3.office as t3office
from
(
select t2.*,if(t2.u_id <> @p ,@rn:=1,@rn:=@rn+1) rn,@p:=t2.u_id from t2 ,(select @rn:=0,@p:=0) rn order by t2.u_id
) t2
left outer join
(
select t3.*,if(t3.u_id <> @p1 ,@rn1:=1,@rn1:=@rn1+1) rn,@p1:=t3.u_id from t3 ,(select @rn1:=0,@p1:=0) rn1 order by t3.u_id
) t3 on t3.u_id = t2.u_id and t2.rn = t3.rn
union all
select t2.u_id as t2id, t2.temp as t2temp, t3.u_id as t3uid,t3.office as t3office
from
(
select t2.*,if(t2.u_id <> @p ,@rn:=1,@rn:=@rn+1) rn,@p:=t2.u_id from t2 ,(select @rn:=0,@p:=0) rn order by t2.u_id
) t2
right outer join
(
select t3.*,if(t3.u_id <> @p1 ,@rn1:=1,@rn1:=@rn1+1) rn,@p1:=t3.u_id from t3 ,(select @rn1:=0,@p1:=0) rn1 order by t3.u_id
) t3 on t3.u_id = t2.u_id and t2.rn = t3.rn
where t2.rn is null
) z on t1.u_id = t2id or t1.u_id = t3uid
order by t1.u_id
+------+-------+--------+----------+
| u_id | name | t2temp | t3office |
+------+-------+--------+----------+
| 10 | ram | NULL | 56 |
| 10 | ram | 32 | 57 |
| 10 | ram | 25 | 60 |
| 12 | peter | 32 | 57 |
| 20 | sat | 42 | 56 |
+------+-------+--------+----------+
5 rows in set (0.00 sec)