有2个表结构如下:
表一:
CREATE TABLE [dbo].[website_name] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[value] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[re] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
表二:
CREATE TABLE [dbo].[website_data] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[title] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[content] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[date] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[value] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[re] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]测试数据:
website_name
ID name value re
1 AAA 1 1
2 BBB 2 1
3 CCC 3 1
4 DDD 1 2
5 EEE 2 2
6 FFF 1 3
...
website_data
ID title content value re
1 AAA1 FSDFS.. 1 1
2 AAA2 FSDFS.. 1 1
3 AAA3 FSDFS.. 2 1
4 AAA4 FSDFS.. 3 1
5 AAA5 FSDFS.. 3 1
6 AAA6 FSDFS.. 2 2
7 AAA7 FSDFS.. 1 2
8 AAA8 FSDFS.. 3 1
9 AAA9 FSDFS.. 3 1
10 AAg FSDFS.. 2 1
11 AAs FSDFS.. 2 1
...其中website_name.value=website_data.value和website_name.re=website_data.re要查询当website_name.re=1时,首先计算出website_name有几个不同的value值,这里为3,然后在website_data中提取2*3=6条记录(2是任意指定的常量),而且满足website_data.re=1的每个value的记录都是2条,只能识别SQL2000,谢绝2005,
表一:
CREATE TABLE [dbo].[website_name] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[value] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[re] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
表二:
CREATE TABLE [dbo].[website_data] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[title] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[content] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[date] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[value] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[re] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]测试数据:
website_name
ID name value re
1 AAA 1 1
2 BBB 2 1
3 CCC 3 1
4 DDD 1 2
5 EEE 2 2
6 FFF 1 3
...
website_data
ID title content value re
1 AAA1 FSDFS.. 1 1
2 AAA2 FSDFS.. 1 1
3 AAA3 FSDFS.. 2 1
4 AAA4 FSDFS.. 3 1
5 AAA5 FSDFS.. 3 1
6 AAA6 FSDFS.. 2 2
7 AAA7 FSDFS.. 1 2
8 AAA8 FSDFS.. 3 1
9 AAA9 FSDFS.. 3 1
10 AAg FSDFS.. 2 1
11 AAs FSDFS.. 2 1
...其中website_name.value=website_data.value和website_name.re=website_data.re要查询当website_name.re=1时,首先计算出website_name有几个不同的value值,这里为3,然后在website_data中提取2*3=6条记录(2是任意指定的常量),而且满足website_data.re=1的每个value的记录都是2条,只能识别SQL2000,谢绝2005,
declare @i int;set @i=1;select @i=2*count(value) from website_name where re=@menuid;exec('select top '+@i+' title =left(title ,30),* from website_data where re='''+@menuid+''' order by date desc')
ID title content value re
1 AAA1 FSDFS.. 1 1
2 AAA2 FSDFS.. 1 1
3 AAA3 FSDFS.. 2 1
4 AAA4 FSDFS.. 3 1
5 AAA5 FSDFS.. 3 1
10 AAg FSDFS.. 2 1 共6条,每个value都是2条
--建表
if object_id('website_name') is not null drop table website_nameCREATE TABLE [dbo].[website_name] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[value] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[re] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
go if object_id('website_data') is not null drop table website_data
CREATE TABLE [dbo].[website_data] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[title] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[content] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[date] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[value] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[re] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go--插入测试数据
insert into website_name
select 'AAA','1','1' union all
select 'BBB','2','1' union all
select 'CCC','3','1' union all
select 'DDD','1','2' union all
select 'EEE','2','2' union all
select 'FFF','1','3' insert into website_data
select 'AAA1','FSDFS..','','1','1' union all
select 'AAA2','FSDFS..','','1','1' union all
select 'AAA3','FSDFS..','','2','1' union all
select 'AAA4','FSDFS..','','3','1' union all
select 'AAA5','FSDFS..','','3','1' union all
select 'AAA6','FSDFS..','','2','2' union all
select 'AAA7','FSDFS..','','1','2' union all
select 'AAA8','FSDFS..','','3','1' union all
select 'AAA9','FSDFS..','','3','1' union all
select 'AAg','FSDFS..','','2','1' union all
select 'AAs','FSDFS..','','2','1' --SQL语句 (2000)
declare @i int;set @i=1;
declare @menuid int;set @menuid=1;select @i=2*count(value)
from website_name
where re=@menuid;exec ('select top '+@i+' title =left(title ,30),* from website_data t1 where [id] in (select top 2 id from website_data t2 where re='''+@menuid+''' and t1.value=t2.value) order by date desc')
--结果
---------------------------------------------------------
--ID title content data value re
--1 AAA1 FSDFS.. 1 1
--2 AAA2 FSDFS.. 1 1
--3 AAA3 FSDFS.. 2 1
--4 AAA4 FSDFS.. 3 1
--5 AAA5 FSDFS.. 3 1
--10 AAg FSDFS.. 2 1
---------------------------------------------------------
猜一下,是不是这样?