SELECT table2.*,
(SELECT Sum(qtde)
FROM table1
WHERE table1.cnes = table2.cnes
AND table1.procedimento = table2.procedimento
AND table1.mes = table2.mes
AND table1.ano = table2.ano
GROUP BY cnes,
procedimento,
ano,
mes) AS soma
FROM table2
This code works great in Access.
此代码在Access中非常有用。
I have two tables.
我有两张桌子。
Table1 has the columns: CNES, PROCEDIMENTO, MES, ANO, QTDE
表1列有:CNES,PROCEDIMENTO,MES,ANO,QTDE
It may contain information like this (and I can't change this fact, I get the info from another software)
它可能包含这样的信息(我不能改变这个事实,我从另一个软件获得信息)
+------+--------------+-----+-----+------+
| CNES | PROCEDIMENTO | MES | ANO | QTDE |
+------+--------------+-----+-----+------+
| C1 | P1 | M1 | A1 | 12 |
| C1 | P1 | M1 | A1 | 3 |
| C1 | P1 | M1 | A1 | 5 |
| C1 | P2 | M1 | A1 | 4 |
| C1 | P2 | M1 | A1 | 4 |
| C1 | P2 | M1 | A1 | 3 |
| C1 | P3 | M1 | A1 | 4 |
| C2 | P2 | M1 | A1 | 5 |
| C2 | P2 | M1 | A1 | 4 |
| C2 | P1 | M1 | A1 | 3 |
+------+--------------+-----+-----+------+
QTDE = quantity of procedures (PROCEDIMENTO), from an hospital (CNES), done in a month (MES) and year (ANO)
QTDE =来自医院(CNES)的程序数量(PROCEDIMENTO),在一个月内完成(MES)和一年(ANO)
So to know the quantity of procedures from one hospital to a single month i need to search all the rows and sum all the rows that's from that month...
因此,要知道从一个医院到一个月的程序数量,我需要搜索所有行并总结那个月的所有行...
Table2 has the columns: CNES, PROCEDIMENTO, NOME, MES, ANO, META
表2列有:CNES,PROCEDIMENTO,NOME,MES,ANO,META
This table contains the number of procedures the hospital needs to perform in a month (META) and the actual name of the procedure (NOME. PROCEDIMENTO is a number but i store it as string[code])
该表包含医院一个月内需要执行的程序数(META)和程序的实际名称(NOME.PROCEDIMENTO是一个数字,但我将其存储为字符串[code])
So my query, that works in Access, would do this from that example above
所以我的查询,在Access中工作,将从上面的例子中做到这一点
Query1
+------+--------------+------+-----+-----+------+------------+
| CNES | PROCEDIMENTO | NOME | MES | ANO | META | SUM(QTDE) |
+------+--------------+------+-----+-----+------+------------+
| C1 | P1 | N1 | M1 | A1 | 23 | 20 |
| C1 | P2 | N2 | M1 | A1 | 20 | 11 |
| C1 | P3 | N3 | M1 | A1 | 04 | 4 |
| C2 | P2 | N2 | M1 | A1 | 05 | 9 |
| C2 | P1 | N1 | M1 | A1 | 01 | 3 |
+------+--------------+------+-----+-----+------+------------+
If you take a closer look you will realize that the last column is the sum of quantity for that month so I can use that to see if the hospital did the quantity of procedures it was supposed to do in that month.
如果你仔细看看,你会发现最后一列是那个月的数量总和所以我可以用它来看看医院是否做了那个月应该做的程序数量。
Besides that, and my query doesn't do that, but I wish it did... I would need another column to show the % (SUM(QTDE)/META). But I need to make %% that are over 100% to be just 100%... and I need to careful with divisions by zero problems....
除此之外,我的查询不会这样做,但我希望它...我需要另一列来显示%(SUM(QTDE)/ META)。但是我需要将超过100%的%%变为100%......我需要小心零问题......
1 个解决方案
#1
0
I think this works fine in MySQL 5.5.30:
我认为这在MySQL 5.5.30中工作正常:
select *,
if(meta = 0 or soma > meta, 100, (soma/meta)*100) Percent
from
(
SELECT table2.*,
(SELECT Sum(qtde)
FROM table1
WHERE table1.cnes = table2.cnes
AND table1.procedimento = table2.procedimento
AND table1.mes = table2.mes
AND table1.ano = table2.ano
GROUP BY cnes,
procedimento,
ano,
mes) AS soma
FROM table2) TMP;
#1
0
I think this works fine in MySQL 5.5.30:
我认为这在MySQL 5.5.30中工作正常:
select *,
if(meta = 0 or soma > meta, 100, (soma/meta)*100) Percent
from
(
SELECT table2.*,
(SELECT Sum(qtde)
FROM table1
WHERE table1.cnes = table2.cnes
AND table1.procedimento = table2.procedimento
AND table1.mes = table2.mes
AND table1.ano = table2.ano
GROUP BY cnes,
procedimento,
ano,
mes) AS soma
FROM table2) TMP;