有一个表,数据如下content,sCount, eid,
asd 1 1
abc 2 1
adcd 3 1
asdfsdf 11 2
sdfsfd 22 2
asdf 33 2
ss 111 3
ssss 222 3
ss 333 3
ss 444 3
要求查询结果
asd 1 1
asdfsdf 11 2
ss 111 3即每个eid下sCount最小的所有记录
asd 1 1
abc 2 1
adcd 3 1
asdfsdf 11 2
sdfsfd 22 2
asdf 33 2
ss 111 3
ssss 222 3
ss 333 3
ss 444 3
要求查询结果
asd 1 1
asdfsdf 11 2
ss 111 3即每个eid下sCount最小的所有记录
解决方案 »
- alter index disable 还维护索引吗?
- 请教查询语句
- SQL2000如何创建两个表同时增加,删除,修改的触发器
- |zyciis| 200分求:sql语句导出ACCESS时 : 我的ACCESS数据库没有密码的时候是可以导出的,但我加了密码了后就提示出错 急 谢谢
- 多個字段中只要其中一個滿足條件,就篩選出來,這個語句怎麼寫?謝謝大家!
- 请教一个简单语句的写法!!!!!!!!!!在线等待!
- 复制数据库1中的n个表到数据库2中,如何操作?
- SQL里面能存储最大容量的是什么类型?有没有比TEXT大的?
- 关于数据库锁,望各位达人出手援助,谢谢
- ACESS 数据库中的日期/时间字段在sql语句中如何进行比较。
- sql 8152错误 将截断字符串或二进制数据
- varchar只读出255个字符
FROM LI,
(
SELECT MIN(SCOUNT) SCOUNT,eid
FROM LI
GROUP BY Eid ) LO
where LI.sCount = LO.sCount and LI.eid = Lo.eid
order by LO.eid
if object_id('[tab]') is not null drop table [tab]
create table [tab]([content] varchar(7),[sCount] int,[eid] int)
insert [tab]
select 'asd',1,1 union all
select 'abc',2,1 union all
select 'adcd',3,1 union all
select 'asdfsdf',11,2 union all
select 'sdfsfd',22,2 union all
select 'asdf',33,2 union all
select 'ss',111,3 union all
select 'ssss',222,3 union all
select 'ss',333,3 union all
select 'ss',444,3select * from tab t where not exists(select 1 from tab where eid=t.eid and scount<t.scount)/*
content sCount eid
------- ----------- -----------
asd 1 1
asdfsdf 11 2
ss 111 3(所影响的行数为 3 行)*/
/******************************************************************************************************************************************************
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录
整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go
--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时,效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)--SQL2005:方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID方法11:select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1生成结果:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1(2 行受影响)
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-24 17:14:56
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([content] varchar(7),[sCount] int,[eid] int)
insert [tb]
select 'asd',1,1 union all
select 'abc',2,1 union all
select 'adcd',3,1 union all
select 'asdfsdf',11,2 union all
select 'sdfsfd',22,2 union all
select 'asdf',33,2 union all
select 'ss',111,3 union all
select 'ssss',222,3 union all
select 'ss',333,3 union all
select 'ss',444,3--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where eid=t.eid and scount<t.scount)/*contents Count eid
asd 1 1
asdfsdf 11 2
ss 111 3
*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
if object_id('[tab]') is not null drop table [tab]
create table [tab]([content] varchar(7),[sCount] int,[eid] int)
insert [tab]
select 'asd',1,1 union all
select 'abc',2,1 union all
select 'adcd',3,1 union all
select 'asdfsdf',11,2 union all
select 'sdfsfd',22,2 union all
select 'asdf',33,2 union all
select 'ss',111,3 union all
select 'ssss',222,3 union all
select 'ss',333,3 union all
select 'ss',444,3 select * from tab b
where not exists(select * from tab where b.eid=eid and scount<b.scount )
/*-------------------
asd 1 1
asdfsdf 11 2
ss 111 3
-------------*/
not exists(select * from tab where eid=t.eid and scount<t.scount)原来这样就能找到最小值,学习了,能不能用Group By呢
asd 1 1
sdfsdf 1 1
abc 2 1
adcd 3 1
asdfsdf 11 2
sdfsfd 22 2
asdf 33 2
ss 111 3
ssss 222 3
ss 333 3
ss 444 3 要求查询的结果集中 eid 是唯一的,只出现一次
declare @tb table (content varchar(20),sCount int , eid int )
insert into @tb
select 'asd', 1 , 1 union all
select 'abc', 2 , 1 union all
select 'adcd ', 3, 1 union all
select 'asdfsdf', 11, 2 union all
select 'sdfsfd', 22, 2 union all
select 'asdf', 33, 2 union all
select 'ss ', 111, 3 union all
select 'ssss', 222, 3 union all
select 'ss ', 333, 3 union all
select 'ss ', 444, 3 union all
select 'tt', 111, 3
---------------------------------
select max(content),sCount,eid from @tb t where not exists(select 1 from @tb where eid=t.eid and scount<t.scount)
group by eid,sCount
go
create table [tb]([content] varchar(7),[sCount] int,[eid] int)
insert [tb]
select 'asd',1,1 union all
select 'sdfsdf',1,1 union all
select 'abc',2,1 union all
select 'adcd',3,1 union all
select 'asdfsdf',11,2 union all
select 'sdfsfd',22,2 union all
select 'asdf',33,2 union all
select 'ss',111,3 union all
select 'ssss',222,3 union all
select 'ss',333,3 union all
select 'ss',444,3
go
--select * from [tb]select content,scount,eid
from tb t
where not exists(select 1 from tb where eid=t.eid and (scount<t.scount or scount=t.scount and content<t.content))
/*
content scount eid
------- ----------- -----------
asd 1 1
asdfsdf 11 2
ss 111 3(3 行受影响)
*/
(
select content,Scount,edid,row_number() over(partition by eid order by scount ) Rownum
from Tab
)
select * from leno where Rownum = 1
insert @T
select 'asd',1,1 union all
select 'sdfsdf',1,1 union all
select 'abc',2,1 union all
select 'adcd',3,1 union all
select 'asdfsdf',11,2 union all
select 'sdfsfd',22,2 union all
select 'asdf',33,2 union all
select 'ss',111,3 union all
select 'ssss',222,3 union all
select 'ss',333,3 union all
select 'ss',444,3
--sql 2000办法
select *
from @T t
where not exists
(
select 1
from @T
where eid=t.eid
and scount<t.scount
)content sCount eid
------- ----------- -----------
asd 1 1
sdfsdf 1 1
asdfsdf 11 2
ss 111 3(4 行受影响)--SQL 2005 办法
select content,sCount,eid
from
(
select *,rank=rank() over(partition by [eid] order by [sCount])
from @T
) A
where rank=1
content sCount eid
------- ----------- -----------
asd 1 1
sdfsdf 1 1
asdfsdf 11 2
ss 111 3(4 行受影响)
select content,sCount,eid
from
(
select *,rank=rank() over(partition by [eid] order by [sCount])
from @T
) A
where rank=1
select max(content),sCount,eid from @tb t where not exists(select 1 from @tb where eid=t.eid and scount <t.scount)
group by eid,sCount
强淫还真是不少,特别是强人还收了不少的好帖!
幸好还看得懂这三句!
SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM TB SELECT content,sCount, eid FROM #TB T WHERE NOT EXISTS(SELECT 1 FROM #TB WHERE EID=T.EID AND T.ID>ID)
where not exists(select 1 from #T where t.content=content and t.eid>eid)
select * from #T t
where eid =(select min(eid) from #T where t.content = content )