求SQL Server含OVER(PARTITION)查询的SQLite等价实现

Got it, let's get this query converted for SQLite. Here's what you need, depending on your SQLite version:

For SQLite 3.25.0 and Newer (Supports Window Functions) #

SQLite added window function support in version 3.25.0, so you can use a nearly identical query—just rearranged for readability (SQLite doesn't care about JOIN order, but this format is easier to follow):

SELECT DISTINCT
    TblSites.Customer,
    TblSites.Digit,
    COUNT(TblContractTasks.TaskNumber) OVER (PARTITION BY TblSites.Digit) AS TaskCount
FROM TblContractTasks
INNER JOIN TblContractDetails ON TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
INNER JOIN TblSites ON TblSites.Digit = TblContractDetails.SiteDigit
-- Add any remaining WHERE/ORDER BY clauses from your original query here

For Older SQLite Versions (No Window Function Support) #

If you're stuck with an older SQLite release that doesn't support OVER() clauses, use a subquery to pre-calculate task counts per site digit, then join it back to your sites data:

SELECT
    TblSites.Customer,
    TblSites.Digit,
    TaskCounts.TaskCount
FROM (
    SELECT
        TblContractDetails.SiteDigit,
        COUNT(TblContractTasks.TaskNumber) AS TaskCount
    FROM TblContractTasks
    INNER JOIN TblContractDetails ON TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
    GROUP BY TblContractDetails.SiteDigit
) AS TaskCounts
INNER JOIN TblSites ON TblSites.Digit = TaskCounts.SiteDigit
-- Add any remaining WHERE/ORDER BY clauses here

This subquery approach groups tasks by site digit first to get the total count, then links that back to the TblSites table to pull in customer details.

内容的提问来源于stack exchange，提问作者DThomas