mysql - 如何使用复合键进行内连接

Question

I have card_type table in which there is card_id is auto_increment key and cardtpe is primary key. The another table which I have is activities table. The columns in activities table are activity_id with auto_increment key and activity column with primary key. The third table is actual_alldetails table. The columns in actual_alldetails are id with auto_increment key and (emp_code,activity_id,card_id) column with composite key. The fourth table is employee table and columns in these table are emp_code with primary key and emp_name.

当我尝试执行我的查询之一时:-

SELECT i.*,
       d.Date,
       a.in_time,
       a.out_time,
       SEC_TO_TIME(SUM(TIME_TO_SEC(a.out_time))-(TIME_TO_SEC(a.in_time))) AS duration,
       c.cardtype,
       a.wo,
       v.activity,
       a.quty,
       a.wastage,
       a.mcusage,
       a.actual_wastage
FROM employee_details i
INNER JOIN actual_alldetails a ON i.emp_code=a.emp_code
INNER JOIN attendance_date d ON d.date_id=a.date_id
INNER JOIN card_type c ON c.card_id=a.card_id
INNER JOIN activities v ON v.activity_id=a.activity_id
WHERE d.Date='2016-01-30'
ORDER BY v.activity;

它向我显示错误，例如:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause.

我不明白为什么它会显示这样的错误。是因为我正在使用一个主键和一个组合键进行内连接吗？如果我的预测是正确的，那么执行这些操作的正确查询是什么？请帮我解决这个问题。

Answer 1

错误消息说:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause.

您有一个 select 语句，其中包含聚合值 SUM(TIME_TO_SEC(a.out_time) 和非聚合值 TIME_TO_SEC(a.in_time)

我不知道您到底希望得到什么结果，但您要么必须对第二个值应用聚合函数，要么必须按照错误消息所述添加分组依据，以便它知道要分组的内容.

例如将查询更改为:

SELECT i.*,
   d.Date,
   a.in_time,
   a.out_time,
   SEC_TO_TIME(SUM(TIME_TO_SEC(a.out_time))-(TIME_TO_SEC(a.in_time))) AS duration,
   c.cardtype,
   a.wo,
   v.activity,
   a.quty,
   a.wastage,
   a.mcusage,
   a.actual_wastage
FROM employee_details i
INNER JOIN actual_alldetails a ON i.emp_code=a.emp_code
INNER JOIN attendance_date d ON d.date_id=a.date_id
INNER JOIN card_type c ON c.card_id=a.card_id
INNER JOIN activities v ON v.activity_id=a.activity_id
WHERE d.Date='2016-01-30'
GROUP BY i.emp_code
ORDER BY v.activity;

我猜测您员工的 id 值:i.emp_code