表A的结构ID aa bb
-- -- --
1 a1 b1
2 a2 b2
3 a3 b3
4 a4 b4
5 a5 b5
6 a6 b6
7 a7 b7表B的结构
ID1 ID2 cc
-- -- --
1 2 c1
1 3 c2
4 5 c3
4 8 c4
6 7 c5希望得到表A.ID = 1的子ID的数据信息。就是2 a2 b2 c1
3 a3 b3 c2就可以。
-- -- --
1 a1 b1
2 a2 b2
3 a3 b3
4 a4 b4
5 a5 b5
6 a6 b6
7 a7 b7表B的结构
ID1 ID2 cc
-- -- --
1 2 c1
1 3 c2
4 5 c3
4 8 c4
6 7 c5希望得到表A.ID = 1的子ID的数据信息。就是2 a2 b2 c1
3 a3 b3 c2就可以。
解决方案 »
- 求高手,sql2000每行返回另表差值最小的10行
- left join 如果子表中没有此记录,则取 上面最近一条 记录
- sql server varchar 类型默认值 要求 convert(varchar,1000000000000+ID)+convert(varchar,getdate(),112)
- mssql数据类型转换
- 一个SQL语句,但是不知道该怎么写
- sql 怎么从两个表中筛选出客户最后一次购买记录?
- 求一分组的SQL语句?
- 帮忙写一个SQL语句
- 遇到的最难的存储过程嵌套游标问题,高手请进!!!
- msdb数据库出错
- 如何利用触发器动态创建表
- 自动编号,在sql server 2000 中建表如何建立一个字段是自动编号,比如从1开始,直到无穷大!
insert into A values(1, 'a1', 'b1')
insert into A values(2, 'a2', 'b2')
insert into A values(3, 'a3', 'b3')
insert into A values(4, 'a4', 'b4')
insert into A values(5, 'a5', 'b5')
insert into A values(6, 'a6', 'b6')
insert into A values(7, 'a7', 'b7')
create table B(ID1 int, ID2 int, cc varchar(10))
insert into B values(1, 2, 'c1')
insert into B values(1, 3, 'c2')
insert into B values(4, 5, 'c3')
insert into B values(4, 8, 'c4')
insert into B values(6, 7, 'c5')
goselect m.* , n.cc from
(select t.id2 , a.aa , a.bb from a,(select b.id2 from a , b where a.id = 1 and a.id = b.id1) t where t.id2 = a.id) m,
(select b.id2 , b.cc from a , b where a.id = 1 and a.id = b.id1) n
where m.id2 = n.id2drop table A,B/*
id2 aa bb cc
----------- ---------- ---------- ----------
2 a2 b2 c1
3 a3 b3 c2(所影响的行数为 2 行)
*/
create table A(ID int, aa varchar(10) , bb varchar(10))
insert into A values(1, 'a1', 'b1')
insert into A values(2, 'a2', 'b2')
insert into A values(3, 'a3', 'b3')
insert into A values(4, 'a4', 'b4')
insert into A values(5, 'a5', 'b5')
insert into A values(6, 'a6', 'b6')
insert into A values(7, 'a7', 'b7')
create table B(ID1 int, ID2 int, cc varchar(10))
insert into B values(1, 2, 'c1')
insert into B values(1, 3, 'c2')
insert into B values(4, 5, 'c3')
insert into B values(4, 8, 'c4')
insert into B values(6, 7, 'c5')
goselect t.id2 , a.aa , a.bb , t.cc from a,(select b.id2 , b.cc from a , b where a.id = 1 and a.id = b.id1) t where t.id2 = a.iddrop table A,B/*
id2 aa bb cc
----------- ---------- ---------- ----------
2 a2 b2 c1
3 a3 b3 c2(所影响的行数为 2 行)
*/
create table A(ID int, aa varchar(10) , bb varchar(10))
insert into A values(1, 'a1', 'b1')
insert into A values(2, 'a2', 'b2')
insert into A values(3, 'a3', 'b3')
insert into A values(4, 'a4', 'b4')
insert into A values(5, 'a5', 'b5')
insert into A values(6, 'a6', 'b6')
insert into A values(7, 'a7', 'b7')
create table B(ID1 int, ID2 int, cc varchar(10))
insert into B values(1, 2, 'c1')
insert into B values(1, 3, 'c2')
insert into B values(4, 5, 'c3')
insert into B values(4, 8, 'c4')
insert into B values(6, 7, 'c5')
goselect b.id2 , a.aa , a.bb , b.cc from a , b where B.id1 = 1 and a.id = b.id2drop table A,B/*
id2 aa bb cc
----------- ---------- ---------- ----------
2 a2 b2 c1
3 a3 b3 c2(所影响的行数为 2 行)
*/