暑假期间去实验室公司实习，然后碰到一个问题，其实也就是SQL语句的问题。
背景扯一下
用数据库查找数据时，就是将商品的特定属性找出来，简化一下就是有两张表，一张表记录商品的各种属性，另一张表则是记下商品的图片，表结构简化如下
注：数据皆为瞎掰
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
+
| id | name | price |
|
| 0 | product1 | 3 |
| 1 | product2 | 4 |
| 2 | product3 | 6.7 |
| 3 | product4 | 5.84 |
+
+
| image_id | product_id | image_url |
|
| 1 | 0 | ./image/food1.jpg |
| 2 | 0 | ./image/food2.jpg |
| 3 | 0 | ./image/food3.jpg |
| 4 | 3 | ./image/food2.jpg |
| 5 | 2 | ./image/food34.jpg |
| 6 | 1 | ./image/food3327.jpg |
| 7 | 1 | ./image/food9327.jpg |
| 8 | 3 | ./image/foodff27.jpg |
+
|
问题描述
现在的要求就是查找商品并且附带一张图片出来，这种情况下多半用于所有商品列表的情况，只要一张图片就ok了。
一开始我写的sql语句是
1
| SELECT t1.*, t2.* FROM product AS t1 LEFT JOIN product_image AS t2 ON t1.id=t2.product_id;
|
不难想象以下搜索结果
1
2
3
4
5
6
7
8
9
10
11
12
| +
| id | name | price | image_id | product_id | image_url |
|
| 0 | product1 | 3 | 1 | 0 | ./image/food1.jpg |
| 0 | product1 | 3 | 2 | 0 | ./image/food2.jpg |
| 0 | product1 | 3 | 3 | 0 | ./image/food3.jpg |
| 3 | product4 | 5.84 | 4 | 3 | ./image/food2.jpg |
| 2 | product3 | 6.7 | 5 | 2 | ./image/food34.jpg |
| 1 | product2 | 4 | 6 | 1 | ./image/food3327.jpg |
| 1 | product2 | 4 | 7 | 1 | ./image/food9327.jpg |
| 3 | product4 | 5.84 | 8 | 3 | ./image/foodff27.jpg |
+
|
但是明显一些数据重复了，而且我只想要一张图片就够了。
解决方法
一开始我就像试试看加上distinct关键字，也就是如下语句
1
| SELECT DISTINCT t1.id, t1.name, t1.price, t2.* FROM product AS t1 LEFT JOIN product_image AS t2 ON t1.id=t2.product_id;
|
事实就是并没有什么用。。。=。=搜索结果还是上面一样，无法避免重复
然而用分组的group by，效果非常的好，语句如下
1
| select t1.*, t2.* from product as t1 LEFT JOIN product_image as t2 on t1.id=t2.product_id GROUP BY t1.id；
|
结果如下
1
2
3
4
5
6
7
8
| +
| id | name | price | image_id | product_id | image_url |
|
| 0 | product1 | 3 | 1 | 0 | ./image/food1.jpg |
| 1 | product2 | 4 | 6 | 1 | ./image/food3327.jpg |
| 2 | product3 | 6.7 | 5 | 2 | ./image/food34.jpg |
| 3 | product4 | 5.84 | 4 | 3 | ./image/food2.jpg |
+
|
和之前只是单纯的left join比起来，就可以看出image_url选取的是相同条件下第一条记录。
The End~