tb表
字段
create table tb
(id int identity(1,1) primary key,
xingming varchar(10),
riqi datetime,
iflag int)
现有记录
insert into tb
select '张三','2011-03-01',1 union all
select '张三','2011-03-02',2 union all
select '张三','2011-03-03',0 union all
select '李四','2011-03-01',1 union all
select '李四','2011-03-01',2 union all
select '王五','2011-03-01',1
条件
1、按名字分类,不管日期如何,只要iflag有一条记录是0的,则不统计
2、如果有多条算一条
分析
按1规则,张三有一条记录是0
按2规则,李四和王五虽然有多条,但是都没有iflag=0的情况,各算1条
结果
2
按照国际管理,感谢高人,热心人,好人,路人~
字段
create table tb
(id int identity(1,1) primary key,
xingming varchar(10),
riqi datetime,
iflag int)
现有记录
insert into tb
select '张三','2011-03-01',1 union all
select '张三','2011-03-02',2 union all
select '张三','2011-03-03',0 union all
select '李四','2011-03-01',1 union all
select '李四','2011-03-01',2 union all
select '王五','2011-03-01',1
条件
1、按名字分类,不管日期如何,只要iflag有一条记录是0的,则不统计
2、如果有多条算一条
分析
按1规则,张三有一条记录是0
按2规则,李四和王五虽然有多条,但是都没有iflag=0的情况,各算1条
结果
2
按照国际管理,感谢高人,热心人,好人,路人~
解决方案 »
- 求表联结sql语句
- (((((((((((((((((((((((((((((((())))))))))))))))))))))))))))))))))))))
- SQL Server 设置了自动收缩,为什么还是会有提示事务日志已满?
- sqserver2005 连接不上了
- ===连接局域网内数据库出现问题===
- ADO问题,怎么得到批SQL命令的每个影响行数,跟查询分析器里的一样!包括查询、删除、修改,,我用的nextRecordset似乎不行
- master自带的3个表被误删了,怎么办?
- 100分求SQL触发器的问题...在线等...急
- 关于卸库:请问有没有办法查看卸库文件expdat.dmp(用什么格式),另,为什么我卸掉某表之后,用sql*plus仍能在库中查到该表及其数据?
- 公司搬地方,好久没上成网。先来问个问题?修改数据库数据的问题:
- sql语句生成日历
- 不用函数,实现字段相同记录合并一条记录问题
(id int identity(1,1) primary key,
xingming varchar(10),
riqi datetime,
iflag int)
insert into tb
select '张三','2011-03-01',1 union all
select '张三','2011-03-02',2 union all
select '张三','2011-03-03',0 union all
select '李四','2011-03-01',1 union all
select '李四','2011-03-01',2 union all
select '王五','2011-03-01',1select xingming,COUNT(1) from tb a
where not exists(select 1 from tb where xingming=a.xingming and iflag=0)
group by xingming
/*
xingming
---------- -----------
李四 2
王五 1
而不是列出具体的明细select count(*)
from
(
select count(*) as b
from tb a
where not exists
(
select *
from tb
where a.xingming = xingming and iflag=0
)
group by xingming) b1
刚才自己写了一下。不过感觉有点麻烦,期待高人能给出更简洁的
(id int identity(1,1) primary key,
xingming varchar(10),
riqi datetime,
iflag int)
insert into tb
select '张三','2011-03-01',1 union all
select '张三','2011-03-02',2 union all
select '张三','2011-03-03',0 union all
select '李四','2011-03-01',1 union all
select '李四','2011-03-01',2 union all
select '王五','2011-03-01',1select COUNT(distinct xingming) from tb a
where not exists(select 1 from tb where xingming=a.xingming and iflag=0)/*-----------
2
create table tb
(id int identity(1,1) primary key,
xingming varchar(10),
riqi datetime,
iflag int)
insert into tb
select '张三','2011-03-01',1 union all
select '张三','2011-03-02',2 union all
select '张三','2011-03-03',0 union all
select '李四','2011-03-01',1 union all
select '李四','2011-03-01',2 union all
select '王五','2011-03-01',1select count(distinct xingming)
from tb a
where xingming not in (select xingming from tb where iflag=0)drop table tb/*2
create table tb
(id int identity(1,1) primary key,
xingming varchar(10),
riqi datetime,
iflag int)insert into tb
select '张三','2011-03-01',1 union all
select '张三','2011-03-02',2 union all
select '张三','2011-03-03',0 union all
select '李四','2011-03-01',1 union all
select '李四','2011-03-01',2 union all
select '王五','2011-03-01',1SELECT COUNT(DISTINCT xingming ) FROM TB
WHERE xingming NOT IN (SELECT xingming FROM TB WHERE iflag=0)/*
-----------
2(1 row(s) affected)
*/