有一个表,数据如下
id names style
A01 dd c
A02 dd c
A03 fg a
A04 ff a
A05 gg c
A06 fg c
A07 gg c
A08 dd c现在想得到names和style都相同的数据
id names style
A01 dd c
A02 dd c
A08 dd c
A05 gg c
A07 gg c
这个语句,我用游标实现了,但比较慢,有没其他更快更好的方法?
id names style
A01 dd c
A02 dd c
A03 fg a
A04 ff a
A05 gg c
A06 fg c
A07 gg c
A08 dd c现在想得到names和style都相同的数据
id names style
A01 dd c
A02 dd c
A08 dd c
A05 gg c
A07 gg c
这个语句,我用游标实现了,但比较慢,有没其他更快更好的方法?
解决方案 »
- 关于三级库存管理的统计汇总问题?晕死了,请大家帮助!!!
- 〓〓为什么执行的语句被截断(长度超过4000)了?!!!〓〓
- 1分感谢谢兄弟。
- Sql server 数据导到oralce(类型)
- 疑难sql语句,大家帮忙看看?
- 2005的数据库文件,能转成2000数据库的文件么?
- 还原数据库失败,提示“卷不是媒体家族的成员,还原失败!”
- 局域网内的服务器连接
- 基本问题:
- sql server2000中的img字段(图片)用select into到变量然后updateblob到asa6.0中后,再提取该记录的binary字段时长度却为0,为什么?
- 请教一个日期问题
- win2000上用ADO连异机上的数据库失败
group by names,style
having count(*)>1
(select name,style ,count(*) from tb group by name,style having(count(*))>1)
b on a.name=b.name and a.style=b.style
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id varchar(10),name varchar(10),style varchar(10))
insert tb
select
'A01', 'dd', 'c' union all select
'A02', 'dd', 'c'union all select
'A03', 'fg', 'a' union all select
'A04', 'ff', 'a' union all select
'A05', 'gg', 'c' union all select
'A06', 'fg', 'c' union all select
'A07', 'gg', 'c' union all select
'A08', 'dd' , 'c'
go
select * from tb where name in(
select name from tb
group by name,style
having count(*)>1)
/*------------
A01 dd c
A02 dd c
A05 gg c
A07 gg c
A08 dd c
-------*/
create table #a(a nvarchar(20),b nvarchar(20),c nvarchar(20))
insert into #a(a,b,c)
select 'A01', 'dd' , 'c' union all
select'A02' , 'dd' , 'c' union all
select'A03' ,'fg' , 'a' union all
select'A04' ,'ff' , 'a' union all
select'A05' ,'gg' , 'c' union all
select'A06' ,'fg' , 'c' union all
select'A07' ,'gg' , 'c' union all
select'A08' ,'dd', 'c' select * from #a where a not in(
select Max(a) from #a
group by b,c
HAVING COunt(1)=1)
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-09 16:43:50
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] nvarchar(3),[names] nvarchar(2),[style] nvarchar(1))
Insert tb
Select 'A01','dd','c' union all
Select 'A02','dd','c' union all
Select 'A03','fg','a' union all
Select 'A04','ff','a' union all
Select 'A05','gg','c' union all
Select 'A06','fg','c' union all
Select 'A07','gg','c' union all
Select 'A08','dd','c'
Go
--Select * from tb-->SQL查询如下:
select *
from tb t
where exists(
select 1
from tb
where [names]=t.[names]
and [style]=t.[style]
and id<>t.id)
order by 2,3
/*
id names style
---- ----- -----
A01 dd c
A02 dd c
A08 dd c
A05 gg c
A07 gg c(5 行受影响)
*/
SELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE NAMES=T.NAMES AND STYLE=T.STYLE AND ID<>T.ID)
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-09 16:55:11
----------------------------------------------------------------
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] varchar(3),[names] varchar(2),[style] varchar(1))
insert [TB]
select 'A01','dd','c' union all
select 'A02','dd','c' union all
select 'A03','fg','a' union all
select 'A04','ff','a' union all
select 'A05','gg','c' union all
select 'A06','fg','c' union all
select 'A07','gg','c' union all
select 'A08','dd','c'
--------------开始查询--------------------------
SELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE NAMES=T.NAMES AND STYLE=T.STYLE AND ID<>T.ID)
----------------结果----------------------------
/*id names style
---- ----- -----
A01 dd c
A02 dd c
A05 gg c
A07 gg c
A08 dd c(所影响的行数为 5 行)
*/
'A09', 'ff' , 'a' union all select
'A10', 'ff' , 'd'
修改后
select a.* from tb a inner join
(select name,style ,count(*) as sss from tb group by name,style having(count(*))>1) b on a.name=b.name and a.style=b.style
或
select a.* from tb a,
(select name,style ,count(*) as sss from tb group by name,style having(count(*))>1) b where a.name=b.name and a.style=b.style
create table #a(a nvarchar(20),b nvarchar(20),c nvarchar(20))
insert into #a(a,b,c)
select 'A01', 'dd' , 'c' union all
select'A02' , 'dd' , 'c' union all
select'A03' ,'fg' , 'a' union all
select'A04' ,'ff' , 'a' union all
select'A05' ,'gg' , 'c' union all
select'A06' ,'fg' , 'c' union all
select'A07' ,'gg' , 'c' union all
select'A08' ,'dd', 'c' union all
select 'A09', 'ff' , 'a' union all
select 'A10', 'ff' , 'd' select * from #a where a not in(
select Max(a) from #a
group by b,c
HAVING COunt(1)=1)A01 dd c
A02 dd c
A04 ff a
A05 gg c
A07 gg c
A08 dd c
A09 ff a
drop table tab
go
CREATE TABLE [dbo].[tab](
[id] [nvarchar](10),
[names] [nvarchar](10),
[style] [nvarchar](10)
) ON [PRIMARY]
go
INSERT INTO tab(id,names,style) VALUES ('A01','dd','c')
INSERT INTO tab(id,names,style) VALUES ('A02','dd','c')
INSERT INTO tab(id,names,style) VALUES ('A03','fg','a')
INSERT INTO tab(id,names,style) VALUES ('A04','ff','a')
INSERT INTO tab(id,names,style) VALUES ('A05','gg','c')
INSERT INTO tab(id,names,style) VALUES ('A06','fg','c')
INSERT INTO tab(id,names,style) VALUES ('A07','gg','c')
INSERT INTO tab(id,names,style) VALUES ('A08','dd','c')
go
select a.id,b.names,b.style from tab a,(
select names,style from tab group by names,style having count(names)>=2)b
where a.names=b.names and a.style=b.style