时间戳

首页 > 解决方案 > 如何执行 SQL 查询，该查询将根据 id 从 2 个表中返回组合数据

mysql - 如何执行 SQL 查询，该查询将根据 id 从 2 个表中返回组合数据

问题描述

我有2张桌子，

当我查询 SELECT * FROM 类别时，我得到了这个：

[
    {
        "id": 1,
        "title": "Shoes"
    },
    {
        "id": 2,
        "title": "Electronics"
    }
]

当我查询 SELECT * FROM products LIMIT 1 时，我得到这个：

{
    "id": 1,
    "title": "PlayStation 4",
    "image": "https://encrypted-tbn0.gstatic.com/images?q=tbn%3AANd9GcSr-iFW5W8n3_jxNKiclAP_k71Fi9PGcojsMUC-vb8zbwJthbBd",
    "images": "https://encrypted-tbn0.gstatic.com/images?q=tbn%3AANd9GcSr-iFW5W8n3_jxNKiclAP_k71Fi9PGcojsMUC-vb8zbwJthbBd;https://static.toiimg.com/thumb/msid-56933980,width-640,resizemode-4,imgsize-85436/56933980.jpg;https://cdn.mos.cms.futurecdn.net/3328be45e8c7fe5194055b4c687fb769-1200-80.jpeg;https://img.etimg.com/thumb/width-640,height-480,imgsize-76492,resizemode-1,msid-52464286/46.jpg",
    "description": "With PS4, gaming becomes a lot more power packed. Ultra-fast processors, high-performance system, real-time game sharing, remote play and lots more makes it the ultimate companion device.",
    "price": 240.99,
    "quantity": 0,
    "short_desc": "Gaming console",
    "cat_id": 2
}

我想编写一个查询，它将返回带有添加键“类别”而不是“cat_id”的产品，所以在我编写查询并取回产品后，我将得到如下信息：

{
    "id": 1,
    "title": "PlayStation 4",
    "image": "https://encrypted-tbn0.gstatic.com/images?q=tbn%3AANd9GcSr-iFW5W8n3_jxNKiclAP_k71Fi9PGcojsMUC-vb8zbwJthbBd",
    "images": "https://encrypted-tbn0.gstatic.com/images?q=tbn%3AANd9GcSr-iFW5W8n3_jxNKiclAP_k71Fi9PGcojsMUC-vb8zbwJthbBd;https://static.toiimg.com/thumb/msid-56933980,width-640,resizemode-4,imgsize-85436/56933980.jpg;https://cdn.mos.cms.futurecdn.net/3328be45e8c7fe5194055b4c687fb769-1200-80.jpeg;https://img.etimg.com/thumb/width-640,height-480,imgsize-76492,resizemode-1,msid-52464286/46.jpg",
    "description": "With PS4, gaming becomes a lot more power packed. Ultra-fast processors, high-performance system, real-time game sharing, remote play and lots more makes it the ultimate companion device.",
    "price": 240.99,
    "quantity": 0,
    "short_desc": "Gaming console",
    "category": "Electronics"
}

基本上，类别表中产品“cat_id”的匹配项是cat_id == id（来自类别表）。感谢我能得到的所有帮助:)

标签： mysqlnode.jsexpress

解决方案

尝试这个

SELECT p.id, p.title, p.image, p.images, p.description, p.price, p.quantity, p.short_desc,
c.title as category FROM products p JOIN categories c ON
    c.id = p.cat_id;

推荐阅读