在内部联接查询中为一个字段选择唯一值 [英] Select Distinct value for one field on Inner Join Query

问题描述

我想从两个表创建夏季报告.一个表project_type另一个ffw

I want to create summery report from two tables. one table project_type another ffw

表ffw:

|-----------------------------------------------------------------------|
| ffw_id    | division_id   | district_id   | project_type_id | name
|-----------------------------------------------------------------------|
| 1         | 30            | 1             |     2           |myAddress
|-----------------------------------------------------------------------|
| 2         | 12            | 2             |     1           | Asdfads |
|-----------------------------------------------------------------------|
| 3         | 30            | 6             |     1           | kkkkk   |
|-----------------------------------------------------------------------|
|  ..       | ..            | ..            |     ..          | .....   |
|-----------------------------------------------------------------------|

表project_type:

|--------------------------------
| project_type_id | project_type | 
|--------------------------------|
| 1               | food         |
|--------------------------------|
| 2               | work         |
|--------------------------------|
| 3               | visit        |
|--------------------------------|
| ..              | ..           |
|--------------------------------|

在应用division_id条件后，我希望从这两个表获得的结果是

My desired result from that two tables after applying division_id condition will be

|-------------------------------------------|
| no        | project_type  | count         | 
|-------------------------------------------|
| 1         | food          | 2             |
|-------------------------------------------|
| 2         | work          | 1             |
|-------------------------------------------|
| 3         | visit         | .             |
|-------------------------------------------|
|  ..       | ..            | ..            |
|-------------------------------------------|

我正在尝试这段代码，但是它在while循环中显示重复的值

I am trying this code but it displaying duplicate value in while loop

$qry = "
    SELECT * FROM `project_type` 
    LEFT JOIN `ffw` 
        ON project_type.project_type_id = ffw.project_type_id
    WHERE 1
";

if (strlen($_POST["division_id"]) > 0 && $_POST["division_id"] != "0")
{
    $qry .= " AND division_id = '".$_POST["division_id"]."'";
}

$query = mysql_query($qry);

推荐答案

您可以使用group_by并获得以下计数:

You can use group_by and get count as below :

$qry="SELECT pt.project_type_id as no, pt.project_type, count(*) as count FROM project_type pt LEFT JOIN ffw ON pt.project_type_id=ffw.project_type_id";

if(strlen($_POST["division_id"])>0&&$_POST["division_id"]!="0")
{
     $qry.=" Where division_id='".$_POST["division_id"]."'";
}

$qry.=" GROUP BY pt.project_type";

这篇关于在内部联接查询中为一个字段选择唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！