通过左联接类别表来计算交易数量 [英] Counting the number of transactions by left joining a category table

问题描述

想获得关于如何实现以下目标的建议.我有两张桌子.一种称为类别，另一种称为Trxn.我试图找出每个类别中有多少笔交易.我知道我需要将Trxn表与Category表一起加入.但是问题在于，类别表中的CategoryId是6个字符，而这个相同的ID在Trxn表中被分为两列，分别为4和2个字符列.

would like get your advice on how to achieve the following. I have two tables. One is called the Category and other the Trxn. I trying to find how many transactions are are in each category. I understand I need to left join my Trxn table with Category table. But the issue is that, CategoryId in the Category table is 6 characters and this same ID is split into two columns in the Trxn table as 4 and 2 character column respectively.

我如何才能将两列都连接起来，而如何将类别表左键联接. ??是否有可能.我的aspx应用程序必须显示每个类别中完成了多少次traxns. 我已经通过下面的SQLFiddle链接附加了示例数据库架构.

How can I can join the both the columns and left join the Category table. ?? Is it possible. My aspx application has to show how many traxns have been done in each category. I have attached a sample database schema via SQLFiddle link below.

SELECT Category.ID, count(Trxn.Cat_ID) FROM Category
LEFT JOIN Trxn
on Category.ID = (Trxn.Cat_ID+Trxn.STV)
where Category.Status='A'
AND Trxn.Status='ok'
group by Category.ID

http://sqlfiddle.com/#!3/6feef/2/0

请咨询.

推荐答案

您在where子句中拥有左外部联接表的一部分.您无法做到这一点，这使其成为普通的联接.

You have part of your left outer join table in the where clause. You can't do that, it makes it become a normal join.

这有效:

SELECT
  Category.ID,
  COUNT(Trxn.Cat_ID)
FROM
  Category
LEFT JOIN
  Trxn
  ON Category.ID = (rtrim(Trxn.Cat_ID)+ltrim(Trxn.STV))
  AND Trxn.Status='ok'
WHERE Category.Status='A'
GROUP BY Category.ID

这篇关于通过左联接类别表来计算交易数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！