ID有多条记录导致用于副表连接查询时会出现重复

最新推荐文章于 2021-10-24 17:27:57 发布

qq_26535453

最新推荐文章于 2021-10-24 17:27:57 发布

阅读量662

点赞数

分类专栏：测试

本文链接：https://blog.csdn.net/qq_26535453/article/details/48287025

版权

测试专栏收录该内容

22 篇文章 0 订阅

订阅专栏

SELECT <strong>DISTINCT</strong>
    h.house_id AS id,
    title AS title,
    cover_image_url AS thumb,
    house_price AS price,
    unit_price AS unitPrice,
    house_size AS size,
    `house_city_proper` AS area,
    house_room AS chambers,
    house_hall AS halls,
    house_toilet AS toilets,
    `house_partition` AS businessZone,
    address AS address,
    total_floor AS storey,
    floor AS floor,
    `house_property` AS buildings,
    house_aspect AS orientation,
    h.`house_usage` AS type,
    's' AS tradeMode,
    modDate AS lastUpdated,
    0 AS sort
FROM
    `t_house_sold` h
        LEFT JOIN
    `t_promotion` p ON (h.`house_id` = p.`house_id`)
WHERE
    h.`house_area_id` = 66
        AND h.`house_usage` = 'xzl'
        AND `house_state` = 'PUB'
        AND delflag = 0
        AND house_agent_state = 'NORMAL'
        AND house_price != 0
ORDER BY `promotion_time` DESC , `modDate` DESC , `promotion_type` DESC
LIMIT 500

如果没有用DISTINCT 会出现复复数据。也可以算作是处理这种重复的一种方案。

类似于：A表

1 a

2 b

B表：

1 at

1 ac

2 bc

A LEFT JOIN B

ID为1的就会出现两条记录

所以一般副表应有唯一记录，否则易出现重复记录。

方案一：表里不出现一对多