sql
1分组求最大连接主表查询
表A:
a b c
1 xx xxxx
2 yy yyyy
3 zz zzzz
表B:
d e f
1 2004-7-1 13:09:08 alddd
1 2004-7-1 14:22:28 sssss
1 2004-7-1 15:25:22 ddddd
2 2005-8-1 4:12:16 adjskl
2 2005-8-2 3:02:06 ajdksj
3 2005-8-2 21:35:56 sjdskd
表 A和表B是1对多的主从表 A.a = B.d
现在想选出这样的记录
a b c e f
1 xx xxxx 2004-7-1 15:25:22 alddd
2 yy yyyy 2005-8-2 3:02:06 ajdksj
3 zz zzzz 2005-8-2 21:35:56 sjdskd
就是根据选出主表中对应从表中e最大的记录 请问sql语句怎么写
--生成测试数据
create table 表A(a int,b varchar(10),c varchar(10))
insert into 表A select 1,'xx','xxxx'
insert into 表A select 2,'yy','yyyy'
insert into 表A select 3,'zz','zzzz'
create table 表B(d int,e datetime,f varchar(10))
insert into 表B select 1,'2004-7-1 13:09:08','alddd'
insert into 表B select 1,'2004-7-1 14:22:28','sssss'
insert into 表B select 1,'2004-7-1 15:25:22','ddddd'
insert into 表B select 2,'2005-8-1 04:12:16','adjskl'
insert into 表B select 2,'2005-8-2 03:02:06','ajdksj'
insert into 表B select 3,'2005-8-2 21:35:56','sjdskd'
--执行查询
select
a.*,b.e,b.f
from
表A a
inner join
表B b
on
a.a = b.d
where
not exists(select 1 from 表B where d=b.d and e>b.e)
SELECT a.*,b.e,b.f
from 表A a join
(select * from 表B T where not exists
(select 1 from 表B WHERE d=t.d and e>T.e))b on
a.a=b.d
--输出结果
a b c e f
--- ---- ------ ------------------------- --------
1 xx xxxx 2004-07-01 15:25:22.000 ddddd
2 yy yyyy 2005-08-02 03:02:06.000 ajdksj
3 zz zzzz 2005-08-02 21:35:56.000 sjdskd
A:
type_id|type_Name
B:
id | Type_id | Movie_Name
需要在B表中得到Type_id不同的5条记录的Movie_Name及type_name值..
搞了半天写不出来
好像一条sql搞不定啊
那在B表中取type_id不同的5条记录该怎么写呢？
select top 5 T.*,
A.type_name
from (
select * from B tb
where id=(select max(id)
from B
where type_id=tb.type_id)
)T
join A on A.type_id=T.type_id
表A：
id corp
------------------
1 客户1
2 客户2
表B：
id cusID url
----------------------
1 1 abc.com
2 1 xyz.com
表C：
id cusID email
-------------------------
1 1 dd@abc.com
表B与表C的cusID等于表A的id，请问当使用left join时，如何排除重复的记录，只显示相同记录中的一条记录，即：
id corp url email
---------------------------------
1 客户1 abc.com dd@abc.com
2 客户2
select distinct a.id,b.url,c.email
from 表A a left join
(select cusid,url from 表B b where not exists(select 1 from 表B where
cusid=b.cusid and id<b.id) )b on a.id=b.cusid
left join 表C c on a.id=c.cusid
or
select A.id
,A.corp
,B.url
,C.email
from A
left join B on A.id=B.cusID
left join C on A.id=C.cusID
where B.id=(select min(id) from B where cusID=A.id)
or
select A.id
,A.corp
,B.url
,C.email
from A
left join B on A.id=B.cusID
left join C on A.id=C.cusID
where B.id=(select top 1 id from B where cusID=A.id order by id)
２分组求最大．
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/
--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 b 1 b1--b的第一个值 */
--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/
--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5
*/
--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2
*/
--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
