問題描述

我無論如何都不是 SQL 專家，并且很難從查詢中獲取我需要的數(shù)據(jù).我正在處理一個表，Journal_Entry，它有很多列.一列是 Status_ID，它是 Status 表的外鍵，具有三個值Green"、Yellow"和Red".此外，日志條目是針對特定用戶 (User_ID) 記錄的.

I'm not an expert in SQL by any means, and am having a hard time getting the data I need from a query. I'm working with a single table, Journal_Entry, that has a number of columns. One column is Status_ID, which is a foreign key to a Status table with three values "Green", "Yellow", and "Red". Also, a journal entry is logged against a particular User (User_ID).

我正在嘗試獲取為每個狀態(tài)記錄的日記條目數(shù)，作為特定用戶記錄的日記條目總數(shù)的百分比.到目前為止，我已經(jīng)獲得了以下狀態(tài)為 1 的狀態(tài)，即綠色(我知道這不起作用):

I'm trying to get the number of journal entries logged for each Status, as a percentage of the total number of journal entries logged by a particular user. So far I've got the following for a Status of 1, which is green (and I know this doesn't work):

SELECT  CAST((SELECT COUNT(Journal_Entry_ID) 
        FROM Journal_Entry 
        WHERE Status_ID = 1 AND User_ID = 3 / 
        SELECT COUNT(Journal_Entry_ID) 
        FROM Journal_Entry AND User_ID = 3)) AS FLOAT * 100

我需要繼續(xù)查詢其他兩個狀態(tài) ID，2 和 3，理想情況下希望以選擇三列作為百分比結(jié)束，每個狀態(tài)一列:Green_Percent"、Yellow_Percent"和"Red_Percent".

I need to continue the query for the other two status ID's, 2 and 3, and ideally would like to end with the selection of three columns as percentages, one for each Status: "Green_Percent", "Yellow_Percent", and "Red_Percent".

這可能是我問過的最雜亂無章的問題，因此對于任何不夠明確的問題，我深表歉意.我很樂意在必要時進行澄清.另外，我使用的是 SQL Server 2005.

This is probably the most disjointed question I've ever asked, so I apologize for any lack of clarity. I'll be happy to clarify as necessary. Also, I'm using SQL Server 2005.

非常感謝.

推薦答案

使用:

  SELECT je.statusid,
         COUNT(*) AS num,
         (COUNT(*) / (SELECT COUNT(*)+.0
                       FROM JOURNAL_ENTRY) ) * 100
    FROM JOURNAL_ENTRY je
GROUP BY je.statusid

然后是格式化您想要的精度的問題:

Then it's a matter of formatting the precision you want:

CAST(((COUNT(*) / (SELECT COUNT(*)+.0 FROM BCCAMPUS.dbo.COURSES_RFIP)) * 100)
     AS DECIMAL(4,2))

...將給出兩位小數(shù).如果您不需要任何小數(shù)位，請將結(jié)果轉(zhuǎn)換為 INT.

...will give two decimal places. Cast the result to INT if you don't want any decimal places.

您可以使用 CTE 來減少重復:

You could use a CTE to minimize the duplication:

WITH cte AS (
   SELECT je.*
     FROM JOURNAL_ENTRY je
    WHERE je.user_id = 3)
  SELECT c.statusid,
         COUNT(*) AS num,
         (COUNT(*) / (SELECT COUNT(*)+.0
                       FROM cte) ) * 100
    FROM cte c
GROUP BY c.statusid

這篇關(guān)于SQL:使用聚合函數(shù)獲取百分比的查詢有問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！