1 aaa 1班 98
2 bbb 1班 96
3 ccc 1班 98
4 ddd 1班 97
在不知道具体数据的情况下 怎么统计Point字段里 分数相同的所有数据
9 个解决方案
#1
表没有分数字段哪
#2
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (id int,name nvarchar(6),address nvarchar(4),Point int)
insert into [TB]
select 1,'aaa','1班',98 union all
select 2,'bbb','1班',96 union all
select 3,'ccc','1班',98 union all
select 4,'ddd','1班',97
select * from [TB]
SELECT A.* FROM TB A inner JOIN TB B ON A.NAME <> B.NAME AND A.point = B.point
/*
id name address Point
3 ccc 1班 98
1 aaa 1班 98*/
#3
就是98的那一列
#4
--只有两个相同
SELECT Point,COUNT(1)
FROM TB
GROUP BY Point
HAVING COUNT(1) = 2
--至少有两个相同
SELECT Point,COUNT(1)
FROM TB
GROUP BY Point
HAVING COUNT(1) > 1
#5
select * from tb t where exists(select 1 from tb where name<>t.name and point=t.point)
#6
要所有数据,则是
SELECT * FROM TB WHERE Point IN(
SELECT POINT
FROM TB
GROUP BY Point
HAVING COUNT(1) > 1
)
#7
没看懂楼主的意思。。。
这样吗?
select point,COUNT(point) from TB group by point
#8
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(3),[address] nvarchar(2),[Point] int)
Insert #T
select 1,N'aaa',N'1班',98 union all
select 2,N'bbb',N'1班',96 union all
select 3,N'ccc',N'1班',98 union all
select 4,N'ddd',N'1班',97
Go
Select a.* from #T AS a INNER JOIN (SELECT [Point] FROM #T GROUP BY [Point] HAVING COUNT(1)>1) AS b ON a.Point=b.Point
/*
id name address Point
1 aaa 1班 98
3 ccc 1班 98*/
#9
#1
表没有分数字段哪
#2
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (id int,name nvarchar(6),address nvarchar(4),Point int)
insert into [TB]
select 1,'aaa','1班',98 union all
select 2,'bbb','1班',96 union all
select 3,'ccc','1班',98 union all
select 4,'ddd','1班',97
select * from [TB]
SELECT A.* FROM TB A inner JOIN TB B ON A.NAME <> B.NAME AND A.point = B.point
/*
id name address Point
3 ccc 1班 98
1 aaa 1班 98*/
#3
就是98的那一列
#4
--只有两个相同
SELECT Point,COUNT(1)
FROM TB
GROUP BY Point
HAVING COUNT(1) = 2
--至少有两个相同
SELECT Point,COUNT(1)
FROM TB
GROUP BY Point
HAVING COUNT(1) > 1
#5
select * from tb t where exists(select 1 from tb where name<>t.name and point=t.point)
#6
要所有数据,则是
SELECT * FROM TB WHERE Point IN(
SELECT POINT
FROM TB
GROUP BY Point
HAVING COUNT(1) > 1
)
#7
没看懂楼主的意思。。。
这样吗?
select point,COUNT(point) from TB group by point
#8
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(3),[address] nvarchar(2),[Point] int)
Insert #T
select 1,N'aaa',N'1班',98 union all
select 2,N'bbb',N'1班',96 union all
select 3,N'ccc',N'1班',98 union all
select 4,N'ddd',N'1班',97
Go
Select a.* from #T AS a INNER JOIN (SELECT [Point] FROM #T GROUP BY [Point] HAVING COUNT(1)>1) AS b ON a.Point=b.Point
/*
id name address Point
1 aaa 1班 98
3 ccc 1班 98*/