SELECT a.YXSM AS '院系所码', b.YXMC AS '院系名称', count(*) AS '报名人数' FROM KSXX a, ZSZY b WHERE a.KSFSM='11' and a.YXSM = b.YXSM GROUP BY a.YXSM,b.YXMC
查询语句使用了聚合函数。其他属性列,用group by group by YXSM, YXMC
SELECT DISTINCT a.YXSM AS '院系所码',b.YXMC as '院系名称', count(a.yxsm) AS '报名人数' FROM KSXX a,ZSZY b WHERE a.YXSM=b.YXSM and a.KSFSM='11' GROUP BY a.YXSM,b.YXMC
SELECT DISTINCT a.YXSM AS '院系所码', count(a.yxsm) AS '报名人数',b.YXMC as '院系名称' FROM KSXX a on a.KSFSM='11' join ZSZY b on a.YXSM=b.YXSM GROUP BY YXSM
SELECT DISTINCT a.YXSM AS '院系所码', count(a.yxsm) AS '报名人数',b.YXMC as '院系名称' FROM KSXX a on a.KSFSM='11' join ZSZY b on a.YXSM=b.YXSM GROUP BY a.YXSM
group by 的字段必需要出现在Select 句语后面,当然作一些变换也可以。
SELECT A.YXSM AS '院系所码', A.BMRS AS '报名人数',B.YXMC as '院系名称' FROM (SELECT YXSM,count(a.yxsm) AS BMRS FROM KSXX where KSFSM='11' GROUP BY YXSM ) A left join ZSZY B on A.YXSM=A.YXSM
不好意思,写错个地方 SELECT A.YXSM AS '院系所码', A.BMRS AS '报名人数',B.YXMC as '院系名称' FROM (SELECT YXSM,count(a.yxsm) AS BMRS FROM KSXX where KSFSM='11' GROUP BY YXSM ) A left join ZSZY B on A.YXSM=B.YXSM
select distinct ksxx.YXSM AS '院系所码',yxmc AS '院系名称',count(yxsm) AS '报名人数' from ksxx inner join zszy on ksxx.yxsm = zszy.yxsm where ksfsm = '11' group by ksxx.yxsm,zszy.yxmc注意一下几点: 1、别名.字段 2、inner join
--改写一下 select distinct ksxx.YXSM AS '院系所码',yxmc AS '院系名称',count(ksxx.yxsm) AS '报名人数' from ksxx inner join zszy on ksxx.yxsm = zszy.yxsm where ksfsm = '11' group by ksxx.yxsm,zszy.yxmc
SELECT DISTINCT a.YXSM AS '院系所码', count(a.yxsm) AS '报名人数',b.YXMC as '院系名称' FROM KSXX a on a.KSFSM='11' join ZSZY b on a.YXSM=b.YXSM GROUP BY a.YXSM
SELECT DISTINCT a.YXSM AS 院系所码, count(a.yxsm) AS 报名人数 FROM (select yxsm from KSXX WHERE KSFSM='11'GROUP BY YXSM)a join zszy b on a.yxsm=b.yxsm
--这个对: declare @ksxx table(yxsm int,ksfsm varchar(20)) insert into @ksxx select 1,'11'union all select 1,'11'union all select 1,'13'union all select 2,'12'union all select 2,'11'union all select 2,'12'union all select 3,'11'union all select 3,'11'union all select 3,'11'declare @zszy table(yxsm int,yxmc varchar(20)) insert into @zszy select 1,'院系1'union all select 2,'院系2'union all select 3,'院系3'SELECT a.YXSM AS 院系所码,b.yxmc as 院系名称, count(a.yxsm) AS 报名人数 FROM (select yxsm from @KSXX WHERE KSFSM='11')a left join @zszy b on a.yxsm=b.yxsm group by a.yxsm,b.yxmc /* 院系所码 院系名称 报名人数 ----------- -------------------- ----------- 1 院系1 2 2 院系2 1 3 院系3 3(所影响的行数为 3 行) */
group by YXSM, YXMC
SELECT DISTINCT a.YXSM AS '院系所码',b.YXMC as '院系名称', count(a.yxsm) AS '报名人数' FROM KSXX a,ZSZY b WHERE a.YXSM=b.YXSM and a.KSFSM='11' GROUP BY a.YXSM,b.YXMC
GROUP BY YXSM
GROUP BY a.YXSM
FROM
(SELECT YXSM,count(a.yxsm) AS BMRS
FROM KSXX
where KSFSM='11'
GROUP BY YXSM ) A
left join ZSZY B
on A.YXSM=A.YXSM
SELECT A.YXSM AS '院系所码', A.BMRS AS '报名人数',B.YXMC as '院系名称'
FROM
(SELECT YXSM,count(a.yxsm) AS BMRS
FROM KSXX
where KSFSM='11'
GROUP BY YXSM ) A
left join ZSZY B
on A.YXSM=B.YXSM
from ksxx inner join zszy on ksxx.yxsm = zszy.yxsm
where ksfsm = '11'
group by ksxx.yxsm,zszy.yxmc注意一下几点:
1、别名.字段
2、inner join
select distinct ksxx.YXSM AS '院系所码',yxmc AS '院系名称',count(ksxx.yxsm) AS '报名人数'
from ksxx inner join zszy on ksxx.yxsm = zszy.yxsm
where ksfsm = '11'
group by ksxx.yxsm,zszy.yxmc
GROUP BY a.YXSM
ZSZY(YXSM, YXMC.....)后面还有字段
YXSM YXMC
001 外语学院
001 外语学院
001 外语学院
001 外语学院
001 外语学院
002 物理学院
002 物理学院
002 物理学院
.
.
.
.我只想根据001来查询他的名称,真不好意思啊~~~
declare @ksxx table(yxsm int,ksfsm varchar(20))
insert into @ksxx
select 1,'11'union all
select 1,'11'union all
select 1,'13'union all
select 2,'12'union all
select 2,'11'union all
select 2,'12'union all
select 3,'11'union all
select 3,'11'union all
select 3,'11'declare @zszy table(yxsm int,yxmc varchar(20))
insert into @zszy
select 1,'院系1'union all
select 2,'院系2'union all
select 3,'院系3'SELECT a.YXSM AS 院系所码,b.yxmc as 院系名称, count(a.yxsm) AS 报名人数 FROM (select yxsm from @KSXX WHERE KSFSM='11')a left join @zszy b on a.yxsm=b.yxsm group by a.yxsm,b.yxmc
/*
院系所码 院系名称 报名人数
----------- -------------------- -----------
1 院系1 2
2 院系2 1
3 院系3 3(所影响的行数为 3 行)
*/