英文:
SQLite query for ordering a list based on mistakes made
问题
以下是翻译好的内容：
我想知道如何根据服务员所犯错误的数量查询数据库。
我有两个表格：
员工表
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 + " INTEGER,"+ " FOREIGN KEY (" + ID_WAITER_MISTAKE + ") REFERENCES " + EMP_TABLE + "(" + ID_EMP + "), "+ " FOREIGN KEY (" + ID_ORDER_MISTAKE + ") REFERENCES " + ORDER_TABLE + "(" + ID_ORDER + "));";
我正试图根据在错误表中使用ID_WAITER_MISTAKE统计的员工的错误数量对列表进行排序。
String query = SELECT * , COUNT(Mistake_TABLE.ID_Waiter) as CountFROM EMP_TABLEORDER BY Count DESC
英文:
I would like to know how to query a database based on the amount of mistakes made by a waiter.
The two tables I have are
Employee
String employeeTable = "CREATE TABLE " + EMP_TABLE + " ("+ ID_EMP + " INTEGER PRIMARY KEY, "+ FIRST_NAME + " TEXT,"+ LAST_NAME + " TEXT,"+ PROFIT + " INTEGER);";
MistakeEmployee
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 + "));";
I am trying to order the list based on the Employee with the most Mistakes which would be counted in the mistakes table using the ID_WAITER_MISTAKE.
String query = SELECT * , COUNT(Mistake_TABLE.ID_Waiter) as CountFROM EMP_TABLEORDER BY Count DESC
答案1
得分: 1
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." + 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 ;
英文:
You must join the tables, group by employee and aggregate:
SELECT e.ID_EMP, e.FIRST_NAME, e.LAST_NAME,COUNT(m.ID_MISTAKE) AS Mistake_CountFROM employeeTable e LEFT JOIN mistakeTable mON m.ID_WAITER_MISTAKE = e.ID_EMPGROUP BY e.ID_EMP, e.FIRST_NAME, e.LAST_NAMEORDER BY Mistake_Count DESC, e.ID_EMP
and in Java code you should construct the sql string like this:
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 ;
I hope there are no typos.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论