有张考试人员表A id name state city
1 刘虹 1 济南市
2 刘风 0 青岛市
3 张力 0 济南市
4 谷米 1 烟台市
5 张华 1 临沂市name是考生的名字
state是考生的成绩审核状态,0为未审核,1为已审核
city是考生所在地区
我要查询的结果是表中,每个城市,考试成绩未审核的人数、已审核的人数,以及考试的总人数根据上面所给表,可知结果: 城市 考试成绩已审核人数 考试成绩未审核人数 参加考试的总人数
济南市 1 1 2
青岛市 0 1 1
烟台市 1 0 1
临沂市 1 0 1请大家帮我写出查询语句!
多谢,急用!
1 刘虹 1 济南市
2 刘风 0 青岛市
3 张力 0 济南市
4 谷米 1 烟台市
5 张华 1 临沂市name是考生的名字
state是考生的成绩审核状态,0为未审核,1为已审核
city是考生所在地区
我要查询的结果是表中,每个城市,考试成绩未审核的人数、已审核的人数,以及考试的总人数根据上面所给表,可知结果: 城市 考试成绩已审核人数 考试成绩未审核人数 参加考试的总人数
济南市 1 1 2
青岛市 0 1 1
烟台市 1 0 1
临沂市 1 0 1请大家帮我写出查询语句!
多谢,急用!
sum(case when state=0 then 1 else 0 end) as 未审核,
count(*) 参考总人数
from tb
group by 城市
考试成绩已审核人数=sum(case when state=1 then 1 else 0 end ),
考试成绩未审核人数=sum(case when state=1 then 1 else 0 end ),
参加考试的总人数=count(1)
from 表 group by city
sum(case state when 1 then 1 else 0 end) 考试成绩已审核人数 ,
sum(case state when 0 then 1 else 0 end) 考试成绩未审核人数 ,
count(1) 参加考试的总人数
from a
group by city
-- AUTHOR: T.O.P
-- CREATE DATE: 20091126
-- VERSION: SQL SERVER 2000
-- =============================================
DECLARE @TB TABLE([ID] INT,[NAME] VARCHAR(4),[STATE] INT,[CITY] VARCHAR(6))
INSERT @TB
SELECT 1,'刘虹',1,'济南市' UNION ALL
SELECT 2,'刘风',0,'青岛市' UNION ALL
SELECT 3,'张力',0,'济南市' UNION ALL
SELECT 4,'谷米',1,'烟台市' UNION ALL
SELECT 5,'张华',1,'临沂市'SELECT 城市=CITY,
考试成绩已审核人数=SUM(CASE WHEN STATE=1 THEN 1 ELSE 0 END ),
考试成绩未审核人数=SUM(CASE WHEN STATE=1 THEN 1 ELSE 0 END ),
参加考试的总人数=COUNT(1)
FROM @TB
GROUP BY CITY
--测试结果:
/*
城市 考试成绩已审核人数 考试成绩未审核人数 参加考试的总人数
------ ----------- ----------- -----------
济南市 1 1 2
临沂市 1 1 1
青岛市 0 0 1
烟台市 1 1 1(所影响的行数为 4 行)*/
这个应该对的-- =============================================
-- AUTHOR: T.O.P
-- CREATE DATE: 20091126
-- VERSION: SQL SERVER 2000
-- =============================================
DECLARE @TB TABLE([ID] INT,[NAME] VARCHAR(4),[STATE] INT,[CITY] VARCHAR(6))
INSERT @TB
SELECT 1,'刘虹',1,'济南市' UNION ALL
SELECT 2,'刘风',0,'青岛市' UNION ALL
SELECT 3,'张力',0,'济南市' UNION ALL
SELECT 4,'谷米',1,'烟台市' UNION ALL
SELECT 5,'张华',1,'临沂市'SELECT 城市=CITY,
考试成绩已审核人数=SUM(CASE WHEN STATE=1 THEN 1 ELSE 0 END ),
考试成绩未审核人数=SUM(CASE WHEN STATE=0 THEN 1 ELSE 0 END ),
参加考试的总人数=COUNT(1)
FROM @TB
GROUP BY CITY
--测试结果:
/*
城市 考试成绩已审核人数 考试成绩未审核人数 参加考试的总人数
------ ----------- ----------- -----------
济南市 1 1 2
临沂市 1 0 1
青岛市 0 1 1
烟台市 1 0 1(所影响的行数为 4 行)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-26 20:14:22
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(4),[state] int,[city] varchar(6))
insert [tb]
select 1,'刘虹',1,'济南市' union all
select 2,'刘风',0,'青岛市' union all
select 3,'张力',0,'济南市' union all
select 4,'谷米',1,'烟台市' union all
select 5,'张华',1,'临沂市'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select city as 城市 '
select @sql = @sql + ' , sum(case state when ''' + ltrim(state) + ''' then 1 else 0 end) [' + ltrim(state) + ']'
from (select distinct state from tb) as a
set @sql = @sql + ' ,count(1) as 总人数 from tb group by city'
exec(@sql)
----------------结果----------------------------
/* 城市 0 1 总人数
------ ----------- ----------- -----------
济南市 1 1 2
临沂市 0 1 1
青岛市 1 0 1
烟台市 0 1 1(4 行受影响)*/
表B id name state
1 刘虹 1
2 刘风 0
3 张力 0
4 谷米 1
5 张华 1
表C name city
刘虹 济南市
刘风 青岛市
张力 济南市
谷米 烟台市
张华 临沂市 假设两个表的name属性里的值是唯一的,无重复值。
那么用name属性做连接,即:B.name=C.name
那么,要达到原先的查询结果,SQL语句怎么写?
考试成绩已审核人数=SUM(CASE WHEN STATE=1 THEN 1 ELSE 0 END ),
考试成绩未审核人数=SUM(CASE WHEN STATE=1 THEN 1 ELSE 0 END ),
参加考试的总人数=COUNT(*)
FROM @TB
GROUP BY CITY