现有一个问题是:有100位用户,每户一块电能表,每个月要用红外抄表器抄一下每块表的信息,这些信息包括:表号,户号,剩余电量,购电次数,总购电量,总用电量。 有可能会重复抄了,导致数据有些重复,但数据不一定完全一样,比如表号2、户号1的用户,第一次抄时:剩余电量=100,购电次数=3,总购电量=1200,总用电量=1100,而第二次抄时(表太多,抄表员也记不清此表有没有抄数了,所以重新抄了一下)的数据为:剩余电量=95,购电次数=3,总购电量=1200,总用电量=1105。现在的问题是:
这些数据全部导入到一个数据表中,如何将剩余电量最小的那条记录提取出来。有两种方法:
1、将剩余电量最小的那条记录提取出来,放到另一张表中,当然包括没有重复记录的也要放到另外的那个表中。
2、直接从原表中将非剩余电量最小的那些记录删除掉。 请问以上两个方案的SQL语句如何写。
这些数据全部导入到一个数据表中,如何将剩余电量最小的那条记录提取出来。有两种方法:
1、将剩余电量最小的那条记录提取出来,放到另一张表中,当然包括没有重复记录的也要放到另外的那个表中。
2、直接从原表中将非剩余电量最小的那些记录删除掉。 请问以上两个方案的SQL语句如何写。
from TB group by 表号,户号
FROM total
GROUP BY total.用户ID, total.购电次数, total.总购电量;
from (SELECT C.用户ID, Min(C.剩余电量) AS 剩余电量の最小, B.购电次数の最大
from (SELECT A.用户ID, Max(A.购电次数) AS 购电次数の最大 FROM total AS A GROUP BY A.用户ID) as B INNER JOIN total as C ON B.用户ID = C.用户ID AND (B.购电次数の最大 = C.购电次数)
GROUP BY C.用户ID, B.购电次数の最大) as D INNER JOIN total E ON (D.购电次数の最大 = E.购电次数) AND (E.剩余电量 = D.剩余电量の最小) AND (D.用户ID = E.用户ID);
(select 用户ID,表ID,min(剩余电量) as 剩余电量 from TB group by 用户ID,表ID )b on a.用户ID=b.用户ID and a.表ID=b.表ID and a.剩余电量=b.剩余电量
几次说的东西都不一样......select 表号,户号,max(购电次数) as 购电次数1,min(剩余电量) as 剩余电量1,
max(总购电量) as 总购电量1,max(总用电量) as 总用电量1 from table where 表号='22' and 户号='1'
delete from table1 a
where exists (select * from table1 b
where b.表号=a.表号
and b.购电次数=a.购电次数
and b.剩余电量<a.剩余电量)
(一)
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
http://topic.csdn.net/u/20100205/20/675e5d68-8d7c-4bf6-99aa-b368343b0290.html?99330
delete from table1 a
where exists (select * from table1 b
where b.表号=a.表号
and ( (b.购电次数=a.购电次数 and b.剩余电量<a.剩余电量)
or(b.购电次数>a.购电次数)
)
)