USE pubs
SELECT au_id
FROM titleauthor下面是结果集:au_id
-----------
172-32-1176
213-46-8915
213-46-8915
238-95-7766
267-41-2394
267-41-2394
274-80-9391
409-56-7008
427-17-2319
472-27-2349
486-29-1786
486-29-1786
648-92-1872
672-71-3249
712-45-1867
722-51-5454
724-80-9391
724-80-9391
756-30-7391
807-91-6654
846-92-7186
899-46-2035
899-46-2035
998-72-3567
998-72-3567 (25 row(s) affected)而使用了 DISTINCT 后,就能够除去重复项,而只查看唯一的作者 ID:USE pubs
SELECT DISTINCT au_id
FROM titleauthor下面是结果集:au_id
-----------
172-32-1176
213-46-8915
238-95-7766
267-41-2394
274-80-9391
409-56-7008
427-17-2319
472-27-2349
486-29-1786
648-92-1872
672-71-3249
712-45-1867
722-51-5454
724-80-9391
756-30-7391
807-91-6654
846-92-7186
899-46-2035
998-72-3567 (19 row(s) affected)
SELECT au_id
FROM titleauthor下面是结果集:au_id
-----------
172-32-1176
213-46-8915
213-46-8915
238-95-7766
267-41-2394
267-41-2394
274-80-9391
409-56-7008
427-17-2319
472-27-2349
486-29-1786
486-29-1786
648-92-1872
672-71-3249
712-45-1867
722-51-5454
724-80-9391
724-80-9391
756-30-7391
807-91-6654
846-92-7186
899-46-2035
899-46-2035
998-72-3567
998-72-3567 (25 row(s) affected)而使用了 DISTINCT 后,就能够除去重复项,而只查看唯一的作者 ID:USE pubs
SELECT DISTINCT au_id
FROM titleauthor下面是结果集:au_id
-----------
172-32-1176
213-46-8915
238-95-7766
267-41-2394
274-80-9391
409-56-7008
427-17-2319
472-27-2349
486-29-1786
648-92-1872
672-71-3249
712-45-1867
722-51-5454
724-80-9391
756-30-7391
807-91-6654
846-92-7186
899-46-2035
998-72-3567 (19 row(s) affected)
表b的记录为
mobile id optype
13340800295 2222 1
13018188953 2204 1
13018189132 2204 2
13340808888 2204 1
13340808888 2204 1
13340808888 2204 1
13340808888 2204 1
select count(*) as Num ,sum(case when inuse='1' and usertype ='1' and opid= '2204' then 1 else 0 end) as inNum,(count(*) - sum(case when inuse='1' and usertype ='1' and opid= '2204' then 1 else 0 end)) as LogOutNum, '"+ opid +"' as opid from (select distinct * from a, b where mobile=phone and id = '2204') m
Create table A
(phone varchar(15),
opid varchar(5),
inuse Int,
usertype Int
)
Create table B
(mobile varchar(15),
id varchar(5),
optype Int
)
GO
--插入数据
Insert A Values('13036404040', '1004', 1, 2)
Insert A Values('13018188953', '2203', 1, 1)
Insert A Values('13018189132', '2204', 2, 1)
Insert A Values('13309000439', '2214', 1, 1)
Insert A Values('13118139713', '2203', 1, 1)
Insert A Values('13340808888', '2204', 1, 1)Insert B Values('13340800295', '2222', 1)
Insert B Values('13018188953', '2204', 1)
Insert B Values('13018189132', '2204', 2)
Insert B Values('13340808888', '2204', 1)
GO
--测试
select count(*) as Num ,sum(case when inuse='1' and usertype ='1' and opid= '2204' then 1 else 0 end) as inNum,(count(*) - sum(case when inuse='1' and usertype ='1' and opid= '2204' then 1 else 0 end)) as LogOutNum from a, b where mobile=phone and id = '2204' And inuse =1 And usertype=1 And optype=1
--删除测试环境
Drop table A,B
--结果
/*
Num inNum LogOutNum
2 1 1 */
select count(*) as Num ,
sum(case when inuse='1' and usertype ='1' and opid= '2204' then 1 else 0 end) as inNum,
(count(*) - sum(case when inuse='1' and usertype ='1' and opid= '2204' then 1 else 0 end)) as LogOutNum,
'"+ opid +"' as opid from (select distinct * from a, b where mobile=phone and id = '2204' and inuse = '1' and usertype = '1' and optype = '1') m