表TblA (ID,NodeNo,Type,CarryDate)
数据:
1,‘010102’,‘1’,‘2009-10-01’
2,‘010102’,‘1’,‘2009-10-01’
3,‘010102’,‘1’,‘2009-10-01’
4,‘010103’,‘2’,‘2009-10-01’
5,‘010103’,‘2’,‘2009-10-01’
6,‘010104’,‘1’,‘2009-10-01’
7,‘010105’,‘1’,‘2009-10-01’
8,‘010105’,‘2’,‘2009-10-01’
9,‘010105’,‘2’,‘2009-10-01’
10,‘010105’,‘2’,‘2009-10-02’查询某一天网点相同,任务类型相同的的记录数大于1的记录:
结果: 2,‘010102’,‘1’,‘2009-10-01’
3,‘010102’,‘1’,‘2009-10-01’
5,‘010103’,‘2’,‘2009-10-01’
9,‘010105’,‘2’,‘2009-10-01’
数据:
1,‘010102’,‘1’,‘2009-10-01’
2,‘010102’,‘1’,‘2009-10-01’
3,‘010102’,‘1’,‘2009-10-01’
4,‘010103’,‘2’,‘2009-10-01’
5,‘010103’,‘2’,‘2009-10-01’
6,‘010104’,‘1’,‘2009-10-01’
7,‘010105’,‘1’,‘2009-10-01’
8,‘010105’,‘2’,‘2009-10-01’
9,‘010105’,‘2’,‘2009-10-01’
10,‘010105’,‘2’,‘2009-10-02’查询某一天网点相同,任务类型相同的的记录数大于1的记录:
结果: 2,‘010102’,‘1’,‘2009-10-01’
3,‘010102’,‘1’,‘2009-10-01’
5,‘010103’,‘2’,‘2009-10-01’
9,‘010105’,‘2’,‘2009-10-01’
解决方案 »
- 求更改数据库的数据类型的sql语句,在线等
- |zyciis| 当事务中执行的时候没有进行Commit 的时候 事务中的操作将会怎么样
- sql server 对象获取ID问题
- 能不能用SQLSERVER2000来生成SQL查询语言?
- 高手请帮忙,关于考勤的存贮过程
- 关于SQL SEVER 2005大数据量的问题
- 请问:一个SELECT语句里是不是只能有一个CASE?
- 各位高手帮帮小弟,高分赠送
- 提示“将 expression 转换为数据类型 float 时发生算术溢出错误。”请帮忙看看。
- 请教一个老问题: 在SQL SERVER 70 中, 用什么语句设置行级锁?要求是: 别人不能读, 也不能写。 另外, 该如何释放?
- SQL分组问题,高手来看看
- 用as别名条件查询问题,出现列名无效
from (select row=rownumber() over(partition by NodeNo,Type,CarryDate order by getdate()),* from tb)K
where row>1
where id in (
select id
from (
select nodeNO,type,max(id) as id
from tblA
group by nodeNO,type
having count(*)>0
)T
)
where exists(select 1 from tb where NodeNo=t.NodeNo and Type=t.type and CarryDate =t.carrydate and id<t.id)
select a.*
from TblA a
left join (select min(ID) ID,NodeNo,Type,CarryDate
from TblA
group by NodeNo,Type,CarryDate) b
on a.NodeNo = b.NodeNo
and a.Type = b.Type
and a.CarryDate = b.CarryDate
where b.ID is null
--> 测试时间:2009-12-10 15:30:10
--> 测试菜鸟:l8r
--> 我的淘宝:《戒色坊》http://shop36766744.taobao.com/[code=SQL]if object_id('[TblA]') is not null drop table [TblA]
create table [TblA]([ID] int,[NodeNo] varchar(6),[Type] int,[CarryDate] datetime)
insert [TblA]
select 1,'010102',1,'2009-10-01' union all
select 2,'010102',1,'2009-10-01' union all
select 3,'010102',1,'2009-10-01' union all
select 4,'010103',2,'2009-10-01' union all
select 5,'010103',2,'2009-10-01' union all
select 6,'010104',1,'2009-10-01' union all
select 7,'010105',1,'2009-10-01' union all
select 8,'010105',2,'2009-10-01' union all
select 9,'010105',2,'2009-10-01' union all
select 10,'010105',2,'2009-10-02'select ID,NodeNo,Type,CarryDate from (
select *,flag=(select count(1) from TblA where NodeNo=t.NodeNo and Type=t.Type and ID<=t.id and CarryDate=t.CarryDate) from [TblA] t)g
where flag<>1 and (select count(*) from TblA where NodeNo=g.NodeNo and Type=g.Type and CarryDate=g.CarryDate)>1/*
ID NodeNo Type CarryDate
----------- ------ ----------- ------------------------------------------------------
2 010102 1 2009-10-01 00:00:00.000
3 010102 1 2009-10-01 00:00:00.000
5 010103 2 2009-10-01 00:00:00.000
9 010105 2 2009-10-01 00:00:00.000(所影响的行数为 4 行)*/drop table [TblA]
from TblA a
left join (select min(ID) ID,NodeNo,Type,CarryDate
from TblA
group by NodeNo,Type,CarryDate) b
on a.ID = b.ID
and a.NodeNo = b.NodeNo
and a.Type = b.Type
and a.CarryDate = b.CarryDate
where b.ID is null
--> 测试时间:2009-12-10 15:30:10
--> 测试菜鸟:l8r
--> 我的淘宝:《戒色坊》http://shop36766744.taobao.com/if object_id('[TblA]') is not null drop table [TblA]
create table [TblA]([ID] int,[NodeNo] varchar(6),[Type] int,[CarryDate] datetime)
insert [TblA]
select 1,'010102',1,'2009-10-01' union all
select 2,'010102',1,'2009-10-01' union all
select 3,'010102',1,'2009-10-01' union all
select 4,'010103',2,'2009-10-01' union all
select 5,'010103',2,'2009-10-01' union all
select 6,'010104',1,'2009-10-01' union all
select 7,'010105',1,'2009-10-01' union all
select 8,'010105',2,'2009-10-01' union all
select 9,'010105',2,'2009-10-01' union all
select 10,'010105',2,'2009-10-02'select ID,NodeNo,Type,CarryDate from (
select *,flag=(select count(1) from TblA where NodeNo=t.NodeNo and Type=t.Type and ID<=t.id and CarryDate=t.CarryDate) from [TblA] t)g
where flag<>1 and (select count(*) from TblA where NodeNo=g.NodeNo and Type=g.Type and CarryDate=g.CarryDate)>1/*
ID NodeNo Type CarryDate
----------- ------ ----------- ------------------------------------------------------
2 010102 1 2009-10-01 00:00:00.000
3 010102 1 2009-10-01 00:00:00.000
5 010103 2 2009-10-01 00:00:00.000
9 010105 2 2009-10-01 00:00:00.000(所影响的行数为 4 行)*/drop table [TblA]
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-10 15:30:11
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[NodeNo] varchar(6),[Type] int,[CarryDate] datetime)
insert [tb]
select 1,'010102',1,'2009-10-01' union all
select 2,'010102',1,'2009-10-01' union all
select 3,'010102',1,'2009-10-01' union all
select 4,'010103',2,'2009-10-01' union all
select 5,'010103',2,'2009-10-01' union all
select 6,'010104',1,'2009-10-01' union all
select 7,'010105',1,'2009-10-01' union all
select 8,'010105',2,'2009-10-01' union all
select 9,'010105',2,'2009-10-01' union all
select 10,'010105',2,'2009-10-02'
--------------开始查询--------------------------
;with f as
(
select *,px=row_number()over(partition by NodeNo order by NodeNo) from tb
)
select ID,NodeNo,[Type],CarryDate from f where px<>1
----------------结果----------------------------
/*ID NodeNo Type CarryDate
----------- ------ ----------- -----------------------
2 010102 1 2009-10-01 00:00:00.000
3 010102 1 2009-10-01 00:00:00.000
5 010103 2 2009-10-01 00:00:00.000
8 010105 2 2009-10-01 00:00:00.000
9 010105 2 2009-10-01 00:00:00.000
10 010105 2 2009-10-02 00:00:00.000(6 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-10 15:30:11
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[NodeNo] varchar(6),[Type] int,[CarryDate] datetime)
insert [tb]
select 1,'010102',1,'2009-10-01' union all
select 2,'010102',1,'2009-10-01' union all
select 3,'010102',1,'2009-10-01' union all
select 4,'010103',2,'2009-10-01' union all
select 5,'010103',2,'2009-10-01' union all
select 6,'010104',1,'2009-10-01' union all
select 7,'010105',1,'2009-10-01' union all
select 8,'010105',2,'2009-10-01' union all
select 9,'010105',2,'2009-10-01' union all
select 10,'010105',2,'2009-10-02'
--------------开始查询--------------------------
select
ID,NodeNo,Type,CarryDate
from
(select px=row_number() over(partition by NodeNo,Type,CarryDate order by getdate()),* from tb)t
where
px>1
----------------结果----------------------------
/*ID NodeNo Type CarryDate
----------- ------ ----------- -----------------------
2 010102 1 2009-10-01 00:00:00.000
3 010102 1 2009-10-01 00:00:00.000
5 010103 2 2009-10-01 00:00:00.000
9 010105 2 2009-10-01 00:00:00.000(4 行受影响
*/
go
drop table TblA
create table TblA(ID int,NodeNo varchar(8),[Type] int ,CarryDate datetime)
insert TblA select '1','010102','1','2009-10-01'
union all select '2','010102','1','2009-10-01'
union all select '3','010102','1','2009-10-01'
union all select '4','010103','2','2009-10-01'
union all select '5','010103','2','2009-10-01'
union all select '6','010104','1','2009-10-01'
union all select '7','010105','1','2009-10-01'
union all select '8','010105','2','2009-10-01'
union all select '9','010105','2','2009-10-01'
union all select '10','010105','2','2009-10-02'
;with cte as
(select *,id1=row_number() over (partition by NodeNo order by ID)
from TblA where datediff(dd,CarryDate,'2009-10-01')=0)
select a.ID,a.NodeNo,a.[Type],a.CarryDate
from cte a
where exists (select 1 from cte where NodeNo=a.NodeNo and [Type]=a.[Type] and id1<a.id1)
/*(10 行受影响)
ID NodeNo Type CarryDate
----------- -------- ----------- -----------------------
2 010102 1 2009-10-01 00:00:00.000
3 010102 1 2009-10-01 00:00:00.000
5 010103 2 2009-10-01 00:00:00.000
9 010105 2 2009-10-01 00:00:00.000(4 行受影响)
*/
if object_id('[TblA]') is not null drop table [TblA]
create table [TblA]([ID] int,[NodeNo] varchar(6),[Type] int,[CarryDate] datetime)
insert [TblA]
select 1,'010102',1,'2009-10-01' union all
select 2,'010102',1,'2009-10-01' union all
select 3,'010102',1,'2009-10-01' union all
select 4,'010103',2,'2009-10-01' union all
select 5,'010103',2,'2009-10-01' union all
select 6,'010104',1,'2009-10-01' union all
select 7,'010105',1,'2009-10-01' union all
select 8,'010105',2,'2009-10-01' union all
select 9,'010105',2,'2009-10-01' union all
select 10,'010105',2,'2009-10-02'
;WITH tbl AS
(SELECT nID = ROW_NUMBER()OVER(PARTITION BY [NodeNo],[Type],[CarryDate] ORDER BY ID),* FROM TblA)
SELECT ID,[NodeNo],[Type],[CarryDate] FROM tbl WHERE nID > 1/*
ID NodeNo Type CarryDate
2 010102 1 2009-10-01 00:00:00.000
3 010102 1 2009-10-01 00:00:00.000
5 010103 2 2009-10-01 00:00:00.000
9 010105 2 2009-10-01 00:00:00.000
*/
go
create table [tb]([ID] int,[NodeNo] varchar(6),[Type] int,[CarryDate] datetime)
insert [tb]
select 1,'010102',1,'2009-10-01' union all
select 2,'010102',1,'2009-10-01' union all
select 3,'010102',1,'2009-10-01' union all
select 4,'010103',2,'2009-10-01' union all
select 5,'010103',2,'2009-10-01' union all
select 6,'010104',1,'2009-10-01' union all
select 7,'010105',1,'2009-10-01' union all
select 8,'010105',2,'2009-10-01' union all
select 9,'010105',2,'2009-10-01' union all
select 10,'010105',2,'2009-10-02'
--------------开始查询--------------------------
select ID,NodeNo,Type,CarryDate from (
select *,flag = (select top 1 id from tb where NodeNo=t.NodeNo and Type=t.Type and CarryDate=t.CarryDate order by id) from [Tb] t) g
where flag <> id
----------------结果----------------------------
/*ID NodeNo Type CarryDate
----------- ------ ----------- -----------------------
2 010102 1 2009-10-01 00:00:00.000
3 010102 1 2009-10-01 00:00:00.000
5 010103 2 2009-10-01 00:00:00.000
9 010105 2 2009-10-01 00:00:00.000(4 行受影响
*/
insert [TblA]
select 1,'010102',1,'2009-10-01' union all
select 2,'010102',1,'2009-10-01' union all
select 3,'010102',1,'2009-10-01' union all
select 4,'010103',2,'2009-10-01' union all
select 5,'010103',2,'2009-10-01' union all
select 6,'010104',1,'2009-10-01' union all
select 7,'010105',1,'2009-10-01' union all
select 8,'010105',2,'2009-10-01' union all
select 9,'010105',2,'2009-10-01' union all
select 10,'010105',2,'2009-10-02'
goselect m.* from TblA m where
ID not in (select min(id) from TblA where NodeNo = m.NodeNo and Type = m.Type and CarryDate = m.CarryDate)
and exists (select 1 from
(select NodeNo,Type,CarryDate from tbla group by NodeNo,Type,CarryDate having count(1) > 1) n
where NodeNo = m.NodeNo and Type = m.Type and CarryDate = m.CarryDate)drop table tbla/*
ID NodeNo Type CarryDate
----------- ------ ----------- ------------------------------------------------------
2 010102 1 2009-10-01 00:00:00.000
3 010102 1 2009-10-01 00:00:00.000
5 010103 2 2009-10-01 00:00:00.000
9 010105 2 2009-10-01 00:00:00.000(所影响的行数为 4 行)
*/
create table [TblA]([ID] int,[NodeNo] varchar(6),[Type] int,[CarryDate] datetime)
insert [TblA]
select 1,'010102',1,'2009-10-01' union all
select 2,'010102',1,'2009-10-01' union all
select 3,'010102',1,'2009-10-01' union all
select 4,'010103',2,'2009-10-01' union all
select 5,'010103',2,'2009-10-01' union all
select 6,'010104',1,'2009-10-01' union all
select 7,'010105',1,'2009-10-01' union all
select 8,'010105',2,'2009-10-01' union all
select 9,'010105',2,'2009-10-01' union all
select 10,'010105',2,'2009-10-02'select * from TblA a where exists (select * from TblA where a.NodeNo=NodeNo and a.Type=Type and a.CarryDate=CarryDate and a.id>id)