初学者SQL问题:具有多个计数(*)结果的算术

你可以使用 Common Table Expression (CTE) :

WITH c1 AS (
    SELECT COUNT(*) AS cnt
    FROM Users
    WHERE Users.Reputation >= 10000
), c2 AS (
    SELECT COUNT(*) AS cnt
    FROM Users
)
SELECT c1.cnt, c2.cnt, CAST(c1.cnt AS FLOAT) / c2.cnt
FROM c1, c2

除了 using CTEs ,在这种情况下,您还可以执行以下操作:

SELECT CAST((SELECT COUNT(*) FROM Users WHERE Users.Reputation >= 10000) AS float)  /
       (SELECT COUNT(*) FROM USERS) * 100  AS Percentage​

强制转换为浮点除法,因为使用整数除法556/227691将得到0。

多亏了这里的其他答案,我写了以下问题,所有这些问题都适用于SEDE:

“内联视图”

SELECT *, CAST([10K] AS FLOAT)/[All] AS [Ratio]
FROM (
   SELECT
    (SELECT COUNT(*) FROM Users) AS [All],
    (SELECT COUNT(*) FROM Users Where Reputation >= 10000) AS [10K]
) AS UsersCount

( See query result )

变量

DECLARE @numAll FLOAT
DECLARE @num10kers FLOAT

SET @numAll = (SELECT COUNT(*) FROM Users)
SET @num10kers = (SELECT COUNT(*) FROM Users WHERE Users.Reputation >= 10000);

SELECT  @num10kers AS [10K], @numAll AS [All], @num10Kers/@numAll AS [Ratio]

( See query result )

工具书类

• MySQL 5.0 Reference Manual/User variables

公用表表达式

WITH Users10K AS ( 
    SELECT COUNT(*) AS Count
    FROM Users
    WHERE Users.Reputation >= 10000
), UsersAll AS (
    SELECT COUNT(*) As Count
    FROM Users
)
SELECT
    Users10K.Count AS [10K],
    UsersAll.Count AS [All],
    CAST(Users10K.Count AS FLOAT) / UsersAll.Count AS [Ratio]
FROM Users10K, UsersAll

( See query result )

工具书类

• MSDN/Common Table Expression

对于类似这样的查询,如果我在一个表上基于不同的条件执行多个计数,我喜欢使用 SUM 和 CASE :

SELECT
    UsersCount.[10K],
    UsersCount.[All],
    (CAST(UsersCount.[10K] AS FLOAT) / UsersCount.[All]) AS [Ratio]
FROM
    (SELECT
         SUM(CASE
               WHEN Users.Reputation >= 10000 THEN 1
               ELSE 0
             END) AS [10K],
         COUNT(*) AS [All]
     FROM Users) AS UsersCount

( query results )

其优点是您只扫描一次用户表,这可能要快得多。

WITH tmp as (
SELECT COUNT(ID) AS repCount, (SELECT COUNT(ID) FROM Users ) AS totalCount
FROM Users
WHERE Users.Reputation > 10000
)
SELECT tmp.repCount, tmp.totalCount, (cast(tmp.repCount as decimal(10,2))/tmp.TotalCount) * 100 AS Percentage
FROM tmp

更新:不带

SELECT COUNT(ID) AS repCount, (SELECT COUNT(ID) FROM Users ) AS totalCount, 
    (CAST((SELECT COUNT(ID) FROM Users WHERE Users.Reputation > 10000) AS DECIMAL(10,2)) /
        (SELECT COUNT(ID) FROM Users )) * 100 AS Persantage
FROM Users

使用 variables 在MySQL中:

SELECT @a:=(SELECT COUNT(*) FROM Users WHERE Users.Reputation >= 10000),
       @b:=(SELECT COUNT(*) FROM Users),
       IF(@b > 0, @a/@b, "--invalid--")
FROM Users
LIMIT 0,1