基于联接表条件选择count(*)

btqmn9zl 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(261)

我正试图写一个关于教育系统的查询，该系统根据以下11月份对特定学校进行的检查类型返回数字值，只是我不太清楚如何做。
我需要返回一份11月份因违规而被引用的组织的列表，然后返回导致被引用的当月访问计数的类型。我需要一个从检查类型的每种类型的计数。类型的访问基于每个供应商的条件，但不是在不同的供应商的情况下，他们被发出多个不符合。
我需要每种类型的访问是一个单独的专栏。
到目前为止，我的内联子查询正在返回未筛选的结果：

SELECT ...
, (SELECT COUNT(*) FROM Inspection__c WHERE Type_of_Visit__c = 'Pre-Licensing') AS [Number of PreLicensing Statement of Non-Compliance issue date]

这不会影响到我的where子句。
另外，除非有更好的方法，否则我需要为每种类型的访问重复这句话。
老实说，我只是不知道该怎么做。

SELECT  a.Provider_Number__c AS [Provider ID]
, a.Provider_No__c AS [Legacy System Number]
, a.License_Status__c AS [License Status]
, a.Record_Type_Name__c AS [Record Type Name]
, a.Provider_Status__c AS [Provider Status]
, a.License_Type__c AS [License Type]
, a.Name AS [Name]
, a.Provider_Address_1__c AS [Street Address 1]
, a.Provider_Address_2__c AS [Street Address 2]
, a.Provider_city_text__c AS City
, a.Provider_Zip_Code__c AS [Zip Code]
, a.Phone
, a.Provider_Email__c AS Email
, a.Capacity__c AS [Licensed Capacity]
, insp.Date_of_Visit__c AS [Visit Date]
, inv.Statement_of_non_compliance_Issue_Date__c AS [Statement of Non-Compliance Issued Date]
    , (SELECT COUNT(*) FROM Inspection__c WHERE Type_of_Visit__c = 'Pre-Licensing') AS [Number of PreLicensing Statement of Non-Compliance issue date]

  FROM Inspection__c insp
  JOIN Account a ON a.Id = insp.Provider_No__c
  JOIN Investigation__c inv ON inv.Provider_Id__c = a.Id
  WHERE (insp.Date_of_Visit__c BETWEEN '2018-11-01' AND '2018-11-30')
  AND (a.Record_Type_Name__c = 'Large Group' OR a.Record_Type_Name__c = 'Small Group')
  AND insp.Type_of_Visit__c IN ('Pre-Licensing',
    'Provisional License 90-day Monitoring',
    'Provisional License Renewal',
    'Renewal-Monitoring',
    'Renewal-Full Review',
    'Monitoring',
    'Enhanced Monitoring',
    'Capacity Increase',
    'Change of Location',
    'Change of Space',
    'Follow-up',
    'Monitoring outside differential Licensing Cycle',
    'License Upgrade',
    'Re-Activate Provider',
    'Technical Assistance',
    'Progress',
    'Renewal-Enhanced Monitoring')

我需要计算每个提供者的每个访问类型，并将值返回到表中。如果有更好的方法，它不必是一个计数函数。
非常感谢您的帮助。谢谢。

sql mysql count select ssms

来源：https://stackoverflow.com/questions/54045596/select-count-based-on-joined-table-conditions

2条答案

按热度按时间

tzdcorbm1#

我不会把你所有的字串都打出来，但我会教你怎么做。
假设您的查询如下所示：

SELECT A,B,C
FROM T
WHERE Z in ('x','y','z')

其中t有索引index1（在您的例子中index1可能是提供者id）
那你可以这么做

SELECT A,B,C, G1.x_count, G1.y_count, G1.z_count
FROM T
JOIN (
  SELECT index1,
    SUM(CASE WHEN Z = 'x' THEN 1 ELSE 0 END) AS x_COUNT,
    SUM(CASE WHEN Z = 'y' THEN 1 ELSE 0 END) AS y_COUNT,
    SUM(CASE WHEN Z = 'z' THEN 1 ELSE 0 END) AS z_COUNT
  FROM T
  WHERE Z in ('x','y','z')
  GROUP BY index1
) G1 on T.index1 = G1.index1

现在你有了x，y，z的计数

赞(0）回复(0）举报 2021-06-16

2ul0zpep2#

如果您使用的是mysql 8，那么

COUNT(*) OVER (PARTITION BY Type_of_Visit)

也许能解决你的问题。

赞(0）回复(0）举报 2021-06-16

我来回答

基于联接表条件选择count(*)

2条答案

相关问题

热门标签

最新问答