时间戳

首页 > 解决方案 > SQL 连接两个表并检索数据

mysql - SQL 连接两个表并检索数据

问题描述

表 1 - 缺陷

Defect ID | Name             | Type
--------------------------------
SB001     | Light Bead       | Bead
SB002     | Narrow Bead      | Bead
SB003     | ROUND BEAD       | Bead
SB004     | O`RING LEAK      | Bead
C0001     | Spread cord      | GT
C0002     | Sidewall wrinkle | GT

表 2 - 轮胎缺陷

Tyre S/N   | DEFECT ID | Datetime
------------------------------
0108160020 | SB004     | 2019-01-09 21:43:01.293
2512160005 | SB003     | 2019-01-10 00:05:29.150
3010160003 | SB004     | 2019-01-11 00:05:29.150

我需要如下结果

Defect ID  | Defect Name | Type
------------------------
SB004      | O`RING LEAK | BEAD
SB003      | ROUND BEAD  | BEAD
SB001      | Light Bead  | BEAD
SB002      | Narrow Bead | BEAD

我想首先得到经常得到的缺陷和其他缺陷是按名称排序的

谢谢帮助我....

标签： mysqlsql-server

解决方案

尝试：

SELECT
    DF.[Defect ID],
    DF.[Name],
    DF.[Type],
    COUNT(DF.*) AS [Defect count]
FROM
    DEFECT AS DF
    LEFT JOIN
    [Tyres Defect] AS TD
    ON DF.[Defect ID] = TD.[Defect ID]
GROUP BY
    DF.[Defect ID],
    [Name],
    [Type]
ORDER BY
    COUNT(DF.[Defect ID]) DESC
    DF.[Name]

这还将为您提供每种缺陷类型的计数。（这是答案sql-server

推荐阅读