MS access DB和SQL来自同一查询错误
MS access DB和SQL来自同一查询错误
提问于 2014-07-08 11:19:33
我使用MS access DB和SQL，还有table2，我不知道怎么才能得到这样的结果。
name | total | ER| OPD | IPD
CRP | 4 | 1 | 2 | 1
BRUA | 1 | 1 | 0 | 0
BRUB | 2 | 0 | 1 | 1
total| 7 | 2 | 3 | 2我使用了这段代码，但是当我保存文件时，我得到了这个查询引用导致的错误循环引用
SELECT 'CRP' AS name,
count (CRP) AS total,
(select COUNT(CRP) FROM Table2 WHERE Section='ER' and CRP<>"") AS ER,
(SELECT COUNT(CRP) FROM Table2 WHERE Section='OPD' and CRP<>"" ) AS OPD,
(SELECT COUNT(CRP) FROM Table2 WHERE Section = 'IPD' and CRP <> "" ) AS IPD
FROM Table2
where CRP <> ""
UNION ALL
SELECT 'BRUA' AS name,
count (BRUA) AS total,
(select COUNT(BRUA) FROM Table2 WHERE Section='ER' and BRUA<>'' ) AS ER,
(SELECT COUNT(BRUA) FROM Table2 WHERE Section='OPD' and BRUA<>'' ) AS OPD,
(SELECT COUNT(BRUA) FROM Table2 WHERE Section = 'IPD' and BRUA <> '' ) AS IPD
FROM Table2
where BRUA <> ''
UNION ALL SELECT 'BRUB' AS name,
count (BRUB) AS total,
(select COUNT(BRUB) FROM Table2 WHERE Section='ER' and BRUB<>'') AS ER,
(SELECT COUNT(BRUB) FROM Table2 WHERE Section='OPD' and BRUB<>'') AS OPD,
(SELECT COUNT(BRUB) FROM Table2 WHERE Section = 'IPD' and BRUB <> '' ) AS IPD
FROM Table2
where BRUB <> ''
UNION ALL
SELECT 'Total' AS name, Sum(total) AS total1, Sum(ER) AS ER1,
Sum(OPD) AS OPD1, Sum(IPD) AS IPD1
from sertable;回答 1
Stack Overflow用户
发布于 2014-07-08 14:13:31
它告诉您，它无法区分列BRUB和
select COUNT(BRUB) FROM Table2 WHERE Section='ER' and BRUB<>''中的BRUB
SELECT 'BRUB' AS name,
count (BRUB) AS total,
(...) AS ER
FROM Table2
where BRUB <> ''您可以像这样显式地使用别名
SELECT 'CRP' AS name,
count(T1.CRP) AS total,
(select COUNT(Tcrp.CRP) FROM Table2 Tcrp WHERE Section='ER' and CRP<>"") AS ER,
(SELECT COUNT(Tcrp.CRP) FROM Table2 Tcrp WHERE Section='OPD' and CRP<>"" ) AS OPD,
(SELECT COUNT(Tcrp.CRP) FROM Table2 Tcrp WHERE Section = 'IPD' and CRP <> "" ) AS IPD
FROM Table2 T1
where T1.CRP <> ""
UNION ALL SELECT 'BRUA' AS name,
count(T2.BRUA) AS total,
(select COUNT(Tbrua.BRUA) FROM Table2 Tbrua WHERE Tbrua.Section='ER' and Tbrua.BRUA <>'' ) AS ER,
(SELECT COUNT(Tbrua.BRUA) FROM Table2 Tbrua WHERE Tbrua.Section='OPD' and Tbrua.BRUA <>'' ) AS OPD,
(SELECT COUNT(Tbrua.BRUA) FROM Table2 Tbrua WHERE Tbrua.Section = 'IPD' and Tbrua.BRUA <> '' ) AS IPD
FROM Table2 T2
where T2.BRUA <> ''
UNION ALL SELECT 'BRUB' AS name,
count(BRUB) AS total,
(select COUNT(Tbrub.BRUB) FROM Table2 Tbrub WHERE Tbrub.Section='ER' and Tbrub.BRUB<>'') AS ER,
(SELECT COUNT(Tbrub.BRUB) FROM Table2 Tbrub WHERE Tbrub.Section='OPD' and Tbrub.BRUB<>'') AS OPD,
(SELECT COUNT(Tbrub.BRUB) FROM Table2 Tbrub WHERE Tbrub.Section = 'IPD' and Tbrub.BRUB <> '' ) AS IPD
FROM Table2 T3
where T3.BRUB <> ''
UNION ALL
SELECT 'Total' AS name
, Sum(total) AS total1
, Sum(ER) AS ER1
, Sum(OPD) AS OPD1
, Sum(IPD) AS IPD1
from sertable;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24623163
复制相关文章
相似问题
腾讯云开发者
