SQL Query根据引用列检查表列中出现的次数(SQL Query that checks the no of occurances in a table column based on a ref

SQL Query根据引用列检查表列中出现的次数(SQL Query that checks the no of occurances in a table column based on a reference column)

我有2个表叫合同和合同记录。 对于每份合同，将有多个合同记录，这意味着contractid将成为合同记录中的foriegnkey参考。 合同记录中有一个状态列，其值可能为1,2,3,4。 现在我的问题是，如果合同有多个合同记录，并且所有合同都具有状态1，则合同处于活动状态。 如果所有合同记录的状态都不是1，则合同处于非活动状态。 如果至少一个合同记录的状态为1，那么该合同就是分离的。

如何实现这一目标？

I have 2 tables called contract and contractrecords. For every contract there will be multiple contractrecords, that means contractid will be the foriegnkey reference in contractrecords. There is a status column in the contractrecords and it might have the values of 1,2,3,4. Now my problem is if a contract has multiple contractrecords and all of them has the status 1 then the contract is active. If all the contractrecords have the status other than 1, then contract is inactive. If atleast one contractrecord has the status 1 then that contract is partialactive.

How to achive this?

最满意答案

这应该做的工作：

Select contractId, status = case when total = status then 'active' when status = 0 then 'inactive' else 'partial' end From ( Select contractId, total = count(recordId), status = Sum(case when status = 1 then 1 else 0) From contractrecords Group By contractId )

更新您的表和列（并修复拼写错误）

This should do the job:

Select contractId, status = case when total = status then 'active' when status = 0 then 'inactive' else 'partial' end From ( Select contractId, total = count(recordId), status = Sum(case when status = 1 then 1 else 0) From contractrecords Group By contractId )

Update with your tables and columns (and fix typo perhaps)