CREATETABLE T0120
(
ID int,
NAME varchar(22),
MON int,
STATE varchar(21),
AMOUNT int);INSERTINTO T0120 VALUES(1,'张三',201901,'A',9000);INSERTINTO T0120 VALUES(2,'张三',201901,'B',9000);INSERTINTO T0120 VALUES(3,'张三',201901,'E',9000);INSERTINTO T0120 VALUES(4,'李四',201902,'A',1800);INSERTINTO T0120 VALUES(5,'李四',201902,'C',1800);INSERTINTO T0120 VALUES(6,'王五',201902,'C',30000);INSERTINTO T0120 VALUES(7,'王五',201902,'F',30000);
T0120
+------+--------+--------+-------+--------+| ID | NAME | MON | STATE | AMOUNT |+------+--------+--------+-------+--------+|1| 张三 |201901| A |9000||2| 张三 |201901| B |9000||3| 张三 |201901| E |9000||4| 李四 |201902| A |1800||5| 李四 |201902| C |1800||6| 王五 |201902| C |30000||7| 王五 |201902| F |30000|+------+--------+--------+-------+--------+
结果
+------+--------+--------+-------+--------+| ID | NAME | MON | STATE | AMOUNT |+------+--------+--------+-------+--------+|1| 张三 |201901| A |3000||2| 张三 |201901| B |3000||3| 张三 |201901| E |3000||4| 李四 |201902| A |900||5| 李四 |201902| C |900||6| 王五 |201902| C |15000||7| 王五 |201902| F |15000|+------+--------+--------+-------+--------+SELECT ID,t.NAME,MON,STATE,ROUND(AMOUNT/t.C,0)AS AMOUNT
FROM T0120 LEFTJOIN(SELECT NAME,COUNT(*)AS C
FROM T0120
GROUPBY NAME,MON
)t
ON T0120.NAME = t.NAME
SELECT ID,NAME,MON,STATE,ROUND(AMOUNT/(SELECTCOUNT(1)FROM T0120 b WHERE a.MON = b.MON AND a.NAME = b.NAME))AS AMOUNT
FROM T0120 a
Mysql刷题笔记CREATE TABLE T0120( ID int, NAME varchar(22), MON int, STATE varchar(21), AMOUNT int);INSERT INTO T0120 VALUES(1,'张三',201901,'A',9000);INSERT INTO T0120 VALUES(2,'张三',201901,'B',9000);INSERT INTO T0120 VALUES(3,'张三',201901,'E',9000);