USE tempdb
GO
IF OBJECT_ID('A1') IS NOT NULL DROP TABLE A1
IF OBJECT_ID('A2') IS NOT NULL DROP TABLE A2
GO
CREATE TABLE A1(DM VARCHAR(10),mc NVARCHAR(10))
CREATE TABLE A2(ID INT,DM VARCHAR(10),bm NVARCHAR(10))
GO
INSERT INTO A1 VALUES ('001','北京')
INSERT INTO A1 VALUES ('002','南京')
INSERT INTO A1 VALUES ('003','上海')
GO
INSERT INTO A2 VALUES (1, '001','A')
INSERT INTO A2 VALUES (2, '001','B')
INSERT INTO A2 VALUES (3, '001','C')
GO
SELECT a2.id,a1.dm,a2.bm
FROM A1 CROSS JOIN A2 AS a2
ORDER BY a1.dm,a2.id
/*
id dm bm
1 001 A
2 001 B
3 001 C
1 002 A
2 002 B
3 002 C
1 003 A
2 003 B
3 003 C
*/
--测试数据
if not object_id(N'Tempdb..#A1') is null
drop table #A1
Go
Create table #A1([DM] nvarchar(23),[mc] nvarchar(22))
Insert #A1
select N'001',N'北京' union all
select N'002',N'南京' union all
select N'003',N'上海'
GO
if not object_id(N'Tempdb..#A2') is null
drop table #A2
Go
Create table #A2([ID] int,[dm] nvarchar(23),[bm] nvarchar(21))
Insert #A2
select 1,N'001',N'A' union all
select 2,N'001',N'B' union all
select 3,N'001',N'C'
Go
--测试数据结束
SELECT ROW_NUMBER() OVER (ORDER BY #A1.DM) AS ID,
#A1.DM,
bm
FROM #A1,
#A2;
Create table TA (DM varchar(10),mc varchar(20))
Create table TB (id int,DM varchar(10),bm varchar(20))
insert into TA values('001','北京'),('002','南京'),('003','上海')
insert into TB values(1,'001','A'),(2,'001','B'),(3,'001','C')
这四条是生成测试数据数据
select ROW_NUMBER() OVER(ORDER BY TA.DM) AS ID,TA.DM,TB.BM
from TA CROSS JOIN TB
ORDER BY TA.DM
这条才是执行语句,但问题来了SQL2000 不支持ROW_NUMBER() 函数
求教大佬关于我写的sql语句问题。我感觉自己逻辑上没有错。为什么运行不出来呢。求指正我哪里错了。 我的sql:select * from orders where purch_amt>(select avg(purch_amt) from orders group by customer_id ...