Combined Query of two SQL tables and database query under yii, Combined Query yii

Combined Query of two SQL tables and database query under yii, Combined Query yii
Join on


For example, two table queries:

select  u.username, t.title  from user u join task t on u.id = t.id; 

Here, user u is used to simplify the join Operation of table names. If it is added to other tables, on is used to represent the query condition u. username is used to represent the username field in the user table.


The same principle applies to multi-table queries.
This is an SQL statement:

select * from user u join task t on u.id = t.idjoin task_user tu on tu.id = t.id

This method uses query under the yii framework:

$query = new Query();        $query->select('title,content,task.enable,task.id');        $query->from('task');        $query->leftJoin('task_user_rel', 'task.id= task_user_rel.task_id');        $query->leftJoin('user', 'task_user_rel.user_id = user.id');        $query->leftJoin('org_user_rel', 'user.id = org_user_rel.user_id');        $query->leftJoin('org', 'org_user_rel.org_id =org.id');        $query->where([            'org.id' => $orgModel->id        ]);$data = $query->all();$data = $query->one();

In fact, there are many database query methods in the yii framework, such:

   $data = JdCrmSmsUserModel::find()->where([            'vender_id' => [               $venderId            ]        ])->all();

Or use findBySql (), findOne (), findAll (), and so on to replace find () for query.
Combined Query of two SQL database tables

Two steps are involved.
Step 1: first convert the data in Table 2 from a horizontal table to a vertical table. The name field is empty first, and the second step is used for matching. Some users only have data for 8 to 9 months. In this case, they can write the data they do not have as 0. for example, if user a does not have data for December, the corresponding month12 field data is 0, and some correspond to dosage. Code:
Insert into table 3 (number, month1, mon2, month3. ...... month12)
Select number,
Sum (case when month = 1 then dosage else 0 end) month1,
Sum (case when month = 2 then dosage else 0 end) mon2,
........
Sum (case when month = 12 then dosage else 0 end) month12
Group by number
Note: month = 1 is written according to the field type in Table 2. For example, if the data of month is 'february ', it must be written as month = 'february '. 1 to 12.
Step 2: Match
Update Table 3
Set a. name = B. name
From table 1 B
Where a. number = B. number

How to associate SQL queries with two tables in two databases

To associate the data of the two databases, you need to add a prefix, that is, the database, user, table name, and so on. Simply press the query statement they wrote above. Otherwise, it won't work, another one is very stupid. If you export data from one database to another, the two tables can be directly associated.