求解SQL

Question

Examination_Id       Score1       UserId      Score2
-------------------- ----------- ----------- -----------
3377941455731250     100         560048475   0
3377941455731250     100         560048475   80
3377941455731250     100         560048475   65
3377941455731250     100         560048477   0
3377941455731250     100         560048475   0
3377941455731250     60          560048475   0
3377941455731250     60          560048475   80
3377941455731250     60          560048475   65
3377941455731250     60          560048477   0
3377941455731250     60          560048475   0(10 行受影响)对Score1 区间有数
Score1有60和100，则分成3个区间：<60、60<= <100、>=100对应每个区间与Score1进行对比，满足上面的区间条件算一个。
最后结果为：3377941455731250  <60        6
3377941455731250  60-100     4
3377941455731250  >=100      0

Answer 1

select examination_id,'<60',sum(case when score1 < 60 then 1 else 0 end) 
from tb 
group by examination_id
union all
select examination_id,'60-100',sum(case when score1 >=60  and score1 < 100 then 1 else 0 end) 
from tb 
group by examination_id
union all
select examination_id,'>=100',sum(case when score1 >=100 then 1 else 0 end) 
from tb 
group by examination_id
--不是横向的吗？

Answer 2

select  examination_id, area, count(1) cnt
from (select examination_id,
(case when score1<60 then '<60' 
 when score1>=60 and score1<100 then '60-100'
 else '>=100' end) area
from tb) t
group by examination_id, area;

Answer 3

SELECT     examination_id, area, COUNT(1) AS cnt
FROM         (SELECT     examination_id, (CASE WHEN score1 < 60 THEN '<60' WHEN score1 >= 60 AND score1 < 100 THEN '60-100' ELSE '>=100' END) AS area
                       FROM          tb) AS t
GROUP BY examination_id, area

Answer 4

大家都理解错了
Score1有60和100，则分成3个区间：<60、60<= <100、>=100
这个区间是变化的。
如果Score1变化了也许是 <20,30-50,50-70,70-100,>=100这个要看Score1列有几个值

Answer 5

Examination_Id Score1 UserId Score2
-------------------- ----------- ----------- -----------
3377941455731250 80 560048475 0
3377941455731250 100 560048475 80
3377941455731250 100 560048475 65
3377941455731250 100 560048477 0
3377941455731250 100 560048475 0
3377941455731250 50 560048475 0
3377941455731250 60 560048475 80
3377941455731250 60 560048475 65
3377941455731250 60 560048477 0
3377941455731250 60 560048475 0
则Score1分区为： <50, 50-60,60-80,80-100,>100

Answer 6

select examination_id,'<60' as title,isnull(count(*),0) as num 
from tb 　where score1 < 60
group by examination_id
union all
select examination_id,'60-100' as title,isnull(count(*),0) as num  
from tb where score1>=60 and score1<100
group by examination_id
union all
select examination_id,'>=100' as title,isnull(count(*),0) as num
from tb where score1 >=100
group by examination_id

Answer 7

if object_id('tb') is not null
 drop table tb;
go
create table tb (examid char(16),score1 int,userid char(9),score2 int);
go
insert into tb
 select '3377941455731250',80,'560048475',0 union all
 select '3377941455731250',100,'560048475',80 union all
 select '3377941455731250',100,'560048475',65 union all
 select '3377941455731250',100,'560048477',0 union all
 select '3377941455731250',100,'560048475',0 union all
 select '3377941455731250',50,'560048475',0 union all
 select '3377941455731250',60,'560048475',80 union all
 select '3377941455731250',60,'560048475',65 union all
 select '3377941455731250',60,'560048477',0 union all
 select '3377941455731250',60,'560048475',0;
godeclare @sql varchar(4000);
set @sql='';;with t as(
select dense_rank() over (order by score1) rn,score1 
from tb group by score1
)
select @sql=@sql+char(10)+'union all'+char(10)+
'select examid,'+char(10)+
quotename(isnull(ltrim(t1.score1),'')+'-'+isnull(ltrim(t2.score1),''),'''')+' area,'+char(10)+
'sum(case when '+isnull('score2>='+ltrim(t1.score1),'1=1')+
isnull(' and score2<'+ltrim(t2.score1),'')+' then 1 else 0 end) cnt'+char(10)+
'from tb group by examid'
from t t1 full join t t2
on t1.rn=t2.rn-1;
 
set @sql=stuff(@sql,1,11,'');
--print @sql;
exec(@sql);
/*
3377941455731250 -50          6
3377941455731250 50-60      0
3377941455731250 60-80      2
3377941455731250 80-100    2
3377941455731250 100-        0
*/

Answer 8

还是自己来吧。
GO
/****** Object:  Table [Exam].[Requirement]    Script Date: 09/15/2010 22:40:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Exam].[Requirement](
[Id] [bigint] NOT NULL,
[Examination_Id] [bigint] NOT NULL,
[Content] [nvarchar](max) COLLATE Chinese_PRC_90_CI_AS NOT NULL,
[Score] [int] NOT NULL,
[CreateDatetime] [datetime] NOT NULL CONSTRAINT [DF_Requirement_CreateDatetime]  DEFAULT (getdate()),
[OrderId] [int] NOT NULL CONSTRAINT [DF_Requirement_OrderId]  DEFAULT ((0))
) ON [PRIMARY]GO
/****** Object:  Table [Exam].[UserExamination]    Script Date: 09/15/2010 22:40:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Exam].[UserExamination](
[Id] [nvarchar](64) COLLATE Chinese_PRC_CI_AS NOT NULL,
[UserId] [int] NOT NULL CONSTRAINT [DF_UserExamination_UserId]  DEFAULT ((0)),
[Examination_Id] [bigint] NOT NULL,
[IsFinished] [bit] NOT NULL CONSTRAINT [DF_UserExamination_IsFinished]  DEFAULT ((0)),
[CreateDatetime] [datetime] NOT NULL CONSTRAINT [DF_UserExamination_CreateDatetime]  DEFAULT (getdate()),
[FinishedDatetime] [datetime] NOT NULL CONSTRAINT [DF_UserExamination_FinishedDatetime]  DEFAULT (getdate()),
[Score] [int] NOT NULL CONSTRAINT [DF_UserExamination_Score]  DEFAULT ((0)),
[RightCount] [int] NOT NULL CONSTRAINT [DF_UserExamination_RightCount]  DEFAULT ((0)),
[WrongCount] [int] NOT NULL CONSTRAINT [DF_UserExamination_WrongCount]  DEFAULT ((0))
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_Requirement_ExaminationId_Score ON Exam.Requirement
(
Examination_Id ASC,
Score ASC
)
CREATE NONCLUSTERED INDEX IX_UserExamination_ExaminationId_Score ON Exam.UserExamination
(
Examination_Id ASC,
Score ASC
)
CREATE PROCEDURE Exam.GetListByScoreRange
(
@ExaminationId bigint
)
AS
BEGIN
DECLARE @Cte table(ExaminationId bigint,ScoreRange nvarchar(16),UserCount int)

DECLARE @MinScore int
DECLARE @MaxScore int SELECT @MinScore=Min(Score)
FROM Exam.Requirement
WHERE Examination_Id=@ExaminationId SELECT @MaxScore=Max(Score)
FROM Exam.Requirement
WHERE Examination_Id=@ExaminationId INSERT INTO @Cte
SELECT @ExaminationId,'<'+cast(@MinScore AS nvarchar(8)),COUNT(1)
FROM Exam.UserExamination
WHERE Examination_Id=@ExaminationId
AND Score<@MinScore; WITH Cte
AS
(
SELECT Examination_Id,a.Score,(SELECT TOP 1 b.Score FROM Exam.Requirement b WHERE Examination_Id=@ExaminationId AND b.Score>a.Score ORDER BY b.Score) as Topscore
FROM Exam.Requirement a 
WHERE Examination_Id=@ExaminationId
) INSERT INTO @Cte
SELECT @ExaminationId,CAST(a.Score AS nvarchar(8))+'-'+CAST(a.Topscore AS nvarchar(8)),
(SELECT COUNT(1) FROM Exam.UserExamination WHERE Examination_Id=@ExaminationId
and Score BETWEEN a.Score AND a.Topscore)
from Cte a
where a.Topscore IS NOT NULL INSERT INTO @Cte
SELECT @ExaminationId,'>'+ CAST(@MaxScore AS nvarchar(8)),COUNT(1)
FROM Exam.UserExamination
WHERE Examination_Id=@ExaminationId
AND Score>=@MaxScore SELECT ExaminationId
          ,ScoreRange
  ,UserCount
    FROM @CteEND