请教个查询语句

[nq19860409]

进出记录表
人员 信息 局站 门禁 时间
潘莲心 出门 临沂 临沂一楼 2007-10-10 16:36:39
杨恋香 进门 龚路 龚路1楼 2007-10-10 16:36:59
顾培忠 出门 北蔡 北蔡三楼 2007-10-10 16:37:08
杨恋香 出门 龚路 龚路1楼 2007-10-10 16:37:41
鲍国强 进门 北蔡 北蔡三楼 2007-10-10 16:41:14
薛晟 进门 沈家宅 沈家宅一楼 2007-10-10 16:38:20
顾培忠 进门 北蔡 北蔡三楼 2007-10-10 16:38:15


现在要查询出这样的结果

局站 进门次数 出门次数
北蔡 2 1

请问这样的 sql语句怎么写

[天-笑]

啥意思？

[nq19860409]

就是统计进出门次数呀

[zyfcy]

select loca,type,count(*) count
from (
select '潘莲心' name,'出门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
union all
select '潘莲心' name,'出门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
union all
select '潘莲心' name,'进门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
union all
select '潘莲心' name,'出门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
) as a
group by loca,type

[nq19860409]

楼上的解释下吧，我查询的时候可以根据端局查询或者时间

[Cerse]

略懂略懂

[zyfcy]

select loca,
	   sum(case type when '出门' then 1 else 0 end ) countout,
       sum(case type when '进门' then 1 else 0 end ) countin
from (
select '潘莲心' name,'出门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
union all
select '潘莲心' name,'出门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
union all
select '潘莲心' name,'进门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
union all
select '潘莲心' name,'出门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
) as a
group by loca

[nq19860409]

我的意思假设我要查询北蔡局站的进出记录次数，只要得到局站，进门次数，出门次数3个字段

[zyfcy]

第二个SQL应该是正确的,分别统计下进门和出门的次数.

[nq19860409]

6楼的你后面那四个
select '潘莲心' name,'出门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
union all
select '潘莲心' name,'出门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
union all
select '潘莲心' name,'进门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
union all
select '潘莲心' name,'出门' type,'临沂' loca,'临沂一楼' locade,'2007-10-10 16:36:39' time
这是干嘛的呀

[nq19860409]

懂了，多谢了

[shixixi1987]

if exists(select * from sysobjects where [name]='tb')
drop table tb
go
create table tb
(
ry varchar(50),
xx varchar(50),
jz varchar(50),
mj varchar(50),
[time] datetime
)
go
insert into tb(ry,xx,jz,mj,[time])
(
select '潘莲心','出门','临沂','临沂一楼','2007-10-10 16:36:39' union
select '杨恋香','进门','龚路','龚路1楼','2007-10-10 16:36:59' union
select '顾培忠','出门','北蔡','北蔡三楼','2007-10-10 16:37:08' union
select '杨恋香','出门','龚路','龚路1楼','2007-10-10 16:37:41' union
select '鲍国强','进门','北蔡','北蔡三楼','2007-10-10 16:41:14' union
select '薛晟','进门','沈家宅','沈家宅一楼','2007-10-10 16:38:20' union
select '顾培忠','进门','北蔡','北蔡三楼','2007-10-10 16:38:15'
)
go
select jz,进门次数=sum(case when xx='进门' then 1 else 0 end),
出门次数=sum(case when xx='出门' then 1 else 0 end)
from tb group by jz
go 

jz                                                 进门次数        出门次数
-------------------------------------------------- ----------- -----------
北蔡                                                 2           1
龚路                                                 1           1
临沂                                                 0           1
沈家宅                                                1           0

[zhengduan964532]

IF object_id('tb')IS NOT NULL DROP TABLE TB
CREATE TABLE tb(ry VARCHAR(10),INF VARCHAR(10),jz VARCHAR(10),mj VARCHAR(10),TIME VARCHAR(100))
INSERT INTO tb
SELECT  '潘莲心' ,'出门' ,'临沂' ,'临沂一楼' ,'2007-10-10 16:36:39' UNION ALL  
SELECT  '杨恋香' ,'进门', '龚路', '龚路1楼', '2007-10-10 16:36:59' UNION ALL  
SELECT  '顾培忠' ,'出门' ,'北蔡' ,'北蔡三楼' ,'2007-10-10 16:37:08' UNION ALL  
SELECT  '杨恋香', '出门' ,'龚路', '龚路1楼' ,'2007-10-10 16:37:41' UNION ALL  
SELECT  '鲍国强' ,'进门' ,'北蔡' ,'北蔡三楼', '2007-10-10 16:41:14' UNION ALL  
SELECT  '薛晟', '进门', '沈家宅' ,'沈家宅一楼' ,'2007-10-10 16:38:20' UNION ALL  
SELECT  '顾培忠', '进门', '北蔡', '北蔡三楼' ,'2007-10-10 16:38:15'

SELECT * FROM tb

SELECT jz,
       jm=sum(CASE WHEN inf='出门' THEN 1 ELSE 0 END ),
       cm=sum(CASE WHEN inf='进门'  THEN 1 ELSE 0 end)
FROM tb
GROUP BY jz 

[ksaponka_tang]

use OA_DB
go

if object_id('tbk') is not null
drop table tbk
go
create table tbk( empld nvarchar(80), info nvarchar(60), Stat nvarchar(60), MJ nvarchar(80),DT Datetime)
insert tbk
select '潘莲心', '出门', '临沂', '临沂一楼', '2007-10-10 16:36:39' union all
select '杨恋香', '进门', '龚路', '龚路1楼', '2007-10-10 16:36:59' union all
select '顾培忠', '出门', '北蔡', '北蔡三楼', '2007-10-10 16:37:08' union all
select '杨恋香', '出门', '龚路', '龚路1楼', '2007-10-10 16:37:41' union all
select '鲍国强', '进门', '北蔡', '北蔡三楼', '2007-10-10 16:41:14' union all
select '薛晟', '进门', '沈家宅', '沈家宅一楼', '2007-10-10 16:38:20' union all
select '顾培忠', '进门', '北蔡', '北蔡三楼', '2007-10-10 16:38:15'


select Stat, sum(case info when '出门' then 1 else 0 end) 出门次數 ,sum(case info when '进门' then 1 else 0 end) 进门次數
from TBk
group by Stat

-------------------------------------
北蔡 1 2
龚路 1 1
临沂 1 0
沈家宅 0 1