表结构,TABLE
name age
--------------
a 11
b 22
c 33
d 44
用SQL:select * from TEBLE可以查出和上述一样的结构
但是我想得到这样的结果
id name age
--------------------
1 a 11
2 b 22
3 c 33
4 d 44
前提:TABLE数据库结构不变
这个SQL语句该怎么写?
name age
--------------
a 11
b 22
c 33
d 44
用SQL:select * from TEBLE可以查出和上述一样的结构
但是我想得到这样的结果
id name age
--------------------
1 a 11
2 b 22
3 c 33
4 d 44
前提:TABLE数据库结构不变
这个SQL语句该怎么写?
INSERT INTO #A VALUES('A' , 11)
INSERT INTO #A VALUES('B' , 22)
INSERT INTO #A VALUES('C' , 33)
INSERT INTO #A VALUES('D' , 44)
SELECT
ROW_NUMBER() OVER(ORDER BY NAM ) AS ID,
NAM ,
AGE
FROM
#A結果
1 A 11
2 B 22
3 C 33
4 D 44
insert into @t select * from table
select * from @t
insert into @t select 'a',11
insert into @t select 'b',22
insert into @t select 'c',33
insert into @t select 'd',44
select [count]=(select count(1) + 1 from @t where a.age > age),*
from @t a
/*count name age
----------- ---- -----------
1 a 11
2 b 22
3 c 33
4 d 44(所影响的行数为 4 行)*/
declare @t table(name nchar(10),age int)
insert into @t select 'a',11
insert into @t select 'b',22
insert into @t select 'c',33
insert into @t select 'd',44select * from
(select ID=(row_number() over(order by name)),name,age from @t ) t
/*
ID name age
-------------------- ---------- -----------
1 a 11
2 b 22
3 c 33
4 d 44
*/
--树上的鸟儿的语句没有问题
declare @t table(name nchar(10),age int)
insert into @t select 'a',11
insert into @t select 'b',22
insert into @t select 'c',33
insert into @t select 'd',44select id=(select count(1) from @t where age<=a.age),* from @t a order by age
/*
id name age
----------- ---------- -----------
1 a 11
2 b 22
3 c 33
4 d 44
哎
本人笨拙
这是本人的SQL
select id = (select count(1) from D_cutpassel where a.iGrade>iGrade),iBigPassel_No,D_bigpassel.fLong,D_bigpassel.bRepair,sD_C_P_No,D_cutpassel.fLong,iGrade,sSpoil,sKindSpoil,sRe from D_cutpassel,D_bigpassel where D_bigpassel.sD_C = D_cutpassel.sD_C and D_cutpassel.sD_C = '03333333' and D_bigpassel.sD_C_BP_No = D_cutpassel.sD_C_BP_No还有一个a不知道放哪里
如放在from D_cutpassel,D_bigpassel a
列前缀 'D_bigpassel' 与查询中所用的表名或别名不匹配。
如放在from D_cutpassel a,D_bigpassel
列前缀 'D_cutpassel' 与查询中所用的表名或别名不匹配。
怎么放啊?
insert into @t select 'a',11
insert into @t select 'b',22
insert into @t select 'c',33
insert into @t select 'd',44--你的数据库如果是 2005 的话可以两个都用 如果是 2000 就 仅限与第一个
1.
select id=(select count(1) from @t where age<=a.age),a.* from @t a2.
select id=row_number() over(order by [name]),a.* from @t a
--2000不依赖字段
select ID=identity(int,1,1),name,age into #z from @t
select * from #z
--05的方法
select ID=(row_number() over(order by name)),name,age from @t
from D_cutpassel a,D_bigpassel
不过我有点晕了
应该多加点分的
可现在不能加
TO 飞天小虫
from D_cutpassel a,D_bigpassel
如放在from D_cutpassel a,D_bigpassel
列前缀 'D_cutpassel' 与查询中所用的表名或别名不匹配。
提示不正确!!!
全部代码:
select id = (select count(1) from D_cutpassel where a.iGrade>iGrade),iBigPassel_No,D_bigpassel.fLong,D_bigpassel.bRepair,sD_C_P_No,
D_cutpassel.fLong,iGrade,sSpoil,sKindSpoil,sRe from D_cutpassel a,D_bigpassel where
D_bigpassel.sD_C = D_cutpassel.sD_C and D_cutpassel.sD_C = '03333333' and D_bigpassel.sD_C_BP_No = D_cutpassel.sD_C_BP_No
哪里错了
该怎么写?
TO 各位
数据库为2000,非2005
因为 'D_cutpassel' 的表名多次使用,然后又用了a,可能有干扰,不过两个表中有相同的字段名必须得区分开,只能用表名.列名来表示啊,而这样又与a相矛盾
请问这个问题怎么解决啊?!
一波三折啊!哎
select id = (select count(1) from D_cutpassel where a.iGrade> iGrade),
iBigPassel_No,D_bigpassel.fLong,D_bigpassel.bRepair,sD_C_P_No,
a.fLong,iGrade,sSpoil,sKindSpoil,sRe
from D_cutpassel a,D_bigpassel where
D_bigpassel.sD_C = a.sD_C
and a.sD_C = '03333333'
and D_bigpassel.sD_C_BP_No = a.sD_C_BP_No