6,129
社区成员
发帖
与我相关
我的任务
分享
var query= from t1 in db.table1
join t2 in db.table2 on t1.tid1 equals t2.tid1 into g
from t2 in g.DefaultIfEmpty()
select new {tid1=t1.tid1,name=t1.name,time=t2==null?null:g.Max(x=>x.time)};
List<table1> t1List
Lins<table1> t1List = uob.GetListInfo('select * from table1');
// 括号中为表1的查询语句,t1List为表1的数据集合(记得根据自己的查询方法修改)
Lins<table2> t2List = uobj.GetListInfo('select tid1,max(time) from table2 group by tid1'); // 同上
var result = from u in t1List
join s in t2List on u.tid1 equals s.tid1
into all from s in all.DefaultIfEmpty()
select new {
u.tid1,
u.name,
time = s == null ? "" : s.time //重点:判断从表数据是否存在
};
// this.GridView1.DataSource = result;
// this.GridView1.DataBind();
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([tid1] int,[name] varchar(4))
insert [table1]
select 1,'张一' union all
select 2,'张二' union all
select 3,'张三' union all
select 4,'张四' union all
select 5,'张五'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([tid2] int,[tid1] int,[time] datetime)
insert [table2]
select 1,1,'2013-01-12' union all
select 2,1,'2013-03-12' union all
select 3,1,'2013-02-15' union all
select 4,3,'2013-05-12' union all
select 5,2,'2014-03-10' union all
select 6,2,'2014-10-10'
;with tt1(tid1,time)
as (
select a.tid1,max(a.time)
from table2 a
group by a.tid1
)
select table1.tid1,table1.name ,tt1.time
from table1 left outer join tt1 on table1.tid1 =tt1.tid1
order by table1.tid1
--或者是
select tid1 ,name,time from
(
select ROW_NUMBER()over( partition by A.tid1 order by [time])as rownum ,A.tid1,A.name,B.time
from table1 A left join table2 B on A.tid1=B.tid1
) temp where temp.rownum=1
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-28 16:46:56
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([tid1] int,[name] varchar(4))
insert [table1]
select 1,'张一' union all
select 2,'张二' union all
select 3,'张三' union all
select 4,'张四' union all
select 5,'张五'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([tid2] int,[tid1] int,[time] datetime)
insert [table2]
select 1,1,'2013-01-12' union all
select 2,1,'2013-03-12' union all
select 3,1,'2013-02-15' union all
select 4,3,'2013-05-12' union all
select 5,2,'2014-03-10' union all
select 6,2,'2014-10-10'
--------------开始查询--------------------------
SELECT
a.tid1,a.name,b.time
FROM
[table1] AS a
LEFT JOIN [table2] AS b ON a.tid1=b.tid1
WHERE
NOT EXISTS ( SELECT
1
FROM
table2
WHERE
tid1=b.tid1 AND [time]>b.[time] )
----------------结果----------------------------
/* tid1 name time
----------- ---- -----------------------
1 张一 2013-03-12 00:00:00.000
2 张二 2014-10-10 00:00:00.000
3 张三 2013-05-12 00:00:00.000
4 张四 NULL
5 张五 NULL
(5 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-28 16:46:56
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([tid1] int,[name] varchar(4))
insert [table1]
select 1,'张一' union all
select 2,'张二' union all
select 3,'张三' union all
select 4,'张四' union all
select 5,'张五'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([tid2] int,[tid1] int,[time] datetime)
insert [table2]
select 1,1,'2013-01-12' union all
select 2,1,'2013-03-12' union all
select 3,1,'2013-02-15' union all
select 4,3,'2013-05-12' union all
select 5,2,'2014-03-10' union all
select 6,2,'2014-10-10'
--------------开始查询--------------------------
SELECT
a.tid1,a.name,b.time
FROM
[table1] AS a
LEFT JOIN [table2] AS b ON a.tid1=b.tid1
WHERE
NOT EXISTS ( SELECT
1
FROM
table2
WHERE
tid1=b.tid1 AND [time]<b.[time] )
----------------结果----------------------------
/* tid1 name time
----------- ---- -----------------------
1 张一 2013-01-12 00:00:00.000
2 张二 2014-03-10 00:00:00.000
3 张三 2013-05-12 00:00:00.000
4 张四 NULL
5 张五 NULL
(5 行受影响)
*/