使用同一行但不同的視圖/列輸出連接SQL表

編輯：最初的問題通過下面的答案得到解決。我還有一個問題 - 現在我看到“已鏈接”和“未鏈接”計數，我試圖通過 獲取“已鏈接”的百分比COUNT("Article Link")/COUNT(*) as "Percentage"，但它只顯示“0”或“1”而不是實際百分比。

--------------原始問題------------

我有下表。

CaseNumber  Owner   Article Link
ABC1234     John    Linked
ABC2345     Alex    Linked
ABC3456     John    Linked
ABC4567     Maria   Linked
ABC5678     Maria   
ABC6789     John    
ABC7900     Alex    
ABC9011     Issac   Linked
ABC10122    Issac   Linked
ABC11233    Issac   Linked
ABC12344    Issac   Linked
ABC13455    Issac   Linked
ABC14566    Maria   Linked
ABC15677    Maria   Linked
ABC16788    Maria   Linked
ABC17899    John    Linked
ABC19010    John    

我正在嘗試獲得如下所示的輸出，即按“所有者”和“已鏈接”和“未鏈接”的計數進行分組。

**Owner Linked  Not Linked**
Alex    1       1
Issac   5       
John    3       2
Maria   4       1

我已經嘗試了以下方法，這分別給出了結果，但是自聯接或內部/左聯接也無濟于事。

SELECT  Owner, count(Owner) AS "Linked"
FROM Linking_Table 
Where "Article Link"  = 'Linked'
GROUP BY Owner

SELECT  Owner, count(Owner) AS "Not Linked"
FROM Linking_Table 
Where "Article Link" is NULL
GROUP BY Owner

并嘗試了這個。沒有運氣。

SELECT  Owner, count(Owner) AS "Linked", count(Owner) AS "Not Linked"
FROM Linking_Table 
WHERE "Article Link"  = 'Linked' OR "Article Link" is NULL
GROUP BY Owner

任何人都可以建議，我錯過了什么或指出任何檔案嗎？

uj5u.com熱心網友回復：

您可以使用如下條件聚合：

架構和插入陳述句：

CREATE TABLE Linking_Table (CaseNumber VARCHAR(20),  Owner VARCHAR(20),   Article_Link VARCHAR(20))


INSERT INTO Linking_Table VALUES('ABC1234','John','Linked');
INSERT INTO Linking_Table VALUES('ABC2345','Alex','Linked');
INSERT INTO Linking_Table VALUES('ABC3456','John','Linked');
INSERT INTO Linking_Table VALUES('ABC4567','Maria','Linked');
INSERT INTO Linking_Table VALUES('ABC5678','Maria','');   
INSERT INTO Linking_Table VALUES('ABC6789','John','');    
INSERT INTO Linking_Table VALUES('ABC7900','Alex','');    
INSERT INTO Linking_Table VALUES('ABC9011','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC10122','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC11233','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC12344','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC13455','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC14566','Maria','Linked');
INSERT INTO Linking_Table VALUES('ABC15677','Maria','Linked');
INSERT INTO Linking_Table VALUES('ABC16788','Maria','Linked');
INSERT INTO Linking_Table VALUES('ABC17899','John','Linked');
INSERT INTO Linking_Table VALUES('ABC19010','John','');    

詢問：

SELECT Owner,sum(case when Article_Link='Linked' then 1 end) Linked,
sum(case when Article_Link<>'Linked' then 1 end) Not_Linked  
FROM Linking_Table
group by Owner
order by Owner

輸出：

db<>在這里擺弄

查詢計算鏈接百分比：

 SELECT Owner,sum(case when Article_Link='Linked' then 1 end) Linked,
 sum(case when Article_Link<>'Linked' then 1 end) Not_Linked,
 100*sum(case when Article_Link='Linked' then 1 end)/count(*) inked_percentage
 FROM Linking_Table
 group by Owner
 order by Owner

輸出：

db<>在這里擺弄

uj5u.com熱心網友回復：

COUNT(*)計算組中的所有行。

COUNT(column_name)column_name計算所有行NOT NULL

SELECT
  Owner,
  COUNT("Article Link") AS "Linked",
  COUNT(*) - COUNT("Article Link") AS "Not Linked"
FROM
  Linking_Table 
WHERE
     "Article Link"  = 'Linked'
  OR "Article Link" IS NULL
GROUP BY
  Owner

標籤：sql sql服务器

上一篇：如何在SQL中將貨幣格式（從Excel）轉換為小數？

下一篇：如何在SQLServer連接陳述句的右選擇陳述句中的where子句中使用左表中的列