想到个sql。。不知道咋写。。。。

[a2bb]

riqi area info
2007-10-6 南京 房价
2007-10-6 上海 猪肉
2007-10-8 西安 工资
2007-10-8 南京 交通
2007-10-8 上海 美女
.
.
.
.
.
我如果要查找所有时间下共有的area。。这个SQL咋写啊。。假设时间是<2007-10-31

[chuifengde]

select riqi,area from [Table] group by riqi,area having count(1)>1

[dawugui]

共有是啥意思?总数,每天的数量?
select count(*) from tb where riqi < '2007-10-31'
select riqi , count(*) from tb where riqi < '2007-10-31' group by riqi 

[a2bb]

2楼的测了下是没记录>.<
回三楼的。。就是共有的城市。。
那两条记录的话出来应该是
rq
南京
上海

就是这个效果啦

[a2bb]

错了。。应该是
area
南京
上海

[yxh198358]

select area,count(*) qty from tablename group by area where riqi < '2007-10-31'
或者
select distinct area from tablename where riqi < '2007-10-31'

[chuifengde]

select distinct area from [Table] a where exists(select 1 from @a where area=a.area and riqi<>a.riqi) and riqi<'2007-10-31'

[chuifengde]

--上面的数据特殊，改一下
declare @a table(riqi smalldatetime,         area  varchar(10),      info varchar(10))
insert @a select '2007-10-6',     '南京',          '房价' 
union all select '2007-10-6'     ,'上海',          '猪肉' 
union all select '2007-10-8'     ,'西安',          '工资' 
union all select '2007-10-8'     ,'南京',          '交通' 
union all select '2007-10-8'     ,'上海',          '美女' 
union all select '2007-10-9'     ,'上海',          '美女'

select area from @a a where riqi<'2007-10-31' group by area having(count(1)=(select distinct  count(distinct riqi) from @a where riqi<'2007-10-31'))
--result
/*
area       
---------- 
上海

（所影响的行数为 1 行）
*/

[sunhonglei2004]

create table ta
(area char(10),
zongshu int)
insert ta select area,count(area) as zongshu from tb group by area
select area from ta where zongshu>1 group by area

[a2bb]

谢谢吹风的。。。lol~~sf~~

[mysterious8]

对于7楼的说法 存在异议
我如果要查找所有时间下共有的area。 (楼主说的)
其实可以用(∀x)P=˜(∃x(˜P))公式转换成用not exists子句的查询
我的代码（借用7楼源码，做个修改,添加了最后一条记录）

declare @a table(riqi smalldatetime,         area  varchar(10),      info varchar(10))
insert @a select '2007-10-6',     '南京',          '房价' 
union all select '2007-10-6'     ,'上海',          '猪肉' 
union all select '2007-10-8'     ,'西安',          '工资' 
union all select '2007-10-8'     ,'南京',          '交通' 
union all select '2007-10-8'     ,'上海',          '美女' 
union all select '2007-10-9'     ,'上海',          '美女'
--下面是添加的记录
union all select '2007-10-9'	 ,'上海',	   '房价'

--select area from @a a where riqi<'2007-10-31' group by area having(count(1)=(select distinct  count(distinct riqi) from @a where riqi<'2007-10-31'))
--我用的查询语句：
select distinct area from @a a1 where not exists(select * from @a a2 where not exists(select * from @a a3 where a3.area=a1.area and a3.riqi=a2.riqi))