dongliling6336 2016-06-11 23:38
浏览 11
已采纳

将2列连接到1列

I need to connect 2 columns to 1 column, but I can't figure it out how to connect those columns. This is how it looks like:

Table(appointment)

-------------------------------------------
| id | home | salesman | buyer |   date   |
| 1  |  3   |    2     |   4   | 12-6-2016|
| 2  |  1   |    1     |   3   | 15-6-2016|
| 3  |  2   |    5     |   6   | 20-6-2016|
-------------------------------------------

Table(person)

---------------------------------------------------------
| id | name |      email     | phonenumber | permission |
| 1  | John | John@gmail.com |  12345678   |     1      |
| 2  | Jack | Jack@gmail.com |  12345678   |     1      |
| 3  | Henk | Henk@gmail.com |  12345678   |     0      |
| 4  | Mike | Mike@gmail.com |  12345678   |     0      |
| 5  | Tom  | Tom@gmail.com  |  12345678   |     1      |
| 6  | Ben  | Ben@gmail.com  |  12345678   |     0      |
---------------------------------------------------------

Table(home)

--------------------------------------
| id | salesman |  price  |  city    |
| 1  |    2     |  123000 | London   |
| 2  |    1     |  123000 | New York |
| 3  |    5     |  123000 |  Paris   |
--------------------------------------

This is how I want to see it on my php page:

-------------------------------------------------
| home            | salesman | buyer |   date   |
| Home in Paris   |   Jack   |  Mike | 12-6-2016|
| Home in London  |   John   |  Henk | 15-6-2016|
| Home in New York|   Tom    |  Ben  | 20-6-2016|
-------------------------------------------------

People with permission 1 are salesman, permission 0 are buyers.

So my question is, how can I change the numbers to the correct value? Without change the number in table appointment to values in the database...

I don't know much about SQL, so could someone please come with a solution that isn't an example and works with my tables and columns?

Edit:

Table(afspraak)

-------------------------------------------
| id | huis | verkoper | koper |  datum   |
| 1  |  3   |    2     |   4   | 12-6-2016|
| 2  |  1   |    1     |   3   | 15-6-2016|
| 3  |  2   |    5     |   6   | 20-6-2016|
-------------------------------------------

Table(persoon)

---------------------------------------------------------
| id | naam |      email     | phonenumber | rechten    |
| 1  | John | John@gmail.com |  12345678   |     1      |
| 2  | Jack | Jack@gmail.com |  12345678   |     1      |
| 3  | Henk | Henk@gmail.com |  12345678   |     0      |
| 4  | Mike | Mike@gmail.com |  12345678   |     0      |
| 5  | Tom  | Tom@gmail.com  |  12345678   |     1      |
| 6  | Ben  | Ben@gmail.com  |  12345678   |     0      |
---------------------------------------------------------

Table(huis)

--------------------------------------
| id | verkoper |  prijs  |   stad   |
| 1  |    2     |  123000 | London   |
| 2  |    1     |  123000 | New York |
| 3  |    5     |  123000 |  Paris   |
--------------------------------------

My php code:

include('config.php');
$getAfspraak = "SELECT CONCAT('Home in ', HO.stad) AS Home,
   SA.`naam` AS verkoper,
   BU.`naam` AS koper,
   AP.`datum`
FROM afspraak AP
INNER JOIN huis HO ON HO.id = AP.huis
INNER JOIN persoon SA ON SA.id = AP.verkoper AND SA.rechten = 1
INNER JOIN persoon BU ON BU.id = AP.koper AND BU.rechten = 0";
//Inner join for huis   SELECT afspraak.huis, huis.stad FROM afspraak INNER JOIN huis ON afspraak.huis = huis.id; This query should show "huis in [stad]" it means, a home in London.
$dataAfspraak = mysqli_query($con, $getAfspraak) or die(mysqli_error($con));



<table cellspacing=1 border=0 width=100%>
    <tr>
        <th>Koper</th>
        <th>Verkoper</th>
        <th>Huis</th>
        <th>Datum</th>
    </tr>
<?php while($resAfspraak = mysqli_fetch_assoc($dataAfspraak)): ?>
    <tr>
        <td><?php echo $resAfspraak['koper']?></td> <!-- value is naam if the row has 0 at rechten -->
        <td><?php echo $resAfspraak['verkoper']?></td> <!-- value is naam if the row has 1 at rechten -->
        <td><?php echo $resAfspraak['huis']?></td>
        <td><?php echo $resAfspraak['datum']?></td>
    </tr>
<?php endwhile;?>

  • 写回答

1条回答 默认 最新

  • dsns47611 2016-06-12 00:20
    关注

    By the below JOIN and CONCAT query you can achieve your expected result:

    SELECT CONCAT('Home in ', HO.city) AS Home,
           SA.`Name` AS Salesman,
           BU.`Name` AS Buyer,
           AP.`Date`
    FROM Appointment AP
    INNER JOIN Home HO ON HO.Id = AP.Home
    INNER JOIN Person SA ON SA.Id = AP.Salesman AND SA.Permission = 1
    INNER JOIN Person BU ON BU.Id = AP.Buyer AND BU.Permission = 0
    

    UPDATE:

    You no need to get the huis value from separate join. The below query returns the koper, verkoper, huis and datum values.

    $getAfspraak = "SELECT BU.naam AS koper, SA.naam AS verkoper, CONCAT('Huis in ', HO.stad) AS Huis, AP.datum 
    FROM afspraak AP 
    INNER JOIN huis HO ON HO.id = AP.huis 
    INNER JOIN persoon SA ON SA.id = AP.verkoper AND SA.rechten = 1 
    INNER JOIN persoon BU ON BU.id = AP.koper AND BU.rechten = 0";
    $dataAfspraak = mysqli_query($con, $getAfspraak) or die(mysqli_error($con));
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常