难道是select * from tb t where exists(select 1 from tb where a=t.a and b=t.b and c=t.c)
要以ABC都相同来查询并取出数据 ?难道是 select * from tb where a=b and a=c and b=c
select * from tb where checksum(A,B,C) in (select checksum(A,B,C) from tb group by A,B,C having count(*)>1)
也可以 ---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-11-03 09:22:00 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col1] int,[col2] int,[col3] int,[col4] int,[col5] int) insert [tb] select 1,2,3,4,5 union all select 1,2,3,5,6 union all select 1,2,3,7,8 union all select 2,3,5,6,7 union all select 3,4,5,7,7 union all select 1,2,3,4,7 union all select 4,5,6,7,8 --------------开始查询-------------------------- select * from tb where LTRIM(col1)+LTRIM(col2)+LTRIM(col3) in(select LTRIM(col1)+LTRIM(col2)+LTRIM(col3) from tb group by LTRIM(col1)+LTRIM(col2)+LTRIM(col3) having COUNT(1)>1) ----------------结果---------------------------- /* col1 col2 col3 col4 col5 ----------- ----------- ----------- ----------- ----------- 1 2 3 4 5 1 2 3 5 6 1 2 3 7 8 1 2 3 4 7(4 行受影响)*/
select a.* from tab a inner join (select A,B,C from tab group by A,B,C having count(*)>1) b on a.A=b.A and a.B=b.B and a.C=b.C
create table tb(A varchar(3),B varchar(3),C varchar(3),D varchar(3),E varchar(3),F varchar(3),G varchar(3),H varchar(3),I varchar(3),J varchar(3))--字段名 insert into tb select '1','2','3','6','U','O','9','0','B','3' insert into tb select '1','2','3','6','1','1','1','0','B','3' --以上二组123相同找出第1笔 insert into tb select '4','5','6','7','7','O','9','7','B','3' insert into tb select '4','5','7','7','7','O','9','7','B','3' insert into tb select '4','5','6','7','7','O','9','7','B','3' --以上找出45相同但456第一次出现所以找出456的1笔 insert into tb select '1','3','m','7','9','2','4','6','8','0' insert into tb select '1','3','a','7','9','2','4','6','8','0' insert into tb select '1','3','a','7','9','2','4','6','8','0' --以上找出13m那笔 insert into tb select '7','8','9','a','a','a','b','n','5','9' --找出789的1笔 go ;with cte as( select *,ROW_NUMBER()over(order by (select 1))rn from tb ) select * from cte a where not exists (select 1 from cte where a=a.A and b=a.b and rn<a.rn) /* A B C D E F G H I J rn ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -------------------- 1 2 3 6 U O 9 0 B 3 1 4 5 6 7 7 O 9 7 B 3 3 1 3 m 7 9 2 4 6 8 0 6 7 8 9 a a a b n 5 9 9(4 行受影响)*/ go drop table tb
因为我要放在java里对db查询怎么可以缩为单句?
方法2,create table tb(A varchar(3),B varchar(3),C varchar(3),D varchar(3),E varchar(3),F varchar(3),G varchar(3),H varchar(3),I varchar(3),J varchar(3))--字段名insert into tb select '1','2','3','6','U','O','9','0','B','3' insert into tb select '1','2','3','6','1','1','1','0','B','3' --以上二组123相同找出第1笔 insert into tb select '4','5','6','7','7','O','9','7','B','3' insert into tb select '4','5','7','7','7','O','9','7','B','3' insert into tb select '4','5','6','7','7','O','9','7','B','3' --以上找出45相同但456第一次出现所以找出456的1笔 insert into tb select '1','3','m','7','9','2','4','6','8','0' insert into tb select '1','3','a','7','9','2','4','6','8','0' insert into tb select '1','3','a','7','9','2','4','6','8','0' --以上找出13m那笔 insert into tb select '7','8','9','a','a','a','b','n','5','9' --找出789的1笔select t1.A,t1.B,t1.C,t1.D,t1.E,t1.F,t1.G,t1.H,t1.I,t1.J from (select *,row_number() over(partition by A,B order by (select 0)) rn from tb) t1 where rn=1A B C D E F G H I J ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- 1 2 3 6 U O 9 0 B 3 1 3 m 7 9 2 4 6 8 0 4 5 6 7 7 O 9 7 B 3 7 8 9 a a a b n 5 9
是什么意思?
select * from tb where a=b and a=c and b=c
where checksum(A,B,C) in (select checksum(A,B,C) from tb group by A,B,C having count(*)>1)
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-03 09:22:00
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] int,[col2] int,[col3] int,[col4] int,[col5] int)
insert [tb]
select 1,2,3,4,5 union all
select 1,2,3,5,6 union all
select 1,2,3,7,8 union all
select 2,3,5,6,7 union all
select 3,4,5,7,7 union all
select 1,2,3,4,7 union all
select 4,5,6,7,8
--------------开始查询--------------------------
select * from tb where LTRIM(col1)+LTRIM(col2)+LTRIM(col3) in(select LTRIM(col1)+LTRIM(col2)+LTRIM(col3) from tb group by LTRIM(col1)+LTRIM(col2)+LTRIM(col3) having COUNT(1)>1)
----------------结果----------------------------
/* col1 col2 col3 col4 col5
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
1 2 3 5 6
1 2 3 7 8
1 2 3 4 7(4 行受影响)*/
select a.*
from tab a
inner join
(select A,B,C from tab group by A,B,C having count(*)>1) b
on a.A=b.A and a.B=b.B and a.C=b.C
1|2|3|6|U|O|9|0|B|3
1|2|3|6|1|1|1|0|B|3 <--以上二组123相同找出第1笔
4|5|6|7|7|O|9|7|B|3
4|5|7|7|7|O|9|7|B|3
4|5|6|7|7|O|9|7|B|3 <--以上找出45相同但456第一次出现所以找出456的1笔
1|3|m|7|9|2|4|6|8|0
1|3|a|7|9|2|4|6|8|0
1|3|a|7|9|2|4|6|8|0 <--以上找出13m那笔
7|8|9|a|a|a|b|n|5|9 <--找出789的1笔总的来说是找出前2个字段相同且出现的第一条数据
insert into tb select '1','2','3','6','U','O','9','0','B','3'
insert into tb select '1','2','3','6','1','1','1','0','B','3' --以上二组123相同找出第1笔
insert into tb select '4','5','6','7','7','O','9','7','B','3'
insert into tb select '4','5','7','7','7','O','9','7','B','3'
insert into tb select '4','5','6','7','7','O','9','7','B','3' --以上找出45相同但456第一次出现所以找出456的1笔
insert into tb select '1','3','m','7','9','2','4','6','8','0'
insert into tb select '1','3','a','7','9','2','4','6','8','0'
insert into tb select '1','3','a','7','9','2','4','6','8','0' --以上找出13m那笔
insert into tb select '7','8','9','a','a','a','b','n','5','9' --找出789的1笔
go
;with cte as(
select *,ROW_NUMBER()over(order by (select 1))rn from tb
)
select * from cte a where not exists (select 1 from cte where a=a.A and b=a.b and rn<a.rn)
/*
A B C D E F G H I J rn
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --------------------
1 2 3 6 U O 9 0 B 3 1
4 5 6 7 7 O 9 7 B 3 3
1 3 m 7 9 2 4 6 8 0 6
7 8 9 a a a b n 5 9 9(4 行受影响)*/
go
drop table tb
因为我要放在java里对db查询怎么可以缩为单句?
insert into tb select '1','2','3','6','1','1','1','0','B','3' --以上二组123相同找出第1笔
insert into tb select '4','5','6','7','7','O','9','7','B','3'
insert into tb select '4','5','7','7','7','O','9','7','B','3'
insert into tb select '4','5','6','7','7','O','9','7','B','3' --以上找出45相同但456第一次出现所以找出456的1笔
insert into tb select '1','3','m','7','9','2','4','6','8','0'
insert into tb select '1','3','a','7','9','2','4','6','8','0'
insert into tb select '1','3','a','7','9','2','4','6','8','0' --以上找出13m那笔
insert into tb select '7','8','9','a','a','a','b','n','5','9' --找出789的1笔select t1.A,t1.B,t1.C,t1.D,t1.E,t1.F,t1.G,t1.H,t1.I,t1.J
from (select *,row_number() over(partition by A,B order by (select 0)) rn from tb) t1
where rn=1A B C D E F G H I J
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1 2 3 6 U O 9 0 B 3
1 3 m 7 9 2 4 6 8 0
4 5 6 7 7 O 9 7 B 3
7 8 9 a a a b n 5 9