SQL案例：反向思维（发生额案例）

来源：互联网发布：生意专家软件好用吗编辑：程序博客网时间：2024/04/28 14:38

-- 建表CREATE TABLE TestDB(   id INT PRIMARY KEY AUTO_INCREMENT,   AccID VARCHAR(20),    Occmonth DATE,   DebitOccur BIGINT);-- 添加数据INSERT INTO TestDB VALUES(NULL,'101','1988-1-1',100),(NULL,'101','1988-2-1',110),(NULL,'101','1988-3-1',120),(NULL,'101','1988-4-1',100),(NULL,'101','1988-5-1',100),(NULL,'101','1988-6-1',100),(NULL,'101','1988-7-1',100),(NULL,'101','1988-8-1',100);INSERT INTO TestDB VALUES(NULL,'102','1988-1-1',90),(NULL,'102','1988-2-1',110),(NULL,'102','1988-3-1',120),(NULL,'102','1988-4-1',100),(NULL,'102','1988-5-1',100),(NULL,'102','1988-6-1',100),(NULL,'102','1988-7-1',100),(NULL,'102','1988-8-1',100);INSERT INTO TestDB VALUES(NULL,'103','1988-1-1',150),(NULL,'103','1988-2-1',160),(NULL,'103','1988-3-1',180),(NULL,'103','1988-4-1',120),(NULL,'103','1988-5-1',120),(NULL,'103','1988-6-1',120),(NULL,'103','1988-7-1',120),(NULL,'103','1988-8-1',120);INSERT INTO TestDB VALUES(NULL,'104','1988-1-1',130),(NULL,'104','1988-2-1',130),(NULL,'104','1988-3-1',140),(NULL,'104','1988-4-1',150),(NULL,'104','1988-5-1',160),(NULL,'104','1988-6-1',170),(NULL,'104','1988-7-1',180),(NULL,'104','1988-8-1',140);INSERT INTO TestDB VALUES(NULL,'105','1988-1-1',100),(NULL,'105','1988-2-1',80),(NULL,'105','1988-3-1',120),(NULL,'105','1988-4-1',100),(NULL,'105','1988-5-1',100),(NULL,'105','1988-6-1',100),(NULL,'105','1988-7-1',100),(NULL,'105','1988-8-1',100);-- 从 TestDB 数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目-- 难点：所有月份，即存在月份比101存在月份-- 错误答案（没有考虑所有月份）SELECT  a.AccID,a.DebitOccur , b.AccID, b.DebitOccurFROM testdb a ,testdb bWHERE a.Occmonth = b.Occmonth AND a.DebitOccur <= b.DebitOccur AND b.AccID = 101-- 思路：第一步：找出有比101科目月份发生额少的月份--       第二步：排除这些月份SELECT DISTINCT d.AccIDFROM TestDB dWHERE d.AccID NOT IN (  SELECT a.AccID  FROM testdb a ,testdb b  WHERE a.Occmonth = b.Occmonth AND a.DebitOccur <= b.DebitOccur AND b.AccID = 101)SELECT DISTINCT AccID FROM TestDBWHERE AccID NOT IN   (SELECT TestDB.AccID    FROM TestDB, (SELECT * FROM TestDB WHERE AccID='101') AS db101    WHERE TestDB.Occmonth=db101.Occmonth AND TestDB.DebitOccur<=db101.DebitOccur);

总结：
从正面进行判断：是否存在某月比101科目少产生发生额的科目，这样进行查找会比较麻烦，而且很难获取到准确的数据，因为科目的月份是不一定的，因此我们要进行反向思考
反向进行判断：只要存在某一个月比101科目少产生发生额的科目，就进行排除，得出一个结果集，使用not in 关键字即可

0 0