在 PHP 中使用 WITH SQL 查询

Question

我尝试重写下面的查询，以便它可以在 PHP 页面中工作，但是我找不到任何有关如何在 PHP 中使用“;WITH”语句或如何实现相同效果的文档使用有效的 PHP 查询。在 MS SQL 2008 R2 Management Studio 中执行时，该查询工作完美，但当我尝试从 SQLMaestro 将其加载到 MSSQL PHP 生成器时，它会出现错误。

查询是：

;WITH UserStars AS
(
    SELECT a.username, ROUND(AVG(CAST(b.stars AS FLOAT)),1) Stars
    FROM score b
    JOIN game_users a ON a.id = b.game_users_id
    GROUP BY a.username
)
SELECT  CASE WHEN Stars < 1.4 THEN 'StarAmount1'
        WHEN Stars BETWEEN 1.4 AND 2.4 THEN 'StarAmount2'
        WHEN Stars BETWEEN 2.4 AND 3.4 THEN 'StarAmount3'
        WHEN Stars BETWEEN 3.4 AND 4.4 THEN 'StarAmount4'
        WHEN Stars BETWEEN 4.4 AND 5.0 THEN 'StarAmount5' ELSE 'AnotherStarAmount' END         StarAmount,
        COUNT(*) Users
FROM UserStars
GROUP BY CASE WHEN Stars < 1.4 THEN 'StarAmount1'
         WHEN Stars BETWEEN 1.4 AND 2.4 THEN 'StarAmount2'
         WHEN Stars BETWEEN 2.4 AND 3.4 THEN 'StarAmount3'
         WHEN Stars BETWEEN 3.4 AND 4.4 THEN 'StarAmount4'
         WHEN Stars BETWEEN 4.4 AND 5.0 THEN 'StarAmount5' ELSE 'AnotherStarAmount' END

有人能给我指出正确的方向，以便可以在 PHP 和 MS SQL 2008 R2 Management Studio 中使用相同的查询吗？

Answer 1

试试这样：

SELECT  CASE WHEN Stars < 1.4 THEN 'StarAmount1'
        WHEN Stars BETWEEN 1.4 AND 2.4 THEN 'StarAmount2'
        WHEN Stars BETWEEN 2.4 AND 3.4 THEN 'StarAmount3'
        WHEN Stars BETWEEN 3.4 AND 4.4 THEN 'StarAmount4'
        WHEN Stars BETWEEN 4.4 AND 5.0 THEN 'StarAmount5' ELSE 'AnotherStarAmount' END         StarAmount,
        COUNT(*) Users
FROM (SELECT a.username, ROUND(AVG(CAST(b.stars AS FLOAT)),1) Stars
    FROM score b
    JOIN game_users a ON a.id = b.game_users_id
    GROUP BY a.username) UserStars
GROUP BY CASE WHEN Stars < 1.4 THEN 'StarAmount1'
         WHEN Stars BETWEEN 1.4 AND 2.4 THEN 'StarAmount2'
         WHEN Stars BETWEEN 2.4 AND 3.4 THEN 'StarAmount3'
         WHEN Stars BETWEEN 3.4 AND 4.4 THEN 'StarAmount4'
         WHEN Stars BETWEEN 4.4 AND 5.0 THEN 'StarAmount5' ELSE 'AnotherStarAmount' END

Answer 2

这不是一种完全直观的方法，而是一种无需所有 CASE 表达式即可给猫剥皮的不同方法：

;WITH x AS 
(
    SELECT 
        c = game_users_id, 
        [avg] = AVG(CONVERT(DECIMAL(2,1), stars))
    FROM score
    GROUP BY game_users_id
), 
y AS
(
    SELECT StarAmount = 'StarAmount' 
        + LEFT(CONVERT(DECIMAL(2,1), [avg]+.6), 1)
        FROM x
)
SELECT StarAmount, Users = COUNT(*)
    FROM y
    GROUP BY StarAmount
    ORDER BY StarAmount;