刚注册的没有分,大家帮忙!!!
有两个表:t1,t2
t1字段:id,name
t2字段:aid,id,name
t1表id对应t2表中的id,
例子:t1:1 a
2 b
3 c t2:1 1 aa
2 1 bb
3 1 cc
4 2 dd
5 2 ee
6 3 ff
7 3 gg
8 3 hh现在想查询出这样的结果:1 a 3 1 cc
2 b 5 x ee
3 c 8 3 hh也就是把t2表中对应t1最大的id查询出来
有两个表:t1,t2
t1字段:id,name
t2字段:aid,id,name
t1表id对应t2表中的id,
例子:t1:1 a
2 b
3 c t2:1 1 aa
2 1 bb
3 1 cc
4 2 dd
5 2 ee
6 3 ff
7 3 gg
8 3 hh现在想查询出这样的结果:1 a 3 1 cc
2 b 5 x ee
3 c 8 3 hh也就是把t2表中对应t1最大的id查询出来
解决方案 »
- 请问下这条语句的错误。
- 在查询分析器下有没有一条语句可以统计出一个数据库的表的个数或存储过程,视图的个数??急
- 请问如何在一个储存过程中调用另外一个储存过程,需要传入一个参数
- 关于多数据库的触发器问题!!!
- 关于上传图片 上传图片地址提交入数据库的问题
- SQL Server 2005服务器安装 配置
- java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]将截断字符串或二进制数据?
- SQLSERVER 备分出错
- 请问有什么工具可以打MS-SQL Server 中的描述读出来 走 送100分
- 大力,我还是有点问题!!!!!!
- sql中的多行 转换成一行
- sql server 2000问题 远程安装 提示拒绝访问
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]
GO
CREATE TABLE [ta]([id] INT,[name] VARCHAR(1))
INSERT [ta]
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
--------------开始查询--------------------------
----------------结果----------------------------
/*
*/
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[aid] INT,[name] VARCHAR(2))
INSERT [tb]
SELECT 1,1,'aa' UNION ALL
SELECT 2,1,'bb' UNION ALL
SELECT 3,1,'cc' UNION ALL
SELECT 4,2,'dd' UNION ALL
SELECT 5,2,'ee' UNION ALL
SELECT 6,3,'ff' UNION ALL
SELECT 7,3,'gg' UNION ALL
SELECT 8,3,'hh'
--------------开始查询--------------------------
SELECT * FROM [ta] a JOIN [tb] b ON a.id=b.aid
AND b.[id]=(SELECT MAX(id) FROM [tb] WHERE aid=b.aid)
----------------结果----------------------------
/*
id name id aid name
----------- ---- ----------- ----------- ----
1 a 3 1 cc
2 b 5 2 ee
3 c 8 3 hh(3 行受影响)
*/
AND NOT EXISTS (SELECT 1FROM [tb] WHERE aid=b.aid AND id>b.id)
FROM t1
INNER JOIN t2
ON t1.id=t2.id
AND b.id=(SELECT MAX(id)FROM t1 WHERE aid=t2.id)
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]
GO
CREATE TABLE [ta]([id] INT,[name] VARCHAR(1))
INSERT [ta]
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
--------------开始查询--------------------------
----------------结果----------------------------
/*
*/
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[aid] INT,[name] VARCHAR(2))
INSERT [tb]
SELECT 1,1,'aa' UNION ALL
SELECT 2,1,'bb' UNION ALL
SELECT 3,1,'cc' UNION ALL
SELECT 4,2,'dd' UNION ALL
SELECT 5,2,'ee' UNION ALL
SELECT 6,3,'ff' UNION ALL
SELECT 7,3,'gg' UNION ALL
SELECT 8,3,'hh'
;with cte as
(
select t2.*,px=ROW_NUMBER ()over(partition by t1.id order by t2.id) from ta t1 inner join tb t2 on t1.id =t2.aid
)
select * from cte where px=1
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]
GO
CREATE TABLE [ta]([id] INT,[name] VARCHAR(1))
INSERT [ta]
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[aid] INT,[name] VARCHAR(2))
INSERT [tb]
SELECT 1,1,'aa' UNION ALL
SELECT 2,1,'bb' UNION ALL
SELECT 3,1,'cc' UNION ALL
SELECT 4,2,'dd' UNION ALL
SELECT 5,2,'ee' UNION ALL
SELECT 6,3,'ff' UNION ALL
SELECT 7,3,'gg' UNION ALL
SELECT 8,3,'hh'
;with cte as
(
select t2.*,px=ROW_NUMBER ()over(partition by t1.id order by t2.id desc) from ta t1 inner join tb t2 on t1.id =t2.aid
)
select * from cte where px=1
AND b.[id] in (SELECT MAX(id) FROM [tb] c group by c.aid)
declare @t1 table(id int ,name varchar(2))
declare @t2 table(aid int,id int,name varchar(2))
insert into @t1 select 1,'a' union all select 2,'b'
union all select 3,'c'
insert into @t2 select 2,1,'bb'
union all select 3,1,'cc'
union all select 4,2,'dd'
union all select 5,2,'ee'
union all select 6,3,'ff'
union all select 7,3,'gg'
union all select 8,3,'hh'
select t1.id,t1.name,t2.aid,t2.id,t2.name from @t1 t1 join @t2 t2
on t2.aid=(select MAX(aid) from @t2 where id=t1.id)
FROM t1,t2
where t1.id=t2.id
AND t1.id=(SELECT MAX(id)FROM t1 WHERE t1.id=t2.id)
drop table t1
create table t1 ( id tinyint identity(1,1) primary key,value varchar(2) )
insert into t1 select 'a' union all select 'b' union all select 'c'if object_id('t2') is not null
drop table t2
create table t2
(
id tinyint identity(1,1) primary key,
aId tinyint references t1(id),
value varchar(2)
)
insert into t2 select 1,'aa' union all select 1,'bb' union all select 1,'cc' union all
select 2,'dd' union all select 2,'ee' union all select 2,'ff' union all
select 3,'gg' union all select 3,'hh' union all select 3,'ii'select * from t1
select * from t2select *
from t1 join t2
on t1.id=t2.aId and t2.id = (select max(t2.id) from t2 where t1.id=t2.aId)
order by t1.idselect * from t1,t2
where t1.id = t2.aId and t2.id = (select max(id) from t2 where t1.id=t2.aId)
order by t1.id