解决方案 »
- 菜鸟恳求一SQL语句 关于号码筛选 比较急~!
- 请问如何将同类项目合并汇总?
- 怎样把MYSQL的库导入到SERVER2000里啊,麻烦高手给予指点!!!
- 数据库恢复出现错误,怎么办
- 谁还记得那个关于SQL Server 对象命名的链接?(好像是邹建或是大力发起的帖)
- SQL Server代理的作业存储在硬盘上的什么位置?
- SQL语句急切
- 请问这是什么连接?
- 基础——删除重复数据运行错误
- 哪有OCP DBA8i 的官方教材下载???
- 在建立与服务器的连接时出错。在连接到 SQL Server 2005 时,在默认的设置下 SQL Server 不允许进行远程连接可能会导致此失败。
- 求一效率最高的 分类汇总查询
if object_id('[Information]') is not null drop table [Information]
create table [Information]([ID] varchar(1),[State] int)
insert [Information]
select 'a',0 union all
select 'b',1 union all
select 'c',1 union all
select 'd',1 union all
select 'e',0
--> 测试数据:[InformationComment]
if object_id('[InformationComment]') is not null drop table [InformationComment]
create table [InformationComment]([ID] varchar(3),[InformationID] varchar(1),[Content] varchar(8))
insert [InformationComment]
select 'I','a','回复内容' union all
select 'II','a','回复内容' union all
select 'III','c','回复内容' union all
select 'IV','b','回复内容' union all
select 'V','c','回复内容'select b.[id],b.[InformationID],a.[State],b.[Content] from [Information] a
left join [InformationComment] b
on a.[id]=b.InformationID where a.[State]=1
and not exists (select 1 from [InformationComment] where [InformationID]=b.[InformationID] and [id]<b.[id])
/*
id InformationID State Content
---- ------------- ----------- --------
IV b 1 回复内容
III c 1 回复内容
NULL NULL 1 NULL(3 行受影响)*/
if object_id('[Information]') is not null drop table [Information]
create table [Information]([ID] varchar(1),[State] int)
insert [Information]
select 'a',0 union all
select 'b',1 union all
select 'c',1 union all
select 'd',1 union all
select 'e',0
--> 测试数据:[InformationComment]
if object_id('[InformationComment]') is not null drop table [InformationComment]
create table [InformationComment]([ID] varchar(3),[InformationID] varchar(1),[Content] varchar(8))
insert [InformationComment]
select 'I','a','回复内容' union all
select 'II','a','回复内容' union all
select 'III','c','回复内容' union all
select 'IV','b','回复内容' union all
select 'V','c','回复内容'select b.[id],a.[id]as InformationID,a.[State],b.[Content] from [Information] a
left join [InformationComment] b
on a.[id]=b.InformationID where a.[State]=1
and not exists (select 1 from [InformationComment] where [InformationID]=b.[InformationID] and [id]<b.[id])
order by b.[id] desc
/*
id InformationID State Content
---- ------------- ----------- --------
IV b 1 回复内容
III c 1 回复内容
NULL d 1 NULL
(3 行受影响)*/
if object_id('[Information]') is not null drop table [Information]
create table [Information]([ID] varchar(1),[State] int)
insert [Information]
select 'a',0 union all
select 'b',1 union all
select 'c',1 union all
select 'd',1 union all
select 'e',0
--> 测试数据:[InformationComment]
if object_id('[InformationComment]') is not null drop table [InformationComment]
create table [InformationComment]([ID] varchar(3),[InformationID] varchar(1),[Content] varchar(8))
insert [InformationComment]
select 'I','a','回复内容' union all
select 'II','a','回复内容' union all
select 'III','c','回复内容' union all
select 'IV','b','回复内容' union all
select 'V','c','回复内容' select b.[id],a.[id]as InformationID,a.[State],b.[Content] from [Information] a
left join [InformationComment] b
on a.[id]=b.InformationID where a.[State]=1
and not exists (select 1 from [InformationComment] where [InformationID]=b.[InformationID] and [id] <b.[id])
order by b.[id] desc
/*
id InformationID State Content
---- ------------- ----------- --------
IV b 1 回复内容
III c 1 回复内容
NULL d 1 NULL
(3 行受影响) */