我正在嘗試將SQL查詢轉換為作業MS-Access，有什么建議的方法嗎？謝謝你
SELECT
colldet.college,
COUNT(DISTINCT manuscript.p_name) AS A,
COUNT(DISTINCT CASE WHEN s_p = '????' THEN p_name END) AS B,
COUNT(DISTINCT CASE WHEN s_p = '???? ?????' THEN p_name END) AS C,
COUNT(DISTINCT CASE WHEN s_p = '????' THEN p_name END) AS D
FROM manuscript
RIGHT OUTER JOIN colldet
ON manuscript.coll_name = colldet.college
GROUP BY colldet.college
uj5u.com熱心網友回復：
正如 June7 所指出的，Access 不支持 COUNT DISTINCT，因此您需要在計數之前將事物區分開來。同樣正如 June7 建議的那樣，使用 Iif() 而不是 CASE。這是一個猜測：
SELECT Q.College,
COUNT(Q.A1) AS A,
COUNT(Q.B1) AS B,
COUNT(Q.C1) AS C,
COUNT(Q.D1) AS D
FROM (SELECT DISTINCT colldet.college,
manuscript.p_name AS A1,
IIF(s_p = '????',p_name,Null) AS B1,
IIF(s_p = '???? ?????',p_name,Null) AS C1,
IIF(s_p = '????',p_name,Null) AS D1
FROM manuscript
RIGHT OUTER JOIN colldet
ON manuscript.coll_name = colldet.college)
AS Q
GROUP BY Q.college
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/461422.html
上一篇：MS訪問用最近的非零行替換非零值