表结构:
ID USERID DEPTID INFO
1 A AA abc
2 B AA fhgfsd
3 C AA agaga
4 D BB abcabc
5 E BB trhr
6 F BB thth(数据多 就不写了)
功能是实现按DEPTID查询第一个匹配的INFO期待结果:DEPTID INFO
AA abc
BB abcabc大侠来帮忙啊 希望SQL查询实现
ID USERID DEPTID INFO
1 A AA abc
2 B AA fhgfsd
3 C AA agaga
4 D BB abcabc
5 E BB trhr
6 F BB thth(数据多 就不写了)
功能是实现按DEPTID查询第一个匹配的INFO期待结果:DEPTID INFO
AA abc
BB abcabc大侠来帮忙啊 希望SQL查询实现
解决方案 »
- 为什么我用SQL 05自带的MD5加密函数说发现不了???
- sql2005附加数据库 中文字符乱码
- Microsoft OLE DB Provider for SQL Server 错误 '80040e14' 第 1 行: '=' 附近有语法错误。
- 如何使用客户端的事件探查器跟踪服务器端的活动?
- 大早上还没回就说我回复太快,只能在开贴。----问下昨天的问题。
- 班竹们快来呀,大力哥你真好啊,救救我呀!!!!!
- 为什么我改了我的windows2000server的管理员密码后,重新登陆后,登陆sqlserver就失败?
- 这条sql怎么写呀,指点一下
- 望高手指点:sqlCommand的超时问题
- sqlserver2000中,怎样写大量数据的批量更新sql语句
- 获取用户可访问的数据库
- 散分SQL语句高分求助
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-27 17:55:52
-- 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,[USERID] varchar(1),[DEPTID] varchar(2),[INFO] varchar(6))
insert [tb]
select 1,'A','AA','abc' union all
select 2,'B','AA','fhgfsd' union all
select 3,'C','AA','agaga' union all
select 4,'D','BB','abcabc' union all
select 5,'E','BB','trhr' union all
select 6,'F','BB','thth'
--------------开始查询--------------------------select DEPTID,INFO from [tb] where charindex('abc',info)>0
----------------结果----------------------------
/* DEPTID INFO
------ ------
AA abc
BB abcabc(2 行受影响)*/
go
create table tb(ID int, USERID char(2), DEPTID char(4),INFO nvarchar(10))
insert tb
select
1, 'A' , 'AA', 'abc' union all select
2 , 'B' , 'AA' , 'fhgfsd' union all select
3 , 'C' , 'AA' , 'agaga' union all select
4 , 'D' , 'BB' , 'abcabc' union all select
5 , 'E' , 'BB' ,'trhr' union all select
6 , 'F', 'BB' ,'thth'
select * from tb a,(select DEPTID,MIN(id) as id from tb group by DEPTID) b
where a.DEPTID=b.DEPTID and a.ID=b.id
select * from tb a
where not exists(select 1 from tb where DEPTID=a.DEPTID and ID<a.ID)
/*
ID USERID DEPTID INFO
1 A AA abc
4 D BB abcabc
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-27 17:55:52
-- 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,[USERID] varchar(1),[DEPTID] varchar(2),[INFO] varchar(6))
insert [tb]
select 1,'A','AA','abc' union all
select 2,'B','AA','fhgfsd' union all
select 3,'C','AA','agaga' union all
select 4,'D','BB','abcabc' union all
select 5,'E','BB','trhr' union all
select 6,'F','BB','thth'
--------------开始查询--------------------------select
[DEPTID],[INFO]
from
[tb]
where
exists (select min([ID]) from [tb] group by [DEPTID]) and charindex('abc',info)>0
----------------结果----------------------------
/* DEPTID INFO
------ ------
AA abc
BB abcabc(2 行受影响)*/
/*
DEPTID INFO
AA abc
BB abcabc
*/
/*
DEPTID INFO
AA abc
BB abcabc
*/
/*
DEPTID INFO
AA abc
BB abcabc
*/
where not exists(select 1 from tb where DEPTID=a.DEPTID and ID<a.ID)
insert into @tselect 1 , 'A' , 'AA' , 'abc' union
select 2 , 'B' , 'AA' , 'fhgfsd' union
select 3 , 'C' , 'AA' , 'agaga' union
select 4 , 'D' , 'BB' , 'abcabc' union
select 5 , 'E' , 'BB' , 'trhr' union
select 6 , 'F' , 'BB' , 'thth' select t.DEPTID, t.INFO from @t t where exists(
select 1 from @t where DEPTID<>t.DEPTID and t.INFO like '%'+INFO+'%' )
union all
select t.DEPTID, t.INFO from @t t where exists(
select 1 from @t where DEPTID<>t.DEPTID and INFO like '%'+t.INFO+'%' )(所影响的行数为 6 行)DEPTID INFO
------ ----------
BB abcabc
AA abc(所影响的行数为 2 行)
go
create table [tb]([ID] int,[USERID] varchar(1),[DEPTID] varchar(2),[INFO] varchar(6))
insert [tb]
select 1,'A','AA','abc' union all
select 2,'B','AA','fhgfsd' union all
select 3,'C','AA','agaga' union all
select 4,'D','BB','abcabc' union all
select 5,'E','BB','trhr' union all
select 6,'F','BB','thth'--
select a.DEPTID , a.INFO from [tb] a ,
( select b.* from [tb] b , [tb] c
where b.[ID]<>c.[ID] and charindex(b.INFO,c.INFO)>0 ) d
where charindex(d.INFO,a.INFO)>0 --/* DEPTID INFO
-------- ------
--AA abc
--BB abcabc
--
--(2 行受影响)
--
--*/
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-09-27 20:31:32
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (ID INT,USERID VARCHAR(1),DEPTID VARCHAR(2),INFO VARCHAR(6))
INSERT INTO [tb]
SELECT 1,'A','AA','abc' UNION ALL
SELECT 2,'B','AA','fhgfsd' UNION ALL
SELECT 3,'C','AA','agaga' UNION ALL
SELECT 4,'D','BB','abcabc' UNION ALL
SELECT 5,'E','BB','trhr' UNION ALL
SELECT 6,'F','BB','thth'select * from [tb]select * from tb t
where not exists(select 1 from tb where id<t.id and deptid=t.deptid)
and info like '%abc%'