时间戳

首页 > 解决方案 > 用于根据所犯错误对列表进行排序的 SQLite 查询

java - 用于根据所犯错误对列表进行排序的 SQLite 查询

问题描述

我想知道如何根据服务员犯的错误数量来查询数据库。

我有两张桌子是

员工

    String employeeTable = "CREATE TABLE " + EMP_TABLE + " ("
            + ID_EMP + " INTEGER PRIMARY KEY, "
            + FIRST_NAME + " TEXT,"
            + LAST_NAME + " TEXT,"
            + PROFIT + " INTEGER);";

错误员工

    String mistakeTable = "CREATE TABLE " + MISTAKE_TABLE + " ("
            + ID_MISTAKE + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + ID_WAITER_MISTAKE + " INTEGER,"
            + ID_ORDER_MISTAKE + " INTEGER,"
            + MISTAKE_TOTAL + " INITGER,"
            + " FOREIGN KEY (" + ID_WAITER_MISTAKE + ") REFERENCES " + EMP_TABLE + "(" + ID_EMP + "), "
            + " FOREIGN KEY (" + ID_ORDER_MISTAKE + ") REFERENCES " + ORDER_TABLE + "(" + ID_ORDER + "));";

我正在尝试根据错误表中使用Employee最多的 来排序列表。MistakesID_WAITER_MISTAKE

String query = SELECT * , COUNT(Mistake_TABLE.ID_Waiter) as Count
FROM EMP_TABLE
ORDER BY Count DESC

标签： javasqliteandroid-studio

解决方案

您必须加入表格，按员工分组并聚合：

SELECT e.ID_EMP, e.FIRST_NAME, e.LAST_NAME,
       COUNT(m.ID_MISTAKE) AS Mistake_Count
FROM employeeTable e LEFT JOIN mistakeTable m
ON m.ID_WAITER_MISTAKE = e.ID_EMP
GROUP BY e.ID_EMP, e.FIRST_NAME, e.LAST_NAME
ORDER BY Mistake_Count DESC, e.ID_EMP

在 Java 代码中，您应该像这样构造 sql 字符串：

String query = 
    "SELECT e." + ID_EMP + ", e." + FIRST_NAME + ", e." + LAST_NAME + ", " + 
    "COUNT(m." + ID_MISTAKE + ") AS Mistake_Count " +
    "FROM " + EMP_TABLE + "AS e LEFT JOIN " + MISTAKE_TABLE + " AS m " +
    "ON m." + m.ID_WAITER_MISTAKE + " = e." + ID_EMP + " " +
    "GROUP BY e." + ID_EMP + ", e." + FIRST_NAME + ", e." + LAST_NAME + " " +
    "ORDER BY Mistake_Count DESC, e." + ID_EMP ;

我希望没有错别字。

推荐阅读