mysql - LEFT JOIN 来自另一个表的计数

Question

我正在尝试计算一门类(class)已完成的交易数量，我正在尝试将training_transactions与所有行的计数进行左连接，其中training_transaction_course = course_id并且training_transaction_status ='completed'这是我的代码到目前为止:

SELECT    training.*, 
          Count(DISTINCT training_transactions.training_transaction_course) AS completed_training_payments
left JOIN users 
ON        training.course_user = users.user_id 
LEFT JOIN training_transactions 
ON        training.course_user = training_transactions.training_transaction_user 
FROM      training 
WHERE     course_id = ? 
AND       training_transactions.training_transaction_status = 'complete'
AND       course_enabled = 'enabled'

我的 table :

训练交易

CREATE TABLE IF NOT EXISTS `training_transactions` (
  `training_transaction_id` int(11) NOT NULL,
  `training_transaction_user` int(11) NOT NULL,
  `training_transaction_course` int(11) NOT NULL,
  `training_transaction_status` varchar(50) NOT NULL,
  `training_transaction_enabled` varchar(50) NOT NULL DEFAULT 'enabled',
  `training_transaction_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

训练

CREATE TABLE IF NOT EXISTS `training` (
  `course_id` int(11) NOT NULL,
  `course_user` int(11) NOT NULL,
  `course_type` varchar(255) NOT NULL,
  `course_name` varchar(255) NOT NULL,
  `course_location` varchar(255) NOT NULL,
  `course_duration` varchar(255) NOT NULL,
  `course_fitness_type` varchar(255) NOT NULL,
  `course_instructor_name` varchar(255) NOT NULL,
  `course_price` int(15) NOT NULL,
  `course_start_date` date NOT NULL,
  `course_max_attendees` int(8) NOT NULL,
  `course_accommodation` varchar(255) NOT NULL,
  `course_accommodation_price` varchar(255) NOT NULL,
  `course_status` varchar(50) NOT NULL,
  `course_enabled` varchar(10) NOT NULL DEFAULT 'enabled'
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;

如您所见，我正在尝试获取已完成交易的计数，作为从 course_max_attendees 中扣除的计数，然后我可以检查是否还有剩余名额。

Answer 1

您想要选择培训。因此从training中选择。您想用它来显示事务计数，您可以在 select 子句的子查询中执行此操作:

select
  t.*,
  (
    select count(*) 
    from training_transactions tt
    where tt.training_transaction_user = t.course_user
    and tt.training_transaction_status = 'complete'
  ) as completed_training_payments
from training t
where t.course_id = ? 
and t.course_enabled = 'enabled';

这里与连接相同:

select
  t.*, coalesce(tt.cnt, 0) as completed_training_payments
from training t
left join
(
  select training_transaction_status, count(*) as cnt
  from training_transactions
  where training_transaction_status = 'complete'
  group by training_transaction_status
) tt on tt.training_transaction_user = t.course_user
where t.course_id = ? 
and t.course_enabled = 'enabled';