mysql - 如何在单行中显示相似 ID 的多行记录？

Question

我有两个表 user 和 work。工作表有多个用户的工作经验。我必须根据开始日期检索最近的 2 家公司。

条件:

1.如果Work Table有多个用户Id的记录，取最近的2条记录单行显示(如User_id = 1)

2.如果工作表只有一条记录是最近的公司记录，第二条记录应该是“NULL”(例如user_id = 3)。

    user
    user_id  First_name  Last_name
      1        AAA         BBB
      2        PPP         QQQ
      3        SSS         RRR

    work
    user_id recent_company position start_year end_year
        1        ABC          CCC     2014       2015
        1        PQR          DDD     2013       2014
        1        MNO          EEE     2012       2013
        1        MNO          EEE     0000       0000
        2        XYZ          TTT     2008       2009
        2        IJK          MMM     2005       2008
        3        QRS          ZZZ     2001       2002

我试过最近的公司记录 1 :

select u.user_id,u.first_name,u.last_name,uw.recent_company1,uw.position1,uw.start_year,uw.end_year from muser u
left join
(SELECT user_id,recent_company,position,MAX(start_year) as start_year,end_year 
FROM work 
group by user_id 
order by user_id) uw ON u.user_id =uw.user_id

我在开始年份查询最近第一家公司的结果:

user_id  First_name  Last_name     recent_company1   
  1        AAA         BBB            ABC                          
  2        PPP         QQQ            XYZ                           
  3        SSS         RRR            QRS             

position1    start_year1    end_year1
   CCC         2014           2015
   TTT         2008           2009
   ZZZ         2001           2002

最近第一家和第二家公司在 start_year 基础上的要求输出:

user_id  First_name  Last_name   recent_company1   position1    
  1        AAA         BBB          ABC             CCC                 
  2        PPP         QQQ          XYZ             TTT                
  3        SSS         RRR          QRS             ZZZ                 

start_year1    end_year1
    2014         2015
    2008         2009
    2001         2002

   recent_company2   position2   start_year2    end_year2
      PQR               DDD         2013          2014
      IJK               MMM         2005          2008
      NULL              NULL        NULL          NULL

Answer 1

我用 php 脚本做了同样的事情。我也在寻找使用 sql 查询的答案 .. 这样它就可以在 sql 查询中完成 ..

  function tag_listing() {
    echo 'Row Number' . ',' . 'TagID' . ',' . 'Status' . ',' . 'UserName' . ',' . 'Country' . ',' . 'PostCode' . ',';
    $array = array('First', 'Second', 'Third', 'Fourth', 'Fifth', 'Sixth', 'Seventh', 'Eighth', 'Ninth', 'Tenth');
    $entityName = "Allocation Entity";
    $entity_type = "Entity Type";
    $allocDate = "Allocation Date";
    $sqlCount = "SELECT count(*) as total from tag_allocation  group by id_tag HAVING COUNT(*) > 1
                 ORDER BY COUNT(*) DESC";
    $countQuery = $this->db->query($sqlCount);
    foreach ($countQuery->result() as $NewTotal) {
        $resRows = $NewTotal->total;
        $item = array_slice($array, 0, $resRows);
        foreach ($item as $items) {
            echo $items . ' ' . $entityName . ',';
            echo $items . ' ' . $entity_type . ',';
            echo $items . ' ' . $allocDate . ',';
        }
        break;
    }
    $query = $this->db->query("select  id_tag_id, IT.id_tag, ITS.status,
    CASE WHEN firstname IS NULL THEN '' ELSE CONCAT(firstname,' ',lastname) END as Username,
    CASE WHEN U.country IS NULL THEN '' ELSE U.country END as Country,
    CASE WHEN U.postcode IS NULL THEN '' ELSE U.postcode END as postcode,
    CASE WHEN E.entity_name IS NULL THEN '' ELSE E.entity_name END as entity_name,
    ET.entity_type, TA.allocation_date, TA.allocation_type from id_tag IT
    INNER JOIN id_tag_status ITS ON IT.id_tag_status_id = ITS.id_tag_status_id
    RIGHT JOIN tag_allocation TA ON IT.id_tag = TA.id_tag
    INNER JOIN entity_type ET ON ET.entity_type_id = TA.allocation_type
    LEFT JOIN allocation_entity E ON E.entity_id = TA.entity_id
    LEFT JOIN user U ON U.user_id = IT.user_id
    order by IT.id_tag, TA.allocation_date asc        ");
    $IDTAG = '';
    $rowCount = 0;
    foreach ($query->result() as $row) {
        $newID = $row->id_tag;
        if ($IDTAG != $newID) {
            echo "\r\n";
        }
        if ($IDTAG == $newID) {
            echo $entityName = $row->entity_name . ',' . $entityType = $row->entity_type . ',' . $allocationDate = $row->allocation_date . ',';
        } else {
            $rowCount = $rowCount + 1;
            $IDTAG = $row->id_tag;
            echo $rowCount . ',' . $idTag = $row->id_tag . ',' . $status = $row->status . ',' . $Username = $row->Username . ',' . $Country = $row->Country . ',' . $postcode = $row->postcode . ',' . $entityName = $row->entity_name . ',' . $entityType = $row->entity_type . ',' . $allocationDate = $row->allocation_date . ',';
        }
    }
    exit();
}

希望对您有所帮助..试试这个，您会明白的..谢谢...