有2个表,一个是Information,另外个是InformationComment
Information表
id State
1 1
2 1
3 0
4 0
5 1
InformationComment表
id InformationID
a 1
b 2
c 1
d 3
e 4
f 2
g 3要得到的结果 #temp
id InformationID state
a 1 1
b 2 1Information.id与InformationComment.InformationID关联。现在需要查出Information.state为1,在InformationComment.InformationID有重复项时取第一条的结果
Information表
id State
1 1
2 1
3 0
4 0
5 1
InformationComment表
id InformationID
a 1
b 2
c 1
d 3
e 4
f 2
g 3要得到的结果 #temp
id InformationID state
a 1 1
b 2 1Information.id与InformationComment.InformationID关联。现在需要查出Information.state为1,在InformationComment.InformationID有重复项时取第一条的结果
from InformationComment a
join Information b
on a.InformationID =b.id
where not exists(select 1 from Information where State = b.State and id<b.id)
select a.*,b.state
from InformationComment a
right join Information b on a.informationid = b.id
where b.state = 1 and
not exists(select 1 from InformationComment where informationid = a.informationid and id < a.id)
insert #Information
select 1, 1 union all
select 2 , 1 union all
select 3 , 0 union all
select 4 , 0 union all
select 5 , 1
create table #InformationComment(id varchar(10),InformationID int)
insert #InformationComment
select 'a', 1 union all
select 'b ', 2 union all
select 'c ', 1 union all
select 'd ', 3 union all
select 'e ', 4 union all
select 'f ', 2 union all
select 'g ', 3 select b.id,b.InformationID,a.state from #Information a,
(select *,row_number() over(partition by InformationID order by getdate()) as rn
from #InformationComment ) b
where a.id=b.InformationID and rn=1 and a.state=1
if object_id('[Information]') is not null drop table [Information]
go
create table [Information]([id] int,[State] int)
insert [Information]
select 1,1 union all
select 2,1 union all
select 3,0 union all
select 4,0 union all
select 5,1
go
if object_id('[InformationComment]') is not null drop table [InformationComment]
go
create table [InformationComment]([id] varchar(1),[InformationID] int)
insert [InformationComment]
select 'a',1 union all
select 'b',2 union all
select 'c',1 union all
select 'd',3 union all
select 'e',4 union all
select 'f',2 union all
select 'g',3
go
---查询---
select b.*,a.state
from InformationComment b
join Information a on a.id=b.InformationID
and not exists(select 1 from InformationComment where InformationID=b.InformationID and id<b.id)
where a.state=1
---结果---
id InformationID state
---- ------------- -----------
a 1 1
b 2 1(所影响的行数为 2 行)
insert into Information
select 1 , 1 union all
select 2 , 1 union all
select 3 , 0 union all
select 4 , 0 union all
select 5 , 1
drop table InformationComment
create table InformationComment(id varchar(20) ,InformationID int)
insert into InformationComment
select 'a' , 1 union all
select 'b' , 2 union all
select 'c' , 1 union all
select 'd' , 3 union all
select 'e' , 4 union all
select 'f' , 2 union all
select 'g' , 3 select A.*,B.* from Information B inner join
(
select InformationID,min(id) as id from InformationComment group by InformationID
)A
on B.id = A.InformationID
where B.State = 1
if object_id('[Information]') is not null drop table [Information]
create table [Information]([id] int,[State] int)
insert [Information]
select 1,1 union all
select 2,1 union all
select 3,0 union all
select 4,0 union all
select 5,1--> 测试数据:[InformationComment]
if object_id('[InformationComment]') is not null drop table [InformationComment]
create table [InformationComment]([id] varchar(1),[InformationID] int)
insert [InformationComment]
select 'a',1 union all
select 'b',2 union all
select 'c',1 union all
select 'd',3 union all
select 'e',4 union all
select 'f',2 union all
select 'g',3select b.[id],b.[InformationID],a.[State]from [Information] a
inner join [InformationComment] b on a.[id]=b.[InformationID]
where a.[State]=1
and not exists
(
select 1 from [InformationComment] where c.InformationID=b.InformationID and c.id<b.id
)
/*
id InformationID State
---- ------------- -----------
a 1 1
b 2 1*/
DROP TABLE [Information]
GO
CREATE TABLE [Information]([id] INT,[State] INT)
INSERT [Information]
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,0 UNION ALL
SELECT 4,0 UNION ALL
SELECT 5,1
GO
--SELECT * FROM [Information]--> 生成测试数据表:InformationCommentIF NOT OBJECT_ID('[InformationComment]') IS NULL
DROP TABLE [InformationComment]
GO
CREATE TABLE [InformationComment]([id] NVARCHAR(10),[InformationID] INT)
INSERT [InformationComment]
SELECT 'a',1 UNION ALL
SELECT 'b',2 UNION ALL
SELECT 'c',1 UNION ALL
SELECT 'd',3 UNION ALL
SELECT 'e',4 UNION ALL
SELECT 'f',2 UNION ALL
SELECT 'g',3
GO
--SELECT * FROM [InformationComment]-->SQL查询如下:
select a.id,a.InformationID,b.State
into #temp
from InformationComment a
join Information b
on a.InformationID =b.id
where b.State=1
and not exists(select 1 from InformationComment where [InformationID]=a.[InformationID] and id<a.id)
select * from #temp
/*
id InformationID State
a 1 1
b 2 1
*/
drop table #temp
LEFT JOIN INFORMATION b
ON a.imformationid=b.id