时间戳

首页 > 解决方案 > 在mysql中使用关系时如何获取行数

mysql - 在mysql中使用关系时如何获取行数

问题描述

我是 sql 的新手，我已经设法使用下面的 sql 选择与许多表有关系的行。我现在有第四张表，其中包含模型的“子项”项目。模型表（526 行）中的“id”和项目表（1505 行）中的“model_id”之间存在关系。我现在想计算 items 表中与模型表中的 id 具有相同 model_id 的项目，并将它们输出为 Items_in_model。

SELECT models.id AS Id, categories.name AS Category, models.category_name 
AS CategoryName, manufacturers.name AS Manufacturer, models.model AS Model, 
models.day_price AS dayprice, models.week_price AS weekprice 
FROM
models, categories, manufacturers
WHERE models.category=categories.id AND 
models.manufacturer=manufacturers.id 
ORDER BY CategoryName ASC

我用这个 sql 让计数工作“独立”

SELECT COUNT(items.id) AS Count FROM items WHERE items.model_id 
LIKE 2

我尝试了许多不同的方法，但这是唯一没有给我错误但只输出一行（共 526 行）并且计数为（所有项目中为 1505）的方法

SELECT models.id AS Id, categories.name AS Category, 
models.category_name AS CategoryName, COUNT(items.id) AS Count, 
manufacturers.name AS Manufacturer, models.model AS Model, 
COUNT(DISTINCT(items.id)) AS ModelCount, models.day_price AS dayprice, 
models.week_price AS weekprice 
FROM
models, categories, manufacturers, items
WHERE models.category=categories.id AND 
models.manufacturer=manufacturers.id AND models.id=items.model_id
ORDER BY CategoryName ASC

提前致谢！

标签： mysql

解决方案

您的目标不是 100% 明确，但这是我的猜测：

SELECT m.id AS Id, 
    c.name AS Category,
    m.category_name AS CategoryName,
    manufacturers.name AS Manufacturer,
    m.model AS Model, 
    COUNT(i.id) AS ModelCount,
    m.day_price AS dayprice, 
    m.week_price AS weekprice 
FROM models m
LEFT JOIN categories c
ON m.category=c.id
LEFT JOIN manufacturers
ON m.manufacturer=manufacturers.id
LEFT JOIN items i
ON m.id=i.model_id
GROUP BY  m.id

更新不确定您的新问题，但我怀疑您是否需要额外的JOIN. 试试这个方法：

SELECT m.id AS Id, 
    c.name AS Category,
    m.category_name AS CategoryName,
    manufacturers.name AS Manufacturer,
    m.model AS Model, 
    COUNT(i.id) AS ModelCount,
    SUM(IF(i.reservable = 1,1,0)) AS ModelReservableCount,
    m.day_price AS dayprice, 
    m.week_price AS weekprice 
FROM models m
LEFT JOIN categories c
ON m.category=c.id
LEFT JOIN manufacturers
ON m.manufacturer=manufacturers.id
LEFT JOIN items i
ON m.id=i.model_id
GROUP BY  m.id

推荐阅读