# 关于SQL中一个group by语句怎么写

0

问题是这样的，假如公司有10000人，公司的员工的薪水从500到10000不等，现在要以500为一个梯度，统计每个阶段的员工的人数

工资水平                  人数

500~999                        10
1000~1499                    324

1500~2000                    459

.....                                ...

9500~10000                    33

我是初学者，希望能够讲的详细点。谢谢！

0

`建表，插入相应测试数据USE D_SalaryGOCREATE TABLE T_EmpSalary(    AutoID        int IDENTITY(1,1) PRIMARY KEY,    EmpName        nvarchar(20) NOT NULL,    Salary        int             NOT NULL)GOUSE D_SalaryGODECLARE @Index         int,        @EmpName    nvarchar(20),        @Salary        intSET @Index = 1;SET @EmpName = 'rainnoless';SET @Salary = 500;WHILE @Index < 10001BEGIN    INSERT INTO [D_Salary].[dbo].[T_EmpSalary]        (EmpName, Salary)    VALUES        (@EmpName, @Salary)    IF @@ERROR = 0        BEGIN            IF @Index BETWEEN 1 AND 9000                SET @Salary = 500 + @Index            ELSE                SET @Salary = @Index                    SET @Index = @Index + 1;            SET @EmpName = 'rainnoless' + CAST(@Index AS VARCHAR(5));            CONTINUE        END    ELSE        BREAKEND`

`SELECT ( CAST(MIN(minSalary) AS VARCHAR(5)) + '~' + CAST(MAX(maxSalary) AS VARCHAR(5)) ) AS SalaryBound, COUNT(Totals) AS TotalsFROM (SELECT MAX(Salary/500) AS Totals, MAX(Salary) AS maxSalary, MIN(Salary) AS minSalary        FROM [D_Salary].[dbo].[T_EmpSalary]            GROUP BY AutoID) AS SalaryTotalsGROUP BY TotalsORDER BY MIN(minSalary)`

`CREATE VIEW V_SalaryTotalsAS    SELECT MAX(Salary/500) AS Totals, MAX(Salary) AS maxSalary, MIN(Salary) AS minSalary    FROM [D_Salary].[dbo].[T_EmpSalary]    GROUP BY AutoIDGO`

`SELECT ( CAST(MIN(minSalary) AS VARCHAR(5)) + '~' + CAST(MAX(maxSalary) AS VARCHAR(5)) ) AS SalaryBound, COUNT(Totals) AS TotalsFROM [D_Salary].[dbo].[V_SalaryTotals]GROUP BY TotalsORDER BY MIN(minSalary)`

rainnoless | 小虾三级 |园豆：613 | 2009-11-08 01:42

rainnoless | 园豆：613 (小虾三级) | 2009-11-08 11:33

0

select count(1),(a*500)||'-'||( (a+1)*500-1) step from(
select id,salary, trunc(SALARY/500)  a From woody

) group by a

woody.wu | 园豆：3621 (老鸟四级) | 2009-11-07 21:30

0

0

declare @i int,@sql varchar(500)
select @i=0,@sql='select ''0~500'' as 工资水平,count(*) as 人数 from 员工薪水表 where 薪水 between 0 and 500'
set @i=500
while @i<10000
begin
set @sql=@sql+' Union select '''+CAST(@i as varchar)+'~'+CAST(@i+500 as varchar)+''', count(*) from 员工薪水表 where 薪水 between '+cast(@i as varchar)+' and '+cast(@i+500 as varchar)
set @i=@i+500
end

exec(@sql)

dege301 | 园豆：2825 (老鸟四级) | 2009-11-08 01:50

您需要登录以后才能回答，未注册用户请先注册