当连接表具有多列组合作为主键时，如何选择连接表的COUNT()？
我正在尝试在 sql 中连接两个表并获取第二个表的计数。第二个表有多个列，其中三列一起作为主键。我的查询开始为：
SELECT times.*, COUNT(paylog.*) AS `total`
FROM times
LEFT JOIN paylog
ON paylog.type = 'work'
AND paylog.targetID = times.id
我因在 count() 参数中使用表名而感到不满：
您的 SQL 语法有错误；检查手册 与您的 MySQL 服务器版本相对应，以便使用正确的语法 靠近 '*) AS
总计来自时代 LEFT JOIN paylog ' 在第 1 行
如果 paylog 表中有一个唯一的列，我可以遵循此处给出的建议：我可以计算连接表中的行数吗？ 但事实并非如此。如何选择工资表上所有连接行的计数？我需要让它成为一个联接，因为它当前使用子查询获取计数，这使得它有点慢。
I am trying to join two tables in sql and get the count of the second table. The second table has several columns, three of which together serve as the primary key. My query started as:
SELECT times.*, COUNT(paylog.*) AS `total`
FROM times
LEFT JOIN paylog
ON paylog.type = 'work'
AND paylog.targetID = times.id
I get dinged for using the table name in the count() arguments:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '*) AStotal
FROM times
LEFT JOIN paylog ' at line 1
If there was a single unique column in the paylog table, I could follow the advice given here: Can I count number of rows in joined table? But that is not the case. How can I select the count of all the joined rows on the paylog table? I need to have it be a join because it currently gets the count with a subquery and it makes it a little slow.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对于
count(*)的特殊情况，Count 将仅返回列中非空值的数量或结果集中的行数。换句话说，count(paylog.*)不是有效的 SQL。您不需要工资表中的唯一列来进行有效计数，任何列都可以。如果连接成功，它将增加计数，否则该列的值将为空，并且计数保持不变。
如果您尝试获取付款日志中每一行的链接行数（按时间），那么您还需要一个
group by times.id子句。Count will only return the number of non null values in a column or the number of rows in a result set for the special case of
count(*). In other wordscount(paylog.*)isn't valid SQL.You don't need a unique column in the paylog table to do a valid count, any column will do. If the join is successful it'll increment the count, if not the column's value will be null and the count stays the same.
If you're trying to get the count of linked rows in paylog for each row in times then you'll also need a
group by times.idclause.