一张表,名称为aa
id rq
1 2008-01-01
2 2008-01-01
3 2008-01-01
4 2008-01-01
1 2008-12-01
2 2008-12-01
5 2008-12-01
我想查询的结果为
id rq
3 2008-01-01
4 2008-01-01
5 2008-12-01
SQL语句该如何写?
id rq
1 2008-01-01
2 2008-01-01
3 2008-01-01
4 2008-01-01
1 2008-12-01
2 2008-12-01
5 2008-12-01
我想查询的结果为
id rq
3 2008-01-01
4 2008-01-01
5 2008-12-01
SQL语句该如何写?
select id,rq,count(id) from aa where count(id)<2 group by id,rq
一张表,名称为aa
id rq
a 2008-01-01
b 2008-01-01
c 2008-01-01
d 2008-01-01
a 2008-12-01
b 2008-12-01
f 2008-12-01
我想查询的结果为
id rq
c 2008-01-01
d 2008-01-01
f 2008-12-01
SQL语句该如何写?
select id ,rq from aa where id not in
(select id from aa group by id having count(id)>1)
id rq
3 2008-01-01 00:00:00
4 2008-01-01 00:00:00
5 2008-12-01 00:00:00
select * from aa a where not exists(
select * from aa where a.id=id and a.rq<>rq
)
(uid int,
rq smalldatetime)
表aa
id name date1
101 张三 2008-12-01
102 李司 2008-12-01
103 李子 2008-12-01
表bb
id name date1
101 张三 2008-01-01
102 李司 2008-01-01
105 王二 2008-01-01
结果
id1 name date1
103 李子 2008-12-01
105 王二 2008-01-01
sql语句如何写?
TRY:
SELECT * FROM (
select id,name,date1 from aa where id=(select max(id) from aa)
union all
select id,name,date1 from bb where id=(select max(id) from bb)
)AA
DECLARE @AA TABLE(ID VARCHAR(10),NAME VARCHAR(20),DATE1 DATETIME)
INSERT INTO @AA
SELECT '101','张三','2008-12-01' UNION ALL
SELECT '102','李司','2008-12-01' UNION ALL
SELECT '103','李子','2008-12-01' DECLARE @BB TABLE(ID VARCHAR(10),NAME VARCHAR(20),DATE1 DATETIME)
INSERT INTO @BB
SELECT '101','张三','2008-01-01' UNION ALL
SELECT '102','李司','2008-01-01' UNION ALL
SELECT '105','王二','2008-01-01' SELECT * FROM (
select id,[name],date1 from @aa where id=(select max(id) from @aa)
union all
select id,[name],date1 from @bb where id=(select max(id) from @bb)
)AA----------result:
/*
103 李子 2008-12-01 00:00:00.000
105 王二 2008-01-01 00:00:00.000
*/
表aa
id name date1
101 张三 2008-12-01
102 李司 2008-12-01
103 李子 2008-12-01
表bb
id name date1
101 张三 2008-01-01
102 李司 2008-01-01
105 王二 2008-01-01
结果
id1 name date1
103 李子 2008-12-01
105 王二 2008-01-01
sql语句如何写?
union all
SELECT * FROM bb a where aa.name not exists(select * from aa b where a.name=b.name)
except
select * from bb
create table #A(ID VARCHAR(10),NAME VARCHAR(20),DATE1 DATETIME)
INSERT INTO #A
SELECT '101','张三','2008-12-01' UNION ALL
SELECT '102','李司','2008-12-01' UNION ALL
SELECT '103','李子','2008-12-01' create table #b(ID VARCHAR(10),NAME VARCHAR(20),DATE1 DATETIME)
INSERT INTO #b
SELECT '101','张三','2008-01-01' UNION ALL
SELECT '102','李司','2008-01-01' UNION ALL
SELECT '105','王二','2008-01-01' select id,[name],date1 from #A where id not in (select id from #b)
union all
select id,[name],date1 from #b where id not in(select id from #A)
drop table #a
drop table #b-----结果-------
103 李子 2008-12-01 00:00:00.000
105 王二 2008-01-01 00:00:00.000
INSERT INTO @AA
SELECT '101','张三','2008-12-01' UNION ALL
SELECT '102','李司','2008-12-01' UNION ALL
SELECT '103','李子','2008-12-01' DECLARE @BB TABLE(ID VARCHAR(10),NAME VARCHAR(20),DATE1 DATETIME)
INSERT INTO @BB
SELECT '101','张三','2008-01-01' UNION ALL
SELECT '102','李司','2008-01-01' UNION ALL
SELECT '105','王二','2008-01-01' SELECT * FROM (
select id,[name],date1 from @aa
union
select id,[name],date1 from @bb )b where not exists(select 1 from @aa where [name]=b.[name])
or not exists(select 1 from @bb where [name]=b.[name])
2 2008-01-01
3 2008-01-01
4 2008-01-01
1 2008-12-01
2 2008-12-01
5 2008-12-01
select * from table group by id having count(*)<2