column1 column2 column3 column4
1 B C D
2 B C E
3 F F H第一条和第二条数据的列2 和列3 相等,任意取一条
要求结果:
2 B C E
3 F F H
或
1 B C D
3 F F H
1 B C D
2 B C E
3 F F H第一条和第二条数据的列2 和列3 相等,任意取一条
要求结果:
2 B C E
3 F F H
或
1 B C D
3 F F H
解决方案 »
- 如何在数据库上建立两个数据库内容完全相同,但数据库不同数据库。
- 求sql语句,感觉有点复杂
- 时间长度的表示
- Reporting Services 不能向263邮箱发送订阅邮件的问题
- 請教一個排序語句
- 如何获得exec sp_executesql 中的结果集?
- 请教:如何快速查找到一个字符串中的非a-z、A-Z、0-9、中文字符之外的字符存在,并删除!(数据量大)-急!
- 怎么样通过sqlserver得dts直接在两个数据库之间进行操作
- 关于SQL SERVER 的查询
- 运行select提示错误:LOB 数据类型节点的数据库 ID 11 (页 (1:3789),槽 67)不存在
- MSSQL2005 消息8614,级别16,insert exec不能嵌套
- 在线求一SQL查询,谢谢
where column1 in(
select min(column1)
from tbName
group by column2,column3
)
(column1 int, column2 varchar(10), column3 varchar(10),column4 varchar(10))
insert into t
select 1, 'B', 'C', 'D' union all
select 2, 'B', 'C', 'E' union all
select 3, 'F', 'F', 'H' select * from t a
where not exists (select 1 from t where a.column2=column2 and a.column3=column3 and column1<a.column1)column1 column2 column3 column4
----------- ---------- ---------- ----------
1 B C D
3 F F H(2 row(s) affected)
insert into #t select 1,'B','C','D'
union select 2,'B','C','E'
union select 3,'F','F','H'select * from #t where c1 in(select distinct max(c1) from #t group by c2,c3) --select * from #t where c1 in(select distinct min(c1) from #t group by c2,c3) drop table #t
----------------------
2 B C E
3 F F H
----------------
(2 行受影响)
(column1 int, column2 varchar(10), column3 varchar(10),column4 varchar(10))
insert into t
select 1, 'B', 'C', 'D' union all
select 2, 'B', 'C', 'E' union all
select 3, 'F', 'F', 'H' select * from t a
where not exists (select 1 from t where a.column2=column2 and a.column3=column3 and column1<a.column1)--orselect * from t a
where column1=(select min(column1) from t where a.column2=column2 and a.column3=column3 group by column2,column3)
column1 column2 column3 column4
----------- ---------- ---------- ----------
1 B C D
3 F F H(2 row(s) affected)
declare @Test table(column1 int,column2 varchar(1),column3 varchar(1),column4 varchar(1))
insert @Test
select 1,'B','C','D' union all
select 2,'B','C','E' union all
select 3,'F','F','H'select * from @Test a where column1 = (select top 1 column1 from @Test b, (select column2,column3 from @Test group by column2,column3) c where a.column2=b.column2 and a.column3=b.column3 and b.column2=c.column2 and b.column3=c.column3 order by newid())/*
column1 column2 column3 column4
----------- ------- ------- -------
1 B C D
3 F F H
*/
--方法1、
Select * From 表 As A Where Not Exists
(Select 1 From表 Where column2=A.column2 And column3=A.column3 And column1>A.column1)
Order By column1
--方法2、
Select * From 表 As A Where column1 in
(Select Max(column1) From 表 Where column2=A.column2 And column3=A.column3)
Order By column1---column2和column3相等取最小column1那条
--方法1、
Select * From 表 As A Where Not Exists
(Select 1 From 表 Where column2=A.column2 And column3=A.column3 And column1<A.column1)
Order By column1
--方法2、
Select * From 表 As A Where column1 in
(Select Min(column1) From 表 Where column2=A.column2 And column3=A.column3)
Order By column1
Declare @T Table(column1 int,column2 varchar(5),column3 varchar(5),column4 varchar(5))
Insert @T Select 1,'B','C','D'
Union All Select 2,'B','C','E'
Union All Select 3,'F','F','H'
Select * From @T
/*查询结果*/
---column2和column3相等取最大column1那条
--方法1、
Select * From @T As A Where Not Exists
(Select 1 From @T Where column2=A.column2 And column3=A.column3 And column1>A.column1)
Order By column1
--方法2、
Select * From @T As A Where column1 in
(Select Max(column1) From @T Where column2=A.column2 And column3=A.column3)
Order By column1---column2和column3相等取最小column1那条
--方法1、
Select * From @T As A Where Not Exists
(Select 1 From @T Where column2=A.column2 And column3=A.column3 And column1<A.column1)
Order By column1
--方法2、
Select * From @T As A Where column1 in
(Select Min(column1) From @T Where column2=A.column2 And column3=A.column3)
Order By column1
(column1 int, column2 varchar(10), column3 varchar(10),column4 varchar(10))
insert into t
select 1, 'B', 'C', 'D' union all
select 2, 'B', 'C', 'E' union all
select 3, 'F', 'F', 'H' select * from t a
where not exists (select 1 from t where a.column2=column2 and a.column3=column3 and column1<a.column1)
select * from t a
where not exists (select 1 from t where a.column2=column2 and a.column3=column3 and column1>a.column1)
就是随机的
------------------
这样取数据还从来没有做过,恐怕实现不了
insert into #t select 1,'B','C','D'
union select 2,'B','C','E'
union select 3,'F','F','H'select identity(int,1,1) a,* into #t1 from #t order by newid()
select c1,c2,c3,c4 from #t1 where a in(select distinct min(a) from #t1 group by c2,c3)--select * from #t where c1 in(select distinct min(c1) from #t group by c2,c3) drop table #t drop table #t1
---------------------------------------
1 B C D
3 F F H
---------------------------------------
2 B C E
3 F F H
Limpire:连续执行以下代码,每次返回的column1是随机的,但保证每组相同的都返回1条记录。
*/declare @Test table(column1 int,column2 varchar(1),column3 varchar(1),column4 varchar(1))
insert @Test
select 1,'B','C','D' union all
select 2,'B','C','E' union all
select 3,'B','D','F' union all
select 4,'B','C','G' union all
select 5,'B','C','H' union all
select 6,'F','F','I'select column1,column2,column3,column4
from
(
select *, groupno=column1-(select count(*) from @Test where column1<=c.column1 and column2=c.column2 and column3=c.column3) from @Test c
) a
where
column1=(select top 1 column1 from (select *, groupno=column1-(select count(*) from @Test where column1<=c.column1 and column2=c.column2 and column3=c.column3) from @Test c) b where a.groupno=b.groupno order by newid())
-------------------------------
呵呵,你测试过吗?你写的那个可以吗?...
就是因为测试过才有数据
Limpire:连续执行以下代码,每次返回的column1是随机的,但保证每组相同的都返回1条记录。
*/declare @Test table(column1 int,column2 varchar(1),column3 varchar(1),column4 varchar(1))
insert @Test
select 1,'B','C','D' union all
select 2,'B','C','E' union all
select 3,'B','D','F' union all
select 4,'B','C','G' union all
select 5,'B','C','H' union all
select 6,'F','F','I'/*
Limpire:如果column1不是连续唯一的,借助中间表生成连续唯一的ID。
*/declare @Temp table(ID int identity(1,1), column1 int,column2 varchar(1),column3 varchar(1),column4 varchar(1))
insert @Temp select * from @Testselect column1,column2,column3,column4
from
(
select *, groupno=ID-(select count(*) from @Temp where ID<=c.ID and column2=c.column2 and column3=c.column3) from @Temp c
) a
where
ID=(select top 1 ID from (select *, groupno=ID-(select count(*) from @Temp where ID<=c.ID and column2=c.column2 and column3=c.column3) from @Temp c) b where a.groupno=b.groupno order by newid())
select 2,'B','C','E' union all
select 3,'B','D','F' union all
select 4,'B','C','G' union all
select 5,'B','C','H' union all
select 6,'F','F','I'----------------------如果1,2,4,5算是一组的话,在生成连续唯一ID的时候这样就可以了:insert @Temp(column1,column2,column3,column4) select * from @Test order by column2,column3
不用那么麻烦,我的方法不是挺好的吗
不用那么麻烦,我的方法不是挺好的吗
----------------------------你的方法是可以,象我上面列的数据,如果1,2,4,5不算1组,算2组,因为中间有其他记录隔开,你的方法就不行了。还有我前面的方法就是将1,2,4,5归为一组的随机,根本需要什么临时表:declare @Test table(column1 int,column2 varchar(1),column3 varchar(1),column4 varchar(1))
insert @Test
select 1,'B','C','D' union all
select 2,'B','C','E' union all
select 3,'F','F','H'select * from @Test a where column1 = (select top 1 column1 from @Test b, (select column2,column3 from @Test group by column2,column3) c where a.column2=b.column2 and a.column3=b.column3 and b.column2=c.column2 and b.column3=c.column3 order by newid())/*
column1 column2 column3 column4
----------- ------- ------- -------
1 B C D
3 F F H
*/
insert into tb values(1,'B','C','D')
insert into tb values(2,'B','C','E')
insert into tb values(3,'F','F','H')
goselect a.* from tb a,
(select column2,column3,min(column1) column1 from tb group by column2,column3) b
where a.column2 = b.column2 and a.column3 = b.column3 and a.column1 = b.column1
/*
column1 column2 column3 column4
----------- ---------- ---------- ----------
1 B C D
3 F F H
(所影响的行数为 2 行)
*/select * from tb where column1 in (select min(column1) column1 from tb group by column2,column3)
/*
column1 column2 column3 column4
----------- ---------- ---------- ----------
1 B C D
3 F F H
(所影响的行数为 2 行)
*/select * from tb a where column1 = (select top 1 column1 from tb where column2 = a.column2 and column3 = a.column3)
column1 column2 column3 column4
----------- ---------- ---------- ----------
1 B C D
3 F F H
(所影响的行数为 2 行)
*/drop table tb
select * from table1 a where not exists(select * from table1 where column2=a.column2
and column3=a.column3 and column1>a.column1)
create table #t(c1 int,c2 char,c3 char,c4 char)
insert into #t select 1,'B','C','D'
union select 2,'B','C','E'
union select 3,'F','F','H'
declare @FLagValue int
set @FlagValue = round(rand(),0)
if @FlagValue=0
select * from #t where c1 in( select min(c1)as id from #t group by c2,c3)
else
select * from #t where c1 in( select max(c1)as id from #t group by c2,c3)---------------------------------------------------------------------------------
结果:
1 B C D
3 F F H
或者:2 B C E
3 F F H
declare @a table(id int identity(1,1),name1 varchar(20),name2 varchar(20),name3 varchar(20))
insert @a
select 'B','C','D'
union all
select 'B','C','E'
union all
select 'F','F','H'
declare @b table(id int,name1 varchar(20),name2 varchar(20),name3 varchar(20))insert @b
select top 1 * from @a a where exists(select 1 from @a b where a.name1=b.name1 and a.name2=b.name2 and a.id<>b.id) order by newid()
insert @b
select * from @a a where not exists(select 1 from @a b where a.name1=b.name1 and a.name2=b.name2 and a.id<>b.id)
select * from @b
执行结果为:
/*id name1 name2 name3
----------- -------------------- -------------------- --------------------
1 B C D
3 F F H(所影响的行数为 2 行)*/
再次执行为:
/*
id name1 name2 name3
----------- -------------------- -------------------- --------------------
2 B C E
3 F F H(所影响的行数为 2 行)
*/