有两个表 A(id,name)
1 小东
2 小明
3 小亮
和B (aid, sex, mail, time)
2 男 [email protected] 2008-06-09
2 女 [email protected] 2009-06-09 想显示为
name sex mail time
小东 暂无 暂无 暂无
小明 男 [email protected] 2009-06-09
小亮 暂无 暂无 暂无
(要求显示的时间为最新时间,如果aid没有等于id的话显示暂无)
解决方案 »
- @@@@@@@@@@SQL语句@@@@@@@@@@
- 问三个问题(在线等)
- 一个复杂的sql语句,求指点
- 第二次发贴:ms sql2005、 vs05、 visio如何交互
- VB6.0怎么调用存储过程?(菜鸟问题)
- 怎么在SQL2000中新建一个库
- 各位,帮我做一个SQL查询了。急,在线等
- win2003全文检索总是失败 提示“全文操作运行失败。查询子句只包含被忽略的词。”
- 关于SQL Server,采用DBLIB处理并发多连接的问题?
- delete触发器和update触发器代码一样,可以写在一起吗?
- vipid字段号小于7位,如何在vipid字段插入0.如0720512,如何实现
- 表设计问题啊!简单的设计问题
TB B ON A.ID=B.AID
A.name,
B.sex,
B.mail,
B.time
FROM tb1 AS A
LEFT JOIN tb2 AS B
ON A.id=B.aid
WHERE NOT EXISTS(SELECT * FROM tb2 WHERE aid=B.aid AND time>B.time);
a.name,
isnull(b.sex,'暂无') as sex,
isnull(b.mail,'暂无') as mail,
isnull(convert(varchar(10),b.[time],120),'暂无')
from
A
left join
B
on
a.id=b.aid
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (id INT,name VARCHAR(4))
INSERT INTO @tb1
SELECT 1,'小东' UNION ALL
SELECT 2,'小明' UNION ALL
SELECT 3,'小亮'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (aid INT,sex VARCHAR(2),mail VARCHAR(8),time DATETIME)
INSERT INTO @tb2
SELECT 2,'男','[email protected]','2008-06-09' UNION ALL
SELECT 2,'女','[email protected]','2009-06-09'--SQL查询如下:SELECT
A.name,
B.sex,
B.mail,
B.time
FROM @tb1 AS A
LEFT JOIN @tb2 AS B
ON A.id=B.aid
WHERE NOT EXISTS(SELECT * FROM @tb2 WHERE aid=B.aid AND time<B.time);/*
name sex mail time
---- ---- -------- -----------------------
小东 NULL NULL NULL
小明 男 [email protected] 2008-06-09 00:00:00.000
小亮 NULL NULL NULL(3 行受影响)*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(4))
insert [A]
select 1,'小东' union all
select 2,'小明' union all
select 3,'小亮'
if object_id('[B]') is not null drop table [B]
go
create table [B]([aid] int,[sex] varchar(2),[mail] varchar(8),[time] datetime)
insert [B]
select 2,'男','[email protected]','2008-06-09' union all
select 2,'女','[email protected]','2009-06-09'
---查询---
select
a.name,
isnull(b.sex,'暂无') as sex,
isnull(b.mail,'暂无') as mail,
isnull(convert(varchar(10),b.[time],120),'暂无') as [time]
from
A
left join
B
on
a.id=b.aid
and
not exists(select 1 from b t where t.aid=b.aid and t.[time]>b.[time])
---结果---
name sex mail time
---- ---- -------- ----------
小东 暂 暂无 暂无
小明 女 [email protected] 2009-06-09
小亮 暂 暂无 暂无(所影响的行数为 3 行)
create table B("aid" varchar(4) , "sex" char(2), "mail" varchar(10), "time" datetime)
insert into a values('1', '小dong')
insert into a values('2', '小明')
insert into a values('3', '小亮') insert into b select
'2' , '男' , '[email protected]', '2008-06-09' union all
select '2' , '女' , '[email protected]' ,'2009-06-09'
select name,isnull(sex,'暂无'),isnull(mail,'暂无'),isnull(time ,'暂无') from A left outer join b t on a.id = t.aid and id in (select id from B where aid = t.AID and b.time<T.time)
create table B("aid" varchar(4) , "sex" char(4), "mail" varchar(10), "time" varchar(10))
insert into a values('1', '小dong')
insert into a values('2', '小明')
insert into a values('3', '小亮') insert into b select
'2' , '男' , '[email protected]', '2008-06-09' union all
select '2' , '女' , '[email protected]' ,'2009-06-09'
select name,isnull(sex,'暂无'),isnull(mail,'暂无'),isnull(time ,'暂无') from A left outer join b t on a.id = t.aid and id in (select id from B where aid = t.AID and b.time<T.time)---------- ---- ---------- ----------
小dong ?无 ?无 ?无
小明 女 [email protected] 2009-06-09
小亮 ?无 ?无 ?无(3 件処理されました)
TB B ON A.ID=B.AID