1,select distinct * from tb 2,select 物料 价格 from tb group by 物料 价格
select * from (select distinct * from tb)t where 价格=(select max(价格) from (select distinct * from tb)t)
select 物料 , 价格 from tb group by 物料 , 价格 having count(1) > 1
select 物料,价格 from tb;select 物料,价格 from tb grou by 物料,价格 having count(*)>1
---修改 ---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-11-23 10:15:56 -- 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]([物料] varchar(4),[价格] int) insert [tb] select '毛笔',90 union all select '毛笔',90 union all select '毛笔',80 union all select '水笔',60 union all select '水笔',60 --------------开始查询-------------------------- select * from (select distinct * from tb)t where 价格=(select max(价格) from (select distinct * from tb)a where 物料=t.物料) ----------------结果---------------------------- /*物料 价格 ---- ----------- 水笔 60 毛笔 90(2 行受影响)
*/
select 物料,价格 from tb grou by 物料,价格 having count(*)>1
-- ============================================= -- Author: T.O.P -- Create date: 2009/11/23 -- Version: SQL SERVER 2005 -- ============================================= declare @tb table([物料] varchar(4),[价格] int) insert @tb select '毛笔',90 union all select '毛笔',90 union all select '毛笔',80 union all select '水笔',60 union all select '水笔',60select distinct * from @tb t where exists(select [物料], [价格], count([物料]) from @tb where t.[物料] = [物料] and t.[价格] = [价格] group by [物料], [价格] having count([物料])>1) --测试结果: /* 物料 价格 ---- ----------- 毛笔 90 水笔 60(2 row(s) affected)*/
select distinct * from tb 是不显示重复记录呀。我要显示重复记录呀
select 物料 , max(价格) from tb group by 物料
create table tb(物料 varchar(10), 价格 int) insert into tb values('毛笔' , 90) insert into tb values('毛笔' , 90) insert into tb values('毛笔' , 80 ) insert into tb values('水笔' , 60 ) insert into tb values('水笔' , 60 ) goselect 物料 , 价格 from tb group by 物料 , 价格 having count(1) > 1 drop table tb/* 物料 价格 ---------- ----------- 水笔 60 毛笔 90(所影响的行数为 2 行) */
select distinct 物料,max(价格) from tb group by 物料
看来还是理解错误了 我理解成求最大的值了 应该这样select 物料 , 价格 from tb group by 物料 , 价格 having count(1) > 1
declare @tb table([物料] varchar(4),[价格] int) insert @tb select '毛笔',90 union all select '毛笔',90 union all select '毛笔',80 union all select '水笔',60 union all select '水笔',60 select 物料 , max(价格) from @tb group by 物料 /* 物料 ---- ----------- 毛? 90 水? 60(2 個資料列受到影響)*/
-- ============================================= -- Author: T.O.P -- Create date: 2009/11/23 -- Version: SQL SERVER 2005 -- ============================================= declare @tb table([物料] varchar(4),[价格] int) insert @tb select '毛笔',90 union all select '毛笔',90 union all select '毛笔',80 union all select '水笔',60 union all select '水笔',60 --1 select distinct * from @tb t where exists(select [物料], [价格], count([物料]) from @tb where t.[物料] = [物料] and t.[价格] = [价格] group by [物料], [价格] having count([物料])>1)--2 select [物料], [价格] from @tb group by [物料], [价格] having count([物料])>1 --测试结果: /* 物料 价格 ---- ----------- 水笔 60 毛笔 90(2 row(s) affected)*/
如表A: 物料 价格 毛笔 90 毛笔 90 毛笔 80 水笔 60 水笔 60 我要通过SQL得到表A中显示重复的记录: 毛笔 90 水笔 60 group by 物料,价格 having count(*)>1
我要通过SQL得到表A中显示重复的记录:create table #t(id nvarchar(32),pr int) insert into #t select N'毛笔' , 90 union all select N'毛笔' , 90 union all select N'毛笔' ,80 union all select N'水笔' ,60 union all select N'水笔' , 60 union all select N'水笔' , 40 union all select N'水笔' , 40 union all select N'水笔' , 30 select id,pr from #t group by id,pr having count(id)>1
--> 测试数据: @tb declare @tb table (物料 varchar(4),价格 int) insert into @tb select '毛笔',90 union all select '毛笔',90 union all select '毛笔',80 union all select '水笔',60 union all select '水笔',60select * from @tb group by 物料 ,价格 having count(*)>1物料 价格 ---- ----------- 水笔 60 毛笔 90(2 行受影响)
--> 测试数据:@table declare @table table([物料] varchar(4),[价格] int) insert @table select '毛笔',90 union all select '毛笔',90 union all select '毛笔',80 union all select '水笔',60 union all select '水笔',60select 物料,价格 from @table group by 物料,价格 having count(1) > 1---------------------- 水笔 60 毛笔 90
select id,pr from table group by id,pr having count(id)>1
select 物料 , 价格 from tb group by 物料 , 价格 having count(1) > 1
1,select distinct * from tb
2,select 物料 价格 from tb group by 物料 价格
*
from
(select distinct * from tb)t
where
价格=(select max(价格) from (select distinct * from tb)t)
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-23 10:15:56
-- 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]([物料] varchar(4),[价格] int)
insert [tb]
select '毛笔',90 union all
select '毛笔',90 union all
select '毛笔',80 union all
select '水笔',60 union all
select '水笔',60
--------------开始查询--------------------------
select
*
from
(select distinct * from tb)t
where
价格=(select max(价格) from (select distinct * from tb)a where 物料=t.物料)
----------------结果----------------------------
/*物料 价格
---- -----------
水笔 60
毛笔 90(2 行受影响)
*/
-- Author: T.O.P
-- Create date: 2009/11/23
-- Version: SQL SERVER 2005
-- =============================================
declare @tb table([物料] varchar(4),[价格] int)
insert @tb
select '毛笔',90 union all
select '毛笔',90 union all
select '毛笔',80 union all
select '水笔',60 union all
select '水笔',60select distinct *
from @tb t
where exists(select [物料], [价格], count([物料]) from @tb where t.[物料] = [物料] and t.[价格] = [价格] group by [物料], [价格] having count([物料])>1)
--测试结果:
/*
物料 价格
---- -----------
毛笔 90
水笔 60(2 row(s) affected)*/
max(价格)
from tb group by 物料
insert into tb values('毛笔' , 90)
insert into tb values('毛笔' , 90)
insert into tb values('毛笔' , 80 )
insert into tb values('水笔' , 60 )
insert into tb values('水笔' , 60 )
goselect 物料 , 价格 from tb group by 物料 , 价格 having count(1) > 1 drop table tb/*
物料 价格
---------- -----------
水笔 60
毛笔 90(所影响的行数为 2 行)
*/
from tb
group by 物料
应该这样select 物料 , 价格 from tb group by 物料 , 价格 having count(1) > 1
insert @tb
select '毛笔',90 union all
select '毛笔',90 union all
select '毛笔',80 union all
select '水笔',60 union all
select '水笔',60
select 物料 ,
max(价格)
from @tb group by 物料
/*
物料
---- -----------
毛? 90
水? 60(2 個資料列受到影響)*/
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/23
-- Version: SQL SERVER 2005
-- =============================================
declare @tb table([物料] varchar(4),[价格] int)
insert @tb
select '毛笔',90 union all
select '毛笔',90 union all
select '毛笔',80 union all
select '水笔',60 union all
select '水笔',60
--1
select distinct *
from @tb t
where exists(select [物料], [价格], count([物料]) from @tb where t.[物料] = [物料] and t.[价格] = [价格] group by [物料], [价格] having count([物料])>1)--2
select [物料], [价格]
from @tb
group by [物料], [价格]
having count([物料])>1
--测试结果:
/*
物料 价格
---- -----------
水笔 60
毛笔 90(2 row(s) affected)*/
毛笔 90
毛笔 90
毛笔 80
水笔 60
水笔 60 我要通过SQL得到表A中显示重复的记录: 毛笔 90
水笔 60 group by 物料,价格 having count(*)>1
insert into #t
select N'毛笔' , 90 union all
select N'毛笔' , 90 union all
select N'毛笔' ,80 union all
select N'水笔' ,60 union all
select N'水笔' , 60 union all
select N'水笔' , 40 union all
select N'水笔' , 40 union all
select N'水笔' , 30
select id,pr from #t group by id,pr
having count(id)>1
declare @tb table (物料 varchar(4),价格 int)
insert into @tb
select '毛笔',90 union all
select '毛笔',90 union all
select '毛笔',80 union all
select '水笔',60 union all
select '水笔',60select * from @tb
group by 物料 ,价格
having count(*)>1物料 价格
---- -----------
水笔 60
毛笔 90(2 行受影响)
declare @table table([物料] varchar(4),[价格] int)
insert @table
select '毛笔',90 union all
select '毛笔',90 union all
select '毛笔',80 union all
select '水笔',60 union all
select '水笔',60select 物料,价格 from @table
group by 物料,价格
having count(1) > 1----------------------
水笔 60
毛笔 90
having count(id)>1