SQL-筛选器的多个子句

Question

所以我不完全确定它应该是WHERE语句与子语句，与CASE。坦率地说，我仍在努力学习subclause和CASE的用例。

最后，我想要的是他们的出席人数和在一定时间内支付的发票金额。

我在尝试"；错误：语法错误在"处或附近；从"；位置：349〃；

预期输出：子名称、中心ID、教室名称、出勤人数、发票总额、日期、

我加入了多个表格，并希望筛选特定日期内的考勤和特定日期内支付的发票：

select accounts.fname as first_name, accounts.lname as last_name, accounts.dob, centers.label, classrooms.label as classroom_label, 
count(*) as no_of_days_attended, sum(transactions.amount / 100.0) as sum_of_transactions_paid, 
from daily_reports
join account_classrooms
on daily_reports.account_classroom_id = account_classrooms.id
join accounts
on account_classrooms.account_id = accounts.id
join classrooms
on account_classrooms.classroom_id = classrooms.id
join centers
on classrooms.center_id = centers.id
join invoices
on centers.id = invoices.center_id
join transactions
on transactions.invoice_id = invoices.id
where daily_reports.ref_date and transactions.created_at > {{start_date}} and daily_reports.ref_date and transactions.created_at < {{end_date}} and centers.id = {{center_id}}
group by daily_reports.account_classroom_id, accounts.id, classrooms.id, centers.id, 

非常感谢！

Answer 1

您遇到了几个问题。

首先，在FROM子句之前有一个额外的逗号，在GROUP BY的末尾有一个逗号。GROUP BY列不会出现在SELECT语句中，所以这是不起作用的。在进行聚合(SUM((、MAX((等(时，引擎必须知道根据哪些列对聚合进行分组。这些是SELECT列表中的列。

其次，不能像这样同时评估两列：

daily_reports.ref_date and transactions.created_at > {{start_date}}

如果你这样做了，你会得到这个错误：

An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

每个评估都必须是原子的。在这个例子中，它必须是这样的：

daily_reports.ref_date > {{start_date}} and transactions.created_at > {{start_date}}

但是，WHERE子句可以通过使用BETWEEN运算符进一步清理。此外，最好不要使用隐式JOIN。在代码中定义JOIN的类型(例如INNER JOIN(。

我已经应用了别名，格式化了代码，并应用了BETWEEN运算符。看看这是否适合你。正如您所看到的，当代码格式良好时，它的可读性更强，也更容易发现问题。

SELECT  a.fname AS first_name
,a.lname AS last_name
,a.dob
,cn.label
,cl.label AS classroom_label
,COUNT(*) AS no_of_days_attended
,SUM(t.amount / 100.0) AS sum_of_transactions_paid
FROM    daily_reports AS dr
INNER JOIN account_classrooms AS ac ON dr.account_classroom_id = ac.id
INNER JOIN accounts AS a ON ac.account_id = a.id
INNER JOIN classrooms AS cl ON ac.classroom_id = cl.id
INNER JOIN centers AS cn ON cl.center_id = cn.id
INNER JOIN invoices AS i ON cn.id = i.center_id
INNER JOIN transactions AS t ON t.invoice_id = i.invoices.id
WHERE   dr.ref_date BETWEEN @start_date AND @end_date
AND t.created_at BETWEEN @start_date AND @end_date
AND cn.id = @center_id
GROUP BY
a.fname
,a.lname
,a.dob
,cn.label