上图是正在为其构建android应用程序的站点的资源表的图像。请为我的英语不好和解释不力而感到抱歉，我将尽我所能解释。我有一个称为资源的表，如下所示，它是另一个称为产品的表的一部分。产品表存储有关产品的信息，资源表存储图像名称和存储方向。每个图像都有单独的一行。现在，如果我尝试运行查询以链接或加入产品和资源表，由于资源的图像，我开始获取重复/重复的输出值。
以下是产品表
pk_i_id | fk_i_user_id | p_name | p_description | etc
1 4 iphone Iphone X
2 4 Cloth Gucci
3 6 watch Rolex Watch
4 3 car Venza
5 7 tractor CAT
6 9 Fruits Melon
下面是资源表
pk_i_id | fk_i_item_id | s_name | s_extension | s_content_type | s_path
1 2 xDSDki0F jpg image/jpeg oc-content/uploads/0/
2 2 GAsk8vqW jpg image/jpeg oc-content/uploads/0/
3 3 D033Znu1 jpg image/jpeg oc-content/uploads/0/
4 3 yZSPKeCj jpg image/jpeg oc-content/uploads/0/
5 5 rc8wCLOQ jpg image/jpeg oc-content/uploads/0/
6 5 tUWBh6vk jpg image/jpeg oc-content/uploads/0/
8 6 v3rSPl9s jpg image/jpeg oc-content/uploads/0/
9 7 8FTuKvgl jpg image/jpeg oc-content/uploads/0/
10 7 fzb2KzYq jpg image/jpeg oc-content/uploads/0/
11 7 9Qs7MgUl jpg image/jpeg oc-content/uploads/0/
12 8 3HmpjxC7 jpg image/jpeg oc-content/uploads/0/
13 8 VF4M6PrE jpg image/jpeg oc-content/uploads/0/
14 8 tnIH7LqU jpg image/jpeg oc-content/uploads/0/
15 8 M5jKALrD jpg image/jpeg oc-content/uploads/0/
16 9 vBBSSfoD jpg image/jpeg oc-content/uploads/0/
如果我这样查询
select * products p, resource r where p.pk_i_id=r.fk_i_item_id
由于资源图像存储的多个部分，它将给我多个重复查询。如果“关于汽车的图像”为3，该查询将显示三次重复的汽车查询。
从上表中判断，如果我尝试使用以下查询从此处获取”
select * from osoe_t_item_resource
它将在应用程序中显示所有高于表值的值。
请我需要一个查询，它将与另一个列示例相同的项目分组为fk_i_item_id
而不是像下面这样显示
{"NewsApp":[
{"pk_i_id":"1","fk_i_item_id":"2","s_name":"xDSDki0F
{"pk_i_id":"2","fk_i_item_id":"2","s_name":"GAsk8vqW
{"pk_i_id":"3","fk_i_item_id":"3","s_name":"D033Znu1"},
{"pk_i_id":"4","fk_i_item_id":"3","s_name":"yZSPKeCj"},
{"pk_i_id":"5","fk_i_item_id":"5","s_name":"rc8wCLOQ"},
{"pk_i_id":"6","fk_i_item_id":"5","s_name":"tUWBh6vk"},
{"pk_i_id":"8","fk_i_item_id":"6","s_name":"v3rSPl9s"},
{"pk_i_id":"9","fk_i_item_id":"7","s_name":"8FTuKvgl"},
{"pk_i_id":"10","fk_i_item_id":"7","s_name":"fzb2KzYq"},
{"pk_i_id":"11","fk_i_item_id":"7","s_name":"9Qs7MgUl"},
{"pk_i_id":"12","fk_i_item_id":"8","s_name":"3HmpjxC7"},
{"pk_i_id":"13","fk_i_item_id":"8","s_name":"VF4M6PrE"},
{"pk_i_id":"14","fk_i_item_id":"8","s_name":"tnIH7LqU"},
{"pk_i_id":"15","fk_i_item_id":"8","s_name":"M5jKALrD"},
{"pk_i_id":"16","fk_i_item_id":"9","s_name":"vBBSSfoD"},
{"pk_i_id":"17","fk_i_item_id":"9","s_name":"kUt65zVb"},
{"pk_i_id":"18","fk_i_item_id":"9","s_name":"D5DXAszv"},
它应该看起来像这样的输出
{"NewsApp":[
{"pk_i_id":"1","fk_i_item_id":"2","s_name":"xDSDki0F","s_name2":"GAsk8vqW"},
{"pk_i_id":"3","fk_i_item_id":"3","s_name":"D033Znu1","s_name2":"yZSPKeCj"},
{"pk_i_id":"5","fk_i_item_id":"5","s_name":"rc8wCLOQ","s_name2":"tUWBh6vk"},
{"pk_i_id":"8","fk_i_item_id":"6","s_name":"v3rSPl9s"},
{"pk_i_id":"9","fk_i_item_id":"7","s_name":"8FTuKvgl","s_name2":"fzb2KzYq","s_name3":"9Qs7MgUl"},
{"pk_i_id":"12","fk_i_item_id":"8","s_name":"3HmpjxC7"},
{"pk_i_id":"13","fk_i_item_id":"8","s_name":"VF4M6PrE","s_name2":"tnIH7LqU","s_name":"M5jKALrD"},
{"pk_i_id":"16","fk_i_item_id":"9","s_name2":"vBBSSfoD"},
{"pk_i_id":"17","fk_i_item_id":"9","s_name":"kUt65zVb","s_name2":"D5DXAszv"},
如果您密切注意始终将具有相同ID的ID合并在一起，
请帮帮我。
我要保证的是，当有相同记录时fk_i_item_id，您只想保留最低记录pk_i_id。
如果您使用的是MySQL 8.0，则可以通过window函数直接实现ROW_NUMBER()：
SELECT x.pk_i_id, x.fk_i_item_id, x.s_name
FROM (
SELECT
r.*,
ROW_NUMBER() OVER(PARTITION BY r.fk_i_item_id ORDER BY r.pk_i_id) rn
FROM resource r
) x WHERE x.rn = 1
内部查询将每个记录排列在fk_i_item_id组中，而外部查询则过滤掉重复项。
如果您正在运行早期版本的MySQL，则可以将NOT EXIST条件与相关子查询一起使用，以确保不存在其他相同fk_i_item_id且较低的记录pk_i_id：
SELECT r.pk_i_id, r.fk_i_item_id, r.s_name
FROM resource r
WHERE NOT EXISTS (
SELECT 1
FROM resource r1
WHERE r1.fk_i_item_id = r.fk_i_item_id AND r1.pk_i_id < r.pk_i_id
)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句