MySQL JOIN-左表中的重复结果返回NULL [英] MySQL JOIN - Return NULL for duplicate results in left table

问题描述

我相信这是一件非常简单的事情，我发誓我以前做过，但是我不记得怎么做.

I believe this is a pretty simple thing, and I swear I've done it before but I can't remember how.

所以说我有一对多的关系.我想加入两个表，但不允许左侧表重复.

So let's say I have a one-to-many relationship. I want to JOIN the two tables, but not allow duplicates for the left table.

SQLFIDDLE

SQLFIDDLE

因此，根据上述SQLFiddle，我的结果将是:

So based on the above SQLFiddle, my results would be:

categories.title  |  items.NAME  |  items.category_id
-----------------------------------------------------
red               | apple        | 1
red               | car          | 1
red               | paper        | 1
yellow            | lego         | 2
yellow            | banana       | 2
blue              | pen          | 3

我希望它是:

categories.title  |  items.NAME  |  items.category_id
-----------------------------------------------------
red               | apple        | 1
NULL              | car          | 1
NULL              | paper        | 1
yellow            | lego         | 2
NULL              | banana       | 2
blue              | pen          | 3

我的推理是这样，我可以轻松地遍历结果，而无需使用PHP进行任何进一步处理.

My reasoning is that this way, I can easily loop over the results without having to do any further processing with PHP.

推荐答案

您可以将值替换为以下内容:

You can replace the values with something like this:

select 
  case when rownum = 1 then title else null end title,
  name,
  category_id
from
(
  SELECT c.title, 
    i.name, 
    i.category_id,
    @row:=(case when @prev=title and @precat=category_id 
           then @row else 0 end) + 1 as rownum,
    @prev:=title ptitle,
    @precat:=category_id pcat
  FROM items AS i
  INNER JOIN categories AS c 
    ON c.id = i.category_id
   order by i.category_id, c.title
) src
order by category_id, rownum

请参见带演示的SQL提琴

结果是:

|  TITLE |   NAME | CATEGORY_ID |
---------------------------------
|    red |  apple |           1 |
| (null) |    car |           1 |
| (null) |  paper |           1 |
| yellow |   lego |           2 |
| (null) | banana |           2 |
|   blue |    pen |           3 |

这篇关于MySQL JOIN-左表中的重复结果返回NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！