对于两个字段如A,B,怎么样有一条语句实现取出A是有重复的,同时B字段要不相同。我写了个方法很不好,而且还有个大问题。
select * from tonghang_dianping where dianping in (select dianping from tonghang_dianping group by dianping having count(*)>1) and dianping not in (select dianping from tonghang_dianping group by dianping ,sub_name having count(*)>1)
select * from tonghang_dianping where dianping in (select dianping from tonghang_dianping group by dianping having count(*)>1) and dianping not in (select dianping from tonghang_dianping group by dianping ,sub_name having count(*)>1)
from tonghang_dianping K JOIN (SELECT a ,COUNT(*) AS B FROM tonghang_dianping GROUP BY A ) L
ON K.A=L.A
WHERE b>1
from tonghang_dianping K JOIN (SELECT a ,COUNT(*) AS B FROM tonghang_dianping GROUP BY A ) L
ON K.A=L.A
WHERE l.B>1
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-23 18:55:16
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int)
insert [tb]
select 1,2 union all
select 1,2 union all
select 1,3 union all
select 2,4 union all
select 2,5
--------------开始查询--------------------------
;with f as
(
select b,count(1) as num from tb group by b having count(1)=1
)
select * from tb where b in (select b from f)
----------------结果----------------------------
/*a b
----------- -----------
1 3
2 4
2 5(3 行受影响)
*/
from tonghang_dianping K JOIN (SELECT a ,COUNT(*) AS B FROM tonghang_dianping GROUP BY A ) L
ON K.A=L.A
WHERE b>1
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-23 18:55:16
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int)
insert [tb]
select 1,2 union all
select 1,2 union all
select 1,3 union all
select 2,4 union all
select 2,5
--------------开始查询--------------------------
;with f as
(
select b,count(1) as num from tb group by b having count(1)=1
)
select * from tb t where exists (select 1 from f where b=t.b)
----------------结果----------------------------
/*a b
----------- -----------
1 3
2 4
2 5(3 行受影响)
*/
go
create table [tb]([a] int,[b] int)
insert [tb]
select 1,2 union all
select 1,2 union all
select 1,3 union all
select 2,4 union all
select 2,5SELECT DISTINCT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE A=T.A AND B<>T.B)(所影响的行数为 5 行)a b
----------- -----------
1 2
1 3
2 4
2 5(所影响的行数为 4 行)????