我有一个问题
CREATE TABLE [dbo].[sorttable](
[id] [int] IDENTITY(1,1) NOT NULL,
[sort] [nvarchar](50) NULL) GO
/****** Object: Table [dbo].[details] Script Date: 04/10/2012 15:59:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[details](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[sortid] [int] NOT NULL,
[countid] [int] NULL
) GO
两个表。sorttable，details，实现两表联合按sortid分组后取各分组中countid最大的。
------解决方案--------------------
- SQL code
if OBJECT_ID('[sorttable]') is not null drop table [A1]create table [sorttable]([ID] int,[sort] nvarchar(255))insert into [sorttable]select 1,'排序1'union allselect 2,'排序2'union allselect 3,'排序3'if OBJECT_ID('[details]')is not null drop table [B1]create table [details]([ID] int,[name] nvarchar(255),[sortid] int,[count] int)insert into [details]select 1,'明细1',1,1union allselect 2,'明细2',1,2union allselect 3,'明细3',1,3union allselect 4,'明细4',1,10union allselect 5,'明细5',2,10union allselect 6,'明细6',2,20union allselect 7,'明细7',2,30union allselect 8,'明细8',2,100union allselect 9,'明细9',3,101union allselect 10,'明细10',3,201union allselect 11,'明细11',3,301union allselect 12,'明细12',3,1000select MAX([count]) as [count], max([name]) as [name], MAX([details].ID) as detailid, MAX([sorttable].sort) as sortname from [details],[sorttable] where [details].sortid=[sorttable].IDgroup by [details].sortiddrop table [sorttable]drop table [details]
------解决方案--------------------
- SQL code
if OBJECT_ID('[sorttable]') is not null drop table [A1]create table [sorttable]([ID] int,[sort] nvarchar(255))insert into [sorttable]select 1,'排序1'union allselect 2,'排序2'union allselect 3,'排序3'if OBJECT_ID('[details]')is not null drop table [B1]create table [details]([ID] int,[name] nvarchar(255),[sortid] int,[count] int)insert into [details]select 1,'明细1',1,1union allselect 2,'明细2',1,2union allselect 3,'明细3',1,3union allselect 4,'明细4',1,10union allselect 5,'明细5',2,10union allselect 6,'明细6',2,20union allselect 7,'明细7',2,30union allselect 8,'明细8',2,100union allselect 9,'明细9',3,101union allselect 10,'明细10',3,201union allselect 11,'明细11',3,301union allselect 12,'明细12',3,1000SELECT MAX(S.ID) AS ID,MAX(COUNT) AS 最大数量 ,MAX(name) AS 名字 ,MAX(s.sort) AS 排序名FROM details d left join sorttable s on d.sortid = s.ID GROUP BY SORT /* ID 最大数量 名字 排序名----------- ----------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------1 10 明细4 排序12 100 明细8 排序23 1000 明细9 排序3(3 行受影响)*/