SQL 連接和計數

我在 SQLServer 中有 3 個要加入的表，因此我可以計算每個 SUBJ_NAME 出現在 LOAN 表中的 ITEM_ID 的次數。

CREATE TABLE SUBJT (
   SUBJ_ID CHAR(4) PRIMARY KEY,
   SUBJ_NAME VARCHAR(25) NOT NULL,
   SUBJ_DESCRIPT VARCHAR(500)
)
CREATE TABLE ITEM_SUBJECT (
   ITEM_ID CHAR(8),
   SUBJ_ID CHAR(4),
   PRIMARY KEY (ITEM_ID, SUBJ_ID),
   FOREIGN KEY (ITEM_ID) REFERENCES ITEM,
   FOREIGN KEY (SUBJ_ID) REFERENCES SUBJT
)
CREATE TABLE LOAN (
   LOAN_ID INT PRIMARY KEY,
   PAT_ID INT NOT NULL,
   ITEM_ID CHAR(8) NOT NULL,
   VOL_ID INT NOT NULL,
   ITEM_OUTD DATE,
   ITEM_RTD DATE,
   ITEM_REN INT
   FOREIGN KEY (PAT_ID) REFERENCES PATRON,
   FOREIGN KEY (ITEM_ID) REFERENCES ITEM,
   FOREIGN KEY (VOL_ID) REFERENCES VOLUNTEER
)

我用這個查詢加入了表：

SELECT a.ITEM_ID, c.SUBJ_NAME
FROM LOAN AS a
JOIN ITEM_SUBJECT AS b ON a.ITEM_ID = b.ITEM_ID
JOIN SUBJT AS c ON b.SUBJ_ID = c.SUBJ_ID
ORDER BY SUBJ_NAME

但是如何添加計數？

這應該這樣做：

SELECT   a.ITEM_ID, 
        c.SUBJ_NAME,
        COUNT_BIG(c.SUBJ_NAME) AS COUNT_SUBJ_NAME
FROM     LOAN AS a
JOIN     ITEM_SUBJECT AS b
   ON a.ITEM_ID = b.ITEM_ID
JOIN     SUBJT AS c
   ON b.SUBJ_ID = c.SUBJ_ID
GROUP BY a.ITEM_ID. c.SUBJ_NAME;

我不得不稍微改變一下你的桌子。檢查是否可以確保外鍵與完全相同的數據類型的列相關以避免轉換。

CREATE TABLE SUBJT (
   SUBJ_ID CHAR(4) PRIMARY KEY,
   SUBJ_NAME VARCHAR(25) NOT NULL,
   SUBJ_DESCRIPT VARCHAR(500)
)

--DROP TABLE ITEM

CREATE TABLE ITEM
(
   ITEM_ID CHAR(8) PRIMARY KEY,
   ITEM_NAME VARCHAR(25) NOT NULL,
   ITEM_DESCRIPT VARCHAR(500)
)


CREATE TABLE ITEM_SUBJECT (
   ITEM_ID CHAR(8),
   SUBJ_ID CHAR(4),
   PRIMARY KEY (ITEM_ID, SUBJ_ID),
   CONSTRAINT FK_01  FOREIGN KEY(ITEM_ID) REFERENCES ITEM(ITEM_ID),
   CONSTRAINT FK_02 FOREIGN KEY (SUBJ_ID) REFERENCES SUBJT(SUBJ_ID)
)


CREATE TABLE LOAN (
   LOAN_ID INT PRIMARY KEY,
   PAT_ID INT NOT NULL,
   ITEM_ID CHAR(8) NOT NULL,
   VOL_ID INT NOT NULL,
   ITEM_OUTD DATE,
   ITEM_RTD DATE,
   ITEM_REN INT
   --FOREIGN KEY (PAT_ID) REFERENCES PATRON,
   FOREIGN KEY (ITEM_ID) REFERENCES ITEM
   --FOREIGN KEY (VOL_ID) REFERENCES VOLUNTEER
)

-- i did it like this:

SELECT a.ITEM_ID, c.SUBJ_NAME
,count_ITEM_ID=count_big(a.ITEM_ID) as 
FROM LOAN AS a
INNER JOIN ITEM_SUBJECT AS b ON a.ITEM_ID = b.ITEM_ID
INNER JOIN SUBJT AS c ON b.SUBJ_ID = c.SUBJ_ID
GROUP BY a.ITEM_ID, c.SUBJ_NAME
ORDER BY C.SUBJ_NAME

確保您有正確的索引，嘗試擺脫該排序運算符：

引用自：https://dba.stackexchange.com/questions/233575