34,838
社区成员




select distinct 姓名 from tb a where not exists(select 1 from tb where 姓名 = a.姓名 and 分数<> 90)
select right(rtrim(类别),len(rtrim(类别))-1),sum(点击) from tb
group byright(rtrim(类别) order by sum(点击)
--1.假设每个同学的每科成绩都已录入
select distinct 姓名 from 表 a where not exists(select * from 表 where 姓名=a.姓名 and 分数<>90)
--2.
select * from 表 order by stuff(类别,1,1,''),点击 desc
--> 测试数据: #1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (ID int,姓名 varchar(1),科目 varchar(4),分数 int)
insert into #1
select 1,'A','语文',80 union all
select 2,'A','数学',90 union all
select 3,'B','语文',90 union all
select 4,'B','数学',90 union all
select 5,'C','语文',90 union all
select 6,'C','数学',80
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (ID int,类别 varchar(4),价钱 int,点击 int)
insert into #2
select 1,'男装',120,2 union all
select 2,'女装',110,9 union all
select 3,'女裤',220,8 union all
select 4,'男裤',300,5 union all
select 5,'女鞋',800,9 union all
select 6,'男鞋',700,2
--> 找出分数都是90分的同学姓名:
select 姓名 from #1 t where not exists (select 1 from #1 where 姓名=t.姓名 and 分数<>90) group by 姓名 having count(1) = 2
--> 查询出同类别服装(男女为同类)按点击次数排序:
--> 这个问题描述不明确,我觉得楼主应该是要1男1女这样排,这就有点难搞,先抄上面的答案。
select * from #2 order by left(类别,1),点击 desc