显示格式如:
name companyname 记录数
刘德华 007公司 15
张三联 aaa公司 120
李四 003公司 20
王五 ddd公司 30
14 个解决方案
#1
select * from info a where exists(select name from info where name=a.name group by name having count(*)>10)
#2
Select name,companyname ,count(*) as 記錄數
from info
group by name,companyname
having count(*)>10
#3
select *,(select count(*) from info where name=a.name) 记录数 from info a where exists(select name from info where name=a.name group by name having count(*)>10)
#4
select
name,
companyname ,
记录数=count(1)
from info
group by name,companyname
having count(1)>10
#5
2楼的可以。
#6
select a.*,b.记录数
from info a
inner join (
Select name,companyname ,count(*) as 记录数
from info
group by name,companyname
having count(*)>10
)b
on a.name=b.name and a.companyname=b.companyname
from info a
inner join (
Select name,companyname ,count(*) as 记录数
from info
group by name,companyname
having count(*)>10
)b
on a.name=b.name and a.companyname=b.companyname
#7
我支持这样.
#8
select name,companyname,COUNT(*) 记录数
from INFO group by name,companyname having COUNT(*)>10
#9
select
name,
companyname ,
cnt=count(*)
from info
group by name,companyname
having count(*)>10
#10
select name , count(1) 记录数 from info group by name having count(1) > 10
select COmpanyname , count(1) 记录数 from info group by COmpanyname having count(1) > 10
select m.name , m.companyname , n.记录数 from info m,
(select name , count(1) 记录数 from into group by name having count(1) > 10) n
where m.name = n.name
#11
create table test(name varchar(25),companyname varchar(50),记录数 varchar(20))
insert test select '刘德华','007公司','15' union all
select '张三联' , 'aaa公司 ' , '120' union all
select '李四' , '003公司 ' , '20' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '王五' , 'ddd公司 ' , '30'
go
select * from test;
select * from test a where exists(select name from test where name=a.name group by name having count(*)>10)
name companyname 记录数
------------------------- -------------------------------------------------- --------------------
刘德华 007公司 15
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
(所影响的行数为 13 行)
#12
select name,companyname,count(*) as 记录数
from info
group by name,companyname
having count(*)>50
#13
比较空值时必须小心。比较行为取决于 SET ANSI_NULLS 选项的设置。
当 SET ANSI_NULLS 为 ON 时,如果比较中有一个或多个表达式为 NULL,则既不输出 TRUE 也不输出 FALSE,而是输出 UNKNOWN。这是因为未知值不能与其他任何值进行逻辑比较。这种情况发生在一个表达式与 NULL 单词进行比较,或者两个表达式相比,而其中一个表达式取值为 NULL 时。
当 SET ANSI_NULLS 为 ON 时,如果比较中有一个或多个表达式为 NULL,则既不输出 TRUE 也不输出 FALSE,而是输出 UNKNOWN。这是因为未知值不能与其他任何值进行逻辑比较。这种情况发生在一个表达式与 NULL 单词进行比较,或者两个表达式相比,而其中一个表达式取值为 NULL 时。
#14
1.select * from test a where exists(select name from test where name=a.name group by name having count(*)>10)
2.select name as 姓名,companyname as 单位,count(1) as 记录数
from info
group by name,companyname
having count(1)>10
#1
select * from info a where exists(select name from info where name=a.name group by name having count(*)>10)
#2
Select name,companyname ,count(*) as 記錄數
from info
group by name,companyname
having count(*)>10
#3
select *,(select count(*) from info where name=a.name) 记录数 from info a where exists(select name from info where name=a.name group by name having count(*)>10)
#4
select
name,
companyname ,
记录数=count(1)
from info
group by name,companyname
having count(1)>10
#5
2楼的可以。
#6
select a.*,b.记录数
from info a
inner join (
Select name,companyname ,count(*) as 记录数
from info
group by name,companyname
having count(*)>10
)b
on a.name=b.name and a.companyname=b.companyname
from info a
inner join (
Select name,companyname ,count(*) as 记录数
from info
group by name,companyname
having count(*)>10
)b
on a.name=b.name and a.companyname=b.companyname
#7
我支持这样.
#8
select name,companyname,COUNT(*) 记录数
from INFO group by name,companyname having COUNT(*)>10
#9
select
name,
companyname ,
cnt=count(*)
from info
group by name,companyname
having count(*)>10
#10
select name , count(1) 记录数 from info group by name having count(1) > 10
select COmpanyname , count(1) 记录数 from info group by COmpanyname having count(1) > 10
select m.name , m.companyname , n.记录数 from info m,
(select name , count(1) 记录数 from into group by name having count(1) > 10) n
where m.name = n.name
#11
create table test(name varchar(25),companyname varchar(50),记录数 varchar(20))
insert test select '刘德华','007公司','15' union all
select '张三联' , 'aaa公司 ' , '120' union all
select '李四' , '003公司 ' , '20' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '刘德华','地势','8' union all
select '王五' , 'ddd公司 ' , '30'
go
select * from test;
select * from test a where exists(select name from test where name=a.name group by name having count(*)>10)
name companyname 记录数
------------------------- -------------------------------------------------- --------------------
刘德华 007公司 15
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
刘德华 地势 8
(所影响的行数为 13 行)
#12
select name,companyname,count(*) as 记录数
from info
group by name,companyname
having count(*)>50
#13
比较空值时必须小心。比较行为取决于 SET ANSI_NULLS 选项的设置。
当 SET ANSI_NULLS 为 ON 时,如果比较中有一个或多个表达式为 NULL,则既不输出 TRUE 也不输出 FALSE,而是输出 UNKNOWN。这是因为未知值不能与其他任何值进行逻辑比较。这种情况发生在一个表达式与 NULL 单词进行比较,或者两个表达式相比,而其中一个表达式取值为 NULL 时。
当 SET ANSI_NULLS 为 ON 时,如果比较中有一个或多个表达式为 NULL,则既不输出 TRUE 也不输出 FALSE,而是输出 UNKNOWN。这是因为未知值不能与其他任何值进行逻辑比较。这种情况发生在一个表达式与 NULL 单词进行比较,或者两个表达式相比,而其中一个表达式取值为 NULL 时。
#14
1.select * from test a where exists(select name from test where name=a.name group by name having count(*)>10)
2.select name as 姓名,companyname as 单位,count(1) as 记录数
from info
group by name,companyname
having count(1)>10